Yup, DAO has significant shortcomings in a client/sever environment. DAO is simply the application programming interface (API) for the Jet database engine and works perfectly well with Jet database objects. Any bound form you create in an Access app (.mdb) is using DAO to talk to the Jet engine. But DAO can't work with SQL Server objects directly. In order to use DAO and SQL Server together, you would need to use things like pass-through queries that execute Transact SQL commands on ODBC connections to fetch data into local Jet tables. An interesting exercise, but not your optimal option.
Definitely use ADO. ADO was designed to work with data sources other than the Jet database engine (like SQL Server). With ADO you can manipulate SQL Server database objects directly through the Microsoft OLE DB Provider for SQL Server (you indicate the Provider in your connectioin string). Generally, in the type of 2-tier app you're describing, you'll use ADO Command objects a lot to execute stored procedures on the server.
A quick and dirty option is simple linking. You can link bound Access forms directly to SQL Server, and the Jet engine and ODBC handle all the communication details for you. Linking devours a lot of network and server resources, however. The best approach is always to use unbound forms populated from ADO recorsets. Or, use an Access Data Project (.adp) which all use ADO connections anyway.
HTH,
Bob
|
