Printable View
Hello Friends,
Can any one tell me that how do i switch DAO to ADO? I have tried to open ADODB.Recordset of VBA in Access2007 and tried to open tables, but it gave me a syntax error. I think i need some type of converter to convert it. Does any one have any idea about how can i do so?
Thanks in advance.
When you migrate Access data to SQL Server, you have two choices with Access 2000 and only one with other versions. With previous versions of Access, you will link SQL Server tables, using ODBC, to your Access database. With Access 2000, you can either link the table using ODBC or decide to take profit of the new Access Project file (.adp) using OLE-DB. In the link option, your Access-VBA code remains in DAO, but need to be adapted to the newly ODBC-linked tables. In the project option, you DAO code needs to be modified in ADO.
With ADO, you can build all your code around a local database and then, very easily change one line of code that will allow you to access a database on a SQL Server.
With DAO, you use the OpenDatabase command passing the path of the database as one of the arguements. But with ADO, you need to build a connection string. To connect to a local database, use the following connection string:
ConnectionString = "Provider=Microsoft.JET.OLEDB.3.51; _
Data Source=c:\mydb.mdb"
That may seem a bit cumbersome, but this flexibility provides you with the means to connect to almost any database in any format anywhere. The following connection string is used to connect to a SQL Sever database named 'people':
ConnectionString = "driver=[SQLServer]; _
uid=admin;server=myserver;database=people"
DAO and ADO were designed to solve two different problems. As such, they expose two different object models and different methods of manipulating the underlying data engines. These differences could mean that you have to make some extensive changes when migrating your application from DAO to ADO.
The DAO object model is designed specifically for the Microsoft Jet database engine. Jet itself incorporates ISAM and ODBC connectivity and makes the back-end providers look as much like the native Jet engine as possible, though this comes at the expense of performance. DAO also has an ODBCDirect mode that allows it to host RDO objects and access ODBC datasources in a very efficient manner.
The ADO object model was designed for OLE DB providers and is a much simpler and more flexible object model than DAO. However, its architectural design poses some problems when using the Microsoft Jet OLE DB provider, and it is more limited than DAO in Jet functionality it supports.
Access 2000 and 2002 do not include a reference, by default, to the DAO 3.6 Object Library. Instead, Microsoft has included the ADO 2.1 Object Library as the default library for data access in Access 2000 and 2002. This means that if you create a new database in Access 2000 or 2002 and then use VBA code which includes the commonly used Database object, the code will fail. The Database object is not the only object where this problem is likely to occur. TableDef, QueryDef, Workspace, User, Group, and Container objects, as well as their Collection objects, are also commonly used in VBA code to customize database applications. However, without a reference set to the DAO object library, any code that uses these objects will also fail. Microsoft finally listened to the screams of its customers on this issue, so the DAO library is returned to being a default checked reference in new databases created with Access 2003. By default, the DAO reference has a higher priority than the ADO library in Access 2003.
I think DAO is simpler than ADO in Access for a lot of "stuff" especially Access to Access DBM. Check and, if you do not, set a "references" to DAO. You can get a lot of DAO information and code samples from Microsoft Knowledgebase site.
DAO will give you network connectivity; "access it with ease" may depend on factors not within your direct control. One thing that is in your control is the design. If it is an Access to Access design, I split my Access database into two, sometimes three modules:
1) Data tables
2) Lookup tables
3) The Access application
The first two (sometimes combined) are posted to the network site the last I distribute amongst the users, though usually not very many (may be five, ten, or so). If larger than say ten (local) users or the data grows exponentially, I start seeing performance hits and I start looking at Visual Basic or porting to Oracle. If I only have one module accessed by several users then I expect and usually encounter more than just performance problems, that would have been corrected by the former design consideration.