Questions

Using MS Access and ODBC to access an SQL Database

+
0 Votes
Locked

Using MS Access and ODBC to access an SQL Database

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.
Clarifications Clarifications
+
0 Votes
ineuw
Collapse -

Is your current system coded with VBA and DAO or, are you using macros to manipulate data? Did you install MSSQL server, or SQL Express?

As the first and quickest link to information, I recommend Wikipedia, where there are a lot of good (and more or less objective) articles are to be found on the related subjects.

http://en.wikipedia.org/wiki/Microsoft_Data_Access_Components

Microsoft MSDN has numerous articles on converting DAO to ADO and the web is swamped with various levels and qualities of information.

I have tried ADP but found it restrictive when trying to convert VBA code. Also, it uses HTML as the data entry form. Regardless of what you choose, ADO is a requirement to connect and manipulate SQL server data. There is no other [productive!] multi user alternative in a small business LAN environment. ADO was necessary so that Access can connect to object oriented data servers Otherwise, it would have hit a dead end. The upside is that there is a lot of similarity between DAO and ADO.

The great advantage of using MS Access as a Front End is that it has the best QBE, the best and most sophisticated form design, and very good reporting, especially when development time is taken into account.

Your table structures will have to be modified and a date stamp column should be added to each column and filled with unique fictious dates & times for existing records. A date stamp column is a requirement with a MSAccess/MySQL configuration and it may be required by MSSQL server under certain conditions.

Some, or all your queries will have to be modified - less so for MSSQL, more so for MySQL. The modifications depend on the table names, the current SQL statement structure, and the required SQL operations.

What data storage system you use is immaterial. Both MSSQL and MySQL are excellent, (one is free, the other isn't) and ADO communicates equally well with both through OLEDB and ODBC.

+
0 Votes
robo_dev
Collapse -

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. "

+
0 Votes
aos168b
Collapse -

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!