Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 14-03-2009
Member
 
Join Date: Dec 2008
Posts: 61
idea Switch DAO to ADO

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.
Reply With Quote
  #2  
Old 14-03-2009
Member
 
Join Date: Apr 2008
Posts: 1,948
Re: Switch DAO to ADO

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.
Reply With Quote
  #3  
Old 14-03-2009
Member
 
Join Date: May 2008
Posts: 2,008
Re: Switch DAO to 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"
Reply With Quote
  #4  
Old 14-03-2009
Member
 
Join Date: Apr 2008
Posts: 2,001
Re: Switch DAO to ADO

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.
Reply With Quote
  #5  
Old 14-03-2009
Member
 
Join Date: May 2008
Posts: 2,293
Re: Switch DAO to ADO

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.
Reply With Quote
  #6  
Old 14-03-2009
Member
 
Join Date: Oct 2005
Posts: 2,389
Re: Switch DAO to ADO

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.
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags:



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Switch DAO to ADO"
Thread Thread Starter Forum Replies Last Post
Managed switch network speed vs. unmanaged switch network speed LLoLL Networking & Security 6 22-07-2011 07:28 PM
Toshiba a665 s6050 eco wireless switch, Led switch and play/pause are not working Emilian Portable Devices 4 14-04-2011 02:06 PM
My CPU won't switch on s_irah94 Hardware Peripherals 3 26-05-2010 12:12 AM
KVM Switch Help needed tim_ver Hardware Peripherals 4 02-04-2009 04:09 PM
Switch of 24 hydrants Grundy Networking & Security 5 31-10-2008 07:23 PM


All times are GMT +5.5. The time now is 09:36 PM.