Re: MS Access looks for .mdb rather than Progress schema

Create a File DSN.
Then open the file DSN in Notepad or Wordpad, and see how
the values have been written.

(david)

"fitzron" <fitzron@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7B5BEA15-2CB8-469E-9600-67360FB66FB0@xxxxxxxxxxxxxxxx
Hi Dave,

Thanks for the response. I've played around with these settings, but I'm
still not having success. The settings for the Merant ODBC driver for
Progress data source require are:
Host,
Port,
Database
UID
PASSWORD

I'm having trouble finding the proper settings to put these into my
connection string

connectStr =
"Provider=MSDASQL;DSN=RemotePharmDataTest;Uid=uid;Pwd=password;"

I've tried this:

connectStr =

"Provider=MSDASQL;DSN=RemotePharmDataTest;Uid=uid;Pwd=password;database=rtxf
c.pub" (also without the .pub)

In both cases I can connect, but when the query is run it's looking for
pub.mdb. I guess I need the ODBC: part
[odbc:server=svr;database=database],
What should go in the "svr" attribute. I'm having trouble matching that up
wioth the Merant ODBC Driver settings as there does not seem to be a
corresponding server attribute.

Thanks,
Ron

"david@epsomdotcomdotau" wrote:

When you use either IN or [database].[table], the
default connect string means a jet database.

If you want to specify an ODBC or ISAM database, you
have to specify that.

select [field] from [odbc;dsn=fred].[table]
select [field] from [table] in [odbc;dsn=fred]

Normally I specify the database in the connect string,
either of a linked table or a pass through query, and
when I use that connection I get that database:
[odbc:server=svr;database=database]. If all of your
tables and views are in the same schema, you might
be able to specify the schema in your database connect
string, so that you don't have to specify a connect
string in your query.

(david)

"fitzron" <fitzron@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:83D4A3FB-D3AE-442E-9E05-0D9E2F5ACD5A@xxxxxxxxxxxxxxxx
I have some code that imports data into an Access 2003 DB from a
Progress
9.1D DB using the Merant 3.60 32 Bit Progress SQL 92 v9.1D ODBC driver
that
comes bundled with the Progress client.

The ODBC driver allows me to connect to the Progress database no
problem,
but when I try to run a this query against the database:

SELECT ahfsDesc, ahfsCode, dFlag FROM ahfsCfg

it complains because the table or view does not exist. I checked into
things
and this table is actually in a schema called "pub", so I corrected
the
SQL
as so:

SELECT ahfsDesc, ahfsCode, dFlag FROM pub.ahfsCfg

but when this query is run Access thinks we are looking for an Access
database, pub.mdb, in the Documents and Settings directory.

Does anyone know why the ODBC connection would be looking for the
Access
database rather than the Progress schema. Does anyone know a
connection
setting that can fix this?

Here the snippet of code I'm using:

connectStr =
"Provider=MSDASQL;DSN=RemotePharmDataTest;Uid=uid;Pwd=password;"

Set connection = New ADODB.connection

With connection
.ConnectionString = connectStr
.ConnectionTimeout = 10
.Open

End With
'Successfully opens connection

sqlStr = "SELECT ahfsDesc, ahfsCode, dFlag FROM pub.ahfsCfg"

Set rsDest = db.OpenRecordset(sqlStr, dbOpenDynaset, dbReadOnly)

(this line produces the error)

I have though of a solution and that was to create the necessary
synonyms
on
the Progress database, but the vendor appears reluctant to do this.

Any ideas would be appreciated.

Thanks in Advance,
Ron





.

Relevant Pages

  • Re: MS Access looks for .mdb rather than Progress schema
    ... I've played around with these settings, ... The settings for the Merant ODBC driver for ... If you want to specify an ODBC or ISAM database, ... when I use that connection I get that database: ...
    (microsoft.public.access.modulesdaovba)
  • Re: ADO Connection Timeout
    ... so what happens when a connection failure forces one station to revert ... to a local database? ... Further, you *will* have contention issues, Jet does not support record ... to the central server, but you are willing to live with periods where it ...
    (microsoft.public.data.ado)
  • Re: ADO Connection Timeout
    ... When the first test is run, the results are stored in the central database. ... to the central server, but you are willing to live with periods where it ... i.e. a local database or even a text file. ... to function until the connection can be restored to the server. ...
    (microsoft.public.data.ado)
  • Re: ADO Connection Timeout
    ... to the central server, but you are willing to live with periods where it ... i.e. a local database or even a text file. ... to function until the connection can be restored to the server. ...
    (microsoft.public.data.ado)
  • Re: ADO Connection Timeout
    ... much rather write stored procedures and use server side cursors. ... local database, it is vital that I get the information to a central server. ... Once the connection is restored, ...
    (microsoft.public.data.ado)