Questions

Answer for:

Using MS Access and ODBC to access an SQL Database

Message 12 of 13

View entire thread
+
0 Votes
ineuw

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.