remote connect to Access db using ODBC DataServer

Posted by pauldownie on 16-Aug-2016 16:32


I am not having any luck making a remote connection to an Access database located on my server and I'm not sure what the issue is. I can connect to the schema holder but get an error when running a procedure: "IM002: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". I have looked at KB 2747 but I'm not sure there is anything relevant there.

I can connect to the ODBC data source through the schema holder database from the server. I have followed KB 19927 in setting it up but that KB notes that since 10.x onward Microsoft Access is no longer supported. It also states that no DataServer broker is required. I haven't found it necessary to identify any connect parameters in the schema holder when connecting from the server. When trying to connect from the client, I have tried port numbers for the NameServer and for the DataServer for the -S parameter but I'm not really sure what should go there.

Is this not possible or am I missing something?

Thanks, Paul

OE 10.2B

Windows 7 client; Windows Server 2003

All Replies

Posted by Paul Koufalis on 16-Aug-2016 19:52

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.

Posted by pauldownie on 17-Aug-2016 09:53

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.

Posted by Paul Koufalis on 17-Aug-2016 12:33

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.

Posted by pauldownie on 17-Aug-2016 14:38

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)'.


Posted by pauldownie on 17-Aug-2016 15:08

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.


Posted by Paul Koufalis on 17-Aug-2016 15:09

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.

Posted by Paul Koufalis on 17-Aug-2016 15:09

Well I guess you can ignore my last post.

Posted by Paul Koufalis on 17-Aug-2016 15:18

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)

Posted by Paul Koufalis on 17-Aug-2016 15:19

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.

Posted by pauldownie on 17-Aug-2016 16:13

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.

Posted by pauldownie on 17-Aug-2016 16:13

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.

Posted by pauldownie on 22-Aug-2016 16:12

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.

This thread is closed