Access with ODBC linked tables

[goolawah]

I have a simple MS Access application which effectively merges the contents of a text file with the selected contents of two ODBC linked tables and produces another text file as output.

It all works fine on my Windows/Office 2000 system but, on the client's XP/Office 2003 envronment it is unable to access the ODBC-linked tables. I have no trouble doing an ODBC Query in EXCEL to access the files to which my tables are linked, so the ODBC setup seems OK.

I can get around the problem by creating a dummy linked table in Access and can then view it's contents AND those of my two original tables OK. However, if I close and reopen Access none of the ODBC linked tablke will link, not even the dummy one I just created.

Any suggestions appreciated.

 

[Ed2020]

Do you get an error message?

Ed Metcalfe.

Please do not feed the trolls.....

 

[goolawah]

Back at the office I have set up to try and re-create the porblem.
I get an error message

Run-time error 3184
Could not execute query. Could not find linked table.

It only seems to occur when accessing the ODBC tables across the network.

Thanks for help so far.

 

[ArtieChoke]

What do you mean when you say "across the network? And what database are you linking to?

When I have to use Access to connect to a db via linked tables, I use a file DSN. Have you tried that?

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[Ed2020]

What kind of database are you accessing via ODBC? If Oracle then:

I'm not sure what is causing this error, but it may be worth trying passthrough queries rather than linked tables.

Passthrough queries are run at the server, so they pull less data across the network.

This may (or may not!) make a difference.

Ed Metcalfe.

Please do not feed the trolls.....

 

[GhostWolf]

The couple times I've run into this problem, I had to delete the tables from the mdb and re-link them - on the PC where it was to be used.

 

[mirtheil]

Access stores all of the DSN and connection string information in linked tables. If you have linked it on one machine, then put it on another machine and the DSN/connection string isn't exactly the same then you will get an error.
I would second GhostWolf's suggestion: remove and relink the tables.


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.

http://www.mirtheil.com