remote connect to Access db using ODBC DataServer

The official process is to go through the nameserver which shunts the request off to the ODBC DS. Your DSN needs to be defined as a machine DSN on the Windows 2003 Server where the ODBC DS is installed.  Your conx string should look something like this, assuming the SH and the ODBC DS are running on server "w2k3DS".

connect shDB -H w2k3DS -S <SH 4GL port> -db <DSN Name> -dt ODBC -H w2k3DS -S <NS port> -DataService <odbc DS broker name odbroker1> -Dsrv SVUB,1

Increase the ODBC DS logging level and check the ODBC DS log file. What happened?

If you can upgrade to OE 11.5 or 6, there is a better solution with the ODBC classes. You can connect to the DSN directly from within your 4GL code and run SELECTs.

You can try downgrading the ODBC DS to V9 as the V10 client should be able to talk to the V9 DS.

Thanks Paul. It is still not connecting though. There is nothing of interest in the log file (keepAlive, listening, broker registered with name server). I guess nothing is connecting, so nothing to report.

Supplying the -DataService parameter with the name of the ODBC DataServer, rather than the ODBC data source name, My error messages are now:

Data Service odbbrokertest not found at NameServer at Host <correct> Port <correct for name server>. (8892)

DataServer connection failure. (7255)

Failed to connect to the ODBC database. (6142)

Using ODBC data source name with the -DataService parameter gives me the original error message IM002 [Microsoft]...

I'm not sure which usage is correct but neither is working.

I don't think either the upgrade or downgrade is possible for me.

odbbrokertest is not registered with the nameserver or maybe not started? Run nsman -q -i <NS Name> on the server to see what brokers are registered with the NS.

Thanks Paul. It is running and registered. Querying the Nameserver reminded me that the Adminserver is not running on the default port. but that shouldn't matter should it?

It looks like the "Data Service odbbrokertest not found at NameServer..." error I was getting was after trying an alternate value (the logical name defined in the schema holder) for the -db parameter for the DSN. Running with -db set to the data source name returns "non-PROGRESS database test1 unknown. (1008)". Sorry for the mistake.

I should add that running that connect statement also fails with the same error on the server. If I just run the connect shDB -H w2k3DS -S <SH 4GL port> portion it will connect when you run a procedure referencing the database. That is not the case on the client. If I run that it will connect to the SH and the data dictionary will show the schema for the ODBC database but running a procedure referencing the ODBC database returns the error S1000: [Microsoft][ODBC Microsoft Access Driver] Could not find file '(unknown)'.

Paul

It looks like it is working now. According to KB 1569 the problem was: The logical name for the foreign database (-ld parameter in the connection string) is different than the DataServer image stored in the schema holder. So I changed the logical name in the SH to be the same as the DSN and it now works with that connection string as you suggested. I'm not sure why there would be an option to provide a logical database name if it must be the same as the DSN, but now I know. Thanks for pushing me through this.

Paul

I don't think the AdminServer port matters to your DB conx.

I think the rest makes sense. It's hard to debug with just a little bit of information in your post. When you run the "FOR EACH odbc_table" and get the "could not find file" error, did you validate that the _odbcsrv.exe spawned (sorry I forget the exact name of the executable)? And what is in dataserv.lg?  Up the logging level on the odbc ds and see what it spits out.

How did you create the SH? Is it from a previous version? At some point you had to pull the schema from Access.

Well I guess you can ignore my last post.

I was just working on an MS SQL DataServer and in the "Edit Connection Information" box I see that the physical name is the same as the DSN but the logical name is something else. But you are correct, on my command line I match the -db <physical name> -ld <logical name> to what is defined in the SH.

When I change the -ld to something else I get the same error:

non-PROGRESS database mssqldbXXX unknown. (1008)

One last thing: in my case the physical and logical database names do not match in the SH. The physical DB name needs to match the ODBC DSN but the logical name does not it seems.

In my case the physical name is different from the DSN but the physical name is not displayed in the Edit Connection dialog. The only thing I changed was the logical name to match the DSN. if you're saying that the -ld parameter just has to match the logical name defined in the SH I don't think that was my experience. I don't believe I was even using the -ld parameter as it wasn't in the connection string you posted. Or maybe that was the problem. Maybe if the -ld parameter is not included and the logical name is different than the DSN it will raise an error. Although I thought I had been including that parameter in previous tests.

Interesting. Maybe I'm misunderstanding the problem. The ODBC data source name in the SH has to match the DSN. I guess I have some more testing to do to figure out what the issue was. I'm glad to be in a much better place for testing this out. Thanks Paul.

To correct my earlier post, it appears that the logical name defined in the schema holder does not have to be the same as the DSN but if it is different, that name must be supplied in the -ld parameter.