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

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: Ada DB bindings and APQ
    ... >>I didn't like the use of handles for database objects. ... > will ensure that the connection will not go away until at least one query ... > things which can be made with a connection. ... That is different to ODBC. ...
    (comp.lang.ada)
  • RE: Posible to speed up query update with Apllication.screenupdati
    ... Don't change anything on the database side; the only question is the most ... You create a connection by sending the connect string to the ODBC driver. ... (multiple queries). ... You now execute the query (there are several ways in ADO but I usually ...
    (microsoft.public.excel.programming)
  • 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: Word 2003 Closes upon update of sort order of table using Acce
    ... data connection file created to the database folder and created a dummy user ... connection is much faster and seems far more stable. ... incorporate OLE DB, they probably found themselves with a lot more problems ... embed a table or query name. ...
    (microsoft.public.word.mailmerge.fields)
  • Sql Server SCOPE_IDENTITY()
    ... I'm currenly in the process of writing a Database abstraction class ... SCOPE_IDENTITY query it is on the same connection as the INSERT query (which ...
    (microsoft.public.dotnet.framework.adonet)