?
Solved

VB Connection with Access2000 by ADO & DAO

Posted on 2001-09-04
6
Medium Priority
?
1,150 Views
Last Modified: 2013-11-23
How to connect VB with Access2000 using ADO and DAO ?
Which version of ADO and DAO will support ?
0
Comment
Question by:thangaraj
6 Comments
 
LVL 1

Expert Comment

by:songstre
ID: 6454635
'For DAO
Set oConn = DAO.OpenConnection("C:\MyDocs\MyDB.mdb")

'OR for ADO
Set oConn = CreateObject("ADODB.Connection")
oConn.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=H:\Personnal Docs\Novatech.mdb;Persist Security Info=False")
0
 

Expert Comment

by:wvandeneede
ID: 6454648
I have used ADO 2.5 and 2.6 both seem to do the job
just fine.  I think  ADO 2.5 comes standard with Access 2000 (not sure), but you can always download the latest MDAC version from Microsoft for free.  The latest version now is MDAC 2.6.

Anyway the way to connect doesn't really change.
First of all you need a connection object (Let's take ADO).

Go to the Project-References tab and mark the Microsoft ActiveX Data Objects 2.5 Library

Now we can do the coding... (I just took a form with a standard command button on it)

>---- Start Code---<

Option Explicit

Private Sub Command1_Click()

Dim mConnection As ADODB.Connection

Set mConnection = New ADODB.Connection

mConnection.ConnectionString = "Provider=Microsoft.jet.OLEDB.4.0;Data source=" & App.Path & "/Yourdatabase.mdb"

Set mConnection = Nothing

End Sub
>--- End Code---<

The connection string can also contain things like password
other providers to connect to SQL Server etc...

This will connect you to the database, to get data from
the database, you should take a look at the
ADO recordset object which works with the above connection object.
0
 
LVL 1

Expert Comment

by:songstre
ID: 6454676
wvandeneede,

     I can see you are new in EE. Welcome! I'd just like to tell you that in EE, it is considered standard etiquette to post your proposed solution as a comment so that all others may have a chance at answering and you do not lock the question. You should only post as an answer if you are sure that you have the only possible solution to the problem. Thanks.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Expert Comment

by:songstre
ID: 6454679
PS You can withdraw your answer and resubmit it as a comment if you wish.
0
 
LVL 43

Accepted Solution

by:
TimCottee earned 60 total points
ID: 6461359
songstre, if only that were possible. There is currently no withdraw answer option.

thangaraj: you should probably reject this proposed answer at this stage.

Essentially the comments given are correct. To simplify though:

ADO: Requires MDAC_TYP version 2.1 or higher, 2.51 is a stable version, 2.6 is great but doesn't include the jet drivers required for access so probably not your best bet.

DAO: Requires DAO 3.6 rather than DAO 3.51 or lower. This is included with MDAC_TYP upto version 2.51.

You should ensure that the appropriate references are checked in your project and if distributing a project with these components that the correct version of MDAC_TYP is in the \program files\microsoft visual studio\vb98\wizards\pdwizard\redist folder on the development machine.

If you are using ADO with access 2000 then you must use the provider string "Microsoft.Jet.OLEDB.4.0" rather than "Microsoft.Jet.OLEDB.3.51". The version 4 provider will also work with previous versions of access  so you can be assured of some backward compatibility there.
0
 
LVL 2

Expert Comment

by:Lunchy
ID: 6462272
wvandeneede,

I am rejecting your answer on the basis that it does not address the entirety of the questioner's problem.  (Questioner specified DAO and ADO)

Please note that here in the VB topic area the etiquette is to use comments only.  This is one of the busiest areas and even on a simple question there is generally a lot of expert input and activity.  Locking the question by posting as an answer is essentially saying "No-one else has to worry about this, it is so simple it can be answered in a single post."  When many people are posting nearly simultaneously, locking the question leads to confusion and frustration, thus most of us choose to post comments only in order to allow the questioner to choose which comment is the best and to allow the question to remain in the high traffic "Questions awaiting answers" section.

Please read the information at the link below for tips on questions and answers.  If you have any more questions about the way the site works, feel free to post a zero point question at the community support topic area.


Lunchy
Friendly Neighbourhood Community Support Moderator

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month15 days, 4 hours left to enroll

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question