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


[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].[table] 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)

"fitzron" <fitzron@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:71511F15-66B8-4B4F-B776-DA88523A3661@xxxxxxxxxxxxxxxx
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


"david@epsomdotcomdotau" wrote:

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
    ... # Same query previously worked in Progress 8.x using SQL89. ... This entry allows you to keep your existing code written with the ODBC ... BTW, if you put all of the connect information into the connect string, ... I also tried putting the whole thing in the connection string: ...
    (microsoft.public.access.modulesdaovba)
  • Re: OpenDataSource & stored procedure
    ... recognises the ODBC escape syntax. ... You shouldn't need the connection parameter since Word gets it from the ... return multiple result sets - enough to confuse software such as MS Query ... >>> The call works in MSQuery but won't return the data to Word. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: [VW 7.3.1] ODBCConnection
    ... I played around with a few combinations on the connection string and ... with a trusted SQL Server connection. ... there are differences for database connects. ... > any database specific odbc drivers. ...
    (comp.lang.smalltalk)
  • RE: ODBC query in VB code Need HELP
    ... "antointhe" wrote: ... terms of connection settings and object creation / disposal. ... Since your pass-through query already exists (including the ... Dim STRSQL As String ...
    (microsoft.public.access.formscoding)
  • RE: ODBC query in VB code Need HELP
    ... terms of connection settings and object creation / disposal. ... Since your pass-through query already exists (including the ... Dim STRSQL As String ...
    (microsoft.public.access.formscoding)