MS Access looks for .mdb rather than Progress schema

[fitzron]

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

 

[Guest]

When you use either IN or [database].

, 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].

select [field] from

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]

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=rtxfc.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

When you use either IN or [database].

, 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].

select [field] from

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)

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

 

[Guest]

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

(david)

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

When you use either IN or [database].

, 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].

select [field] from

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)

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

 

[fitzron]

Hi David,

The File DSN is a nice little trick, but it did not reveal much more than
I already know:

DRIVER=MERANT 3.60 32-BIT Progress SQL92 v9.1D
UID=xxxxxxx
DB=rxtfc
PORT=10410
HOST=139.48.78.46

This query seemed to get a little farther:
sqlStr = "SELECT ahfsDesc, ahfsCode, dFlag FROM
[odbc;DSN=RemotePharmDataTest;Uid=ODBCTEST;Pwd=DRIVER].[pub.ahfsCfg]"

It got past looking for pub.mdb, and looks like it tried to run the query
against the Progress database, but unfortunately, it cam up with an error:
[DataDirect-Technologies][ODBC Progress driver][PROGRESS]Syntax error 7587

It looks like while this syntax tells Access to pass this query through
properly, PRogress has a problem with it.

I also tried putting the whole thing in the connection string:

connectStr = "odbc;DSN=RemotePharmDataTest;DRIVER={MERANT 3.60 32-BIT
Progress
SQL92v9.1D};UID=ODBCTEST;Pwd=DRIVER;DB=rxtfc;PORT=10410;HOST=139.48.78.46"

This worked, but produced the same behavior as my original post.

This really has me stumped.

Thanks,
Ron

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

(david)

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

When you use either IN or [database].

, 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].

select [field] from

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)

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

 

[Guest]

[odbc;DSN=RemotePharmDataTest;Uid=ODBCTEST;Pwd=DRIVER].[pub.ahfsCfg]"

This is not passthrough query sql. This is Jet SQL. It is parsed by
Jet, and should be translated into ODBC SQL. The ODBC SQL
should be parsed by the ODBC driver, and translated into Progress
SQL. Jet has accepted the SQL, but Progress or the ODBC driver
has not: there has been a failure of translation. Looking at it, perhaps
in the table name? (Only because there is nothing else there to fail on?)

Turn on all the tracing in ODBC and Progress, to see if you can see
what Progress is actually receiving. Also, what is error 7587?

It looks to me like you have tried to use ahsfsCFg and failed:
now you've tried to use pub.ahfsCfg and failed. So, Are you Sure
your permissions are correct? :~)

When you tried "database=rtxfc.pub", did you leave 'pub.' out of
your query? The idea was to try to put the schema into the connect
string, so it could be left out of the query. Is there anything in the
DSN setup that would allow you to specify a schema?

BTW, if you put all of the connect information into the connect string,
you don't need a separate DSN. ? I think that A DSN reference is only
required if the DSN contains binary information that you can't stack
into the connect string?

BTW, we have not discussed Pass Through queries. A pass through
query is in ODBC syntax, or syntax accepted by the ODBC driver,
and you specify the connect string as the querydef connect string, instead
of embedding it into the [database].

SQL.

And also, your first attempt failed because of the embedded '.' which
is the delimiter between database.table.field. To avoid that ambiguity,
Access sometimes let you use # as a replacement for 'dot'.

(david)

Hi David,

The File DSN is a nice little trick, but it did not reveal much more than
I already know:

DRIVER=MERANT 3.60 32-BIT Progress SQL92 v9.1D
UID=xxxxxxx
DB=rxtfc
PORT=10410
HOST=139.48.78.46

This query seemed to get a little farther:
sqlStr = "SELECT ahfsDesc, ahfsCode, dFlag FROM
[odbc;DSN=RemotePharmDataTest;Uid=ODBCTEST;Pwd=DRIVER].[pub.ahfsCfg]"

It got past looking for pub.mdb, and looks like it tried to run the query
against the Progress database, but unfortunately, it cam up with an error:
[DataDirect-Technologies][ODBC Progress driver][PROGRESS]Syntax error 7587

It looks like while this syntax tells Access to pass this query through
properly, PRogress has a problem with it.

I also tried putting the whole thing in the connection string:

connectStr = "odbc;DSN=RemotePharmDataTest;DRIVER={MERANT 3.60 32-BIT
Progress
SQL92v9.1D};UID=ODBCTEST;Pwd=DRIVER;DB=rxtfc;PORT=10410;HOST=139.48.78.46"

This worked, but produced the same behavior as my original post.

This really has me stumped.

Thanks,
Ron

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

(david)

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

:

When you use either IN or [database].

, 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].

select [field] from

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)

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

 

[fitzron]

Hi David,

I received some useful information from the vendor a couple of days ago
and it has solved the problem. I guess they have had other clients with the
same issue; proabably in situations where they were unable to alter their
code. I'm not sure if I agree with the cause that the article indicates, but
the fix does work. I'm sure there must be some way of handling this in Access
as well, but for now I'll move on with this workaround so I can get going
with the rest of the changes I need to make. I'll post the fix here just in
case someone else runs into this. Thanks for your help with this. Ron

Facts:
# Progress 9.x
# MERANT 3.60 32-BIT Progress SQL-92
# Windows 32 Intel
# Windows NT 32 Intel/Windows 2000


Symptoms:
# Error running SQL-92 Query
# Table/View/Synonym not found (7519)
# Same query previously worked in Progress 8.x using SQL89.


Cause:
User is accessing a table from the PUB schema without prefixing the table
with PUB. All 4GL tables, by default, are owned by PUB. Using SQL-92, all
queries must specify the table owner. This was not the case when using SQL-89.


Fixes:
Add the "DefaultSchema" string value set to "PUB" in the registry:

HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\<your DSN name>

and

HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.INI\<your DSN name>

This entry allows you to keep your existing code written with the ODBC
SQL-89 Driver. Reconnect to the database with the client and test the
application again.


Notes:
This solution only applies to ODBC. Do not use the SQL Explorer Tool to test
SQL statements as it uses the JDBC driver. Test instead with WinSQL, MS
Query, ODBC Test, or another Third-party ODBC utility.

[odbc;DSN=RemotePharmDataTest;Uid=ODBCTEST;Pwd=DRIVER].[pub.ahfsCfg]"

This is not passthrough query sql. This is Jet SQL. It is parsed by
Jet, and should be translated into ODBC SQL. The ODBC SQL
should be parsed by the ODBC driver, and translated into Progress
SQL. Jet has accepted the SQL, but Progress or the ODBC driver
has not: there has been a failure of translation. Looking at it, perhaps
in the table name? (Only because there is nothing else there to fail on?)

Turn on all the tracing in ODBC and Progress, to see if you can see
what Progress is actually receiving. Also, what is error 7587?

It looks to me like you have tried to use ahsfsCFg and failed:
now you've tried to use pub.ahfsCfg and failed. So, Are you Sure
your permissions are correct? :~)

When you tried "database=rtxfc.pub", did you leave 'pub.' out of
your query? The idea was to try to put the schema into the connect
string, so it could be left out of the query. Is there anything in the
DSN setup that would allow you to specify a schema?

BTW, if you put all of the connect information into the connect string,
you don't need a separate DSN. ? I think that A DSN reference is only
required if the DSN contains binary information that you can't stack
into the connect string?

BTW, we have not discussed Pass Through queries. A pass through
query is in ODBC syntax, or syntax accepted by the ODBC driver,
and you specify the connect string as the querydef connect string, instead
of embedding it into the [database].

SQL.

And also, your first attempt failed because of the embedded '.' which
is the delimiter between database.table.field. To avoid that ambiguity,
Access sometimes let you use # as a replacement for 'dot'.

(david)

Hi David,

The File DSN is a nice little trick, but it did not reveal much more than
I already know:

DRIVER=MERANT 3.60 32-BIT Progress SQL92 v9.1D
UID=xxxxxxx
DB=rxtfc
PORT=10410
HOST=139.48.78.46

This query seemed to get a little farther:
sqlStr = "SELECT ahfsDesc, ahfsCode, dFlag FROM
[odbc;DSN=RemotePharmDataTest;Uid=ODBCTEST;Pwd=DRIVER].[pub.ahfsCfg]"

It got past looking for pub.mdb, and looks like it tried to run the query
against the Progress database, but unfortunately, it cam up with an error:
[DataDirect-Technologies][ODBC Progress driver][PROGRESS]Syntax error 7587

It looks like while this syntax tells Access to pass this query through
properly, PRogress has a problem with it.

I also tried putting the whole thing in the connection string:

connectStr = "odbc;DSN=RemotePharmDataTest;DRIVER={MERANT 3.60 32-BIT
Progress
SQL92v9.1D};UID=ODBCTEST;Pwd=DRIVER;DB=rxtfc;PORT=10410;HOST=139.48.78.46"

This worked, but produced the same behavior as my original post.

This really has me stumped.

Thanks,
Ron

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

(david)

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

:

When you use either IN or [database].

, 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].

select [field] from

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)

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

 

[david epsom dot com dot au]

Add the "DefaultSchema" string value set to "PUB" in the registry:

That is the 'missing line' that I was hoping to find in
the File DSN. (Your fix is for system and user DSNs, stored
in the registry). The next step would be to see if it
can be accepted in your connect string, instead of using
a separate DSN.

Thanks for the update,

(david)