id="info"

Question

Locked

Using MS Access and ODBC to access an SQL Database

By yabbayogi ·
I have an MS Access 2000 database running on a Win XP Pro workstation that was pulling data from an external dBaseIII-format database using ODBC. That database has now been replaced with an MS SQLExpress database on an MS 2000 Small Business Server. I have re-established the external database links in the Access database using "Machine Data Source" SQL-ODBC links, and I can see the SQL data and run queries on it. But whenever I try to run an Access Visual Basic module from within the MS Access database (either a function I've written or even adding a new button to a form), Access throws up on me with a "Error accessing file. Network connection may have been lost" message. This is even for functions that don't look at the SQL data! From that point on, my Access database becomes unstable, and starts acting crazy (e.g. not letting me delete queries, or no longer displaying forms, etc), and I have to abandon it for a previous version of the Access database. Any ideas from anyone would be most appreciated on how I can write, compile, and run my much-needed VB Access functions, especially those that do look at the external SQL data.

This conversation is currently closed to new comments.

13 total posts (Page 2 of 2)   Prev   01 | 02
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Authentication problem: are you using SA or windows authentication?

by robo_dev In reply to Using MS Access and ODBC ...

My experience is that it is better to try solving by creating a brand-new database and attempt to import objects from the OLD database, rather than trying to export to a new database. It frequently seems that it ends up being usually one, maybe a couple, of objects that are corrupting the entire database.

Although the form is stored locally, it may have a control source to some linked table. The linked table may be on a database that (was) on the network. Check the form's control source. Use the linked table manager to renew the links as some of them may have had the design altered.

KB article on this error:
http://support.microsoft.com/kb/304548

"When you try to view the code for a module, try to compile a database, try to create an MDE file, or try to execute code in a Microsoft Access 2000 database, you may receive one of the following error messages:
Error accessing file. Network connection may have been lost. "

Collapse -

This error is a classic sign of an unpatched Access installation

by aos168b In reply to Using MS Access and ODBC ...

The error: "Error accessing file. Network connection may have been lost", is a classic sign of an unpatched Access installation. It has nothing to do with network connections. You need to install service pack 3 (SP-3) for Access 2000:

http://support.microsoft.com/?id=304548

Good Luck!

Back to Networks Forum
13 total posts (Page 2 of 2)   Prev   01 | 02

Hardware Forums