Access with ADODB

[EYE4U]

Hi all,

This is how you can connect to an Access Database using ADODB..
First add a reference to Microsoft ActiveX Data Objects 2.0 Library

Now to lets Declare some variables :

Private Con As New ADODB.Connection
Private Rec As New ADODB.Recordset

* You can set them to Public too if you want to access the Connection and Recordset in any other Form or Module.

Now lets Create a Connection to your Database :

Con.Provider = "Microsoft.jet.oledb.4.0; "
Con.Open Database Path

* Provider tells the type of Database, if you are using any other type of Database instead of Access mdb use its provider which you can see in ODBC Manager in Control Panel.

After this Connection will established with the Database.

Now we have to open some Tables and have to fetch Records to your app, to do so we need a Recordset in which all the records from selected Tables are fetched.

Now the following code will fetch all Records from Table1 in MDB File :

Rec.Open "Select * From Table1", Con, adOpenDynamic, adLockOptimistic

* First parameter is Source
* Second parameter is Connection
* Third parameter is Open Mode means you can move forward or backward in the table, can add and delete. There are several Modes.
* Fourth parameter helps you to Lock your Database File that it should open once at a time.
* There are few more parameters but i m not discussing them here if anybody want to know can ask :)

Now lets create a sample program which will open a Database and then Load all Records from Table1 in a Recordset.

Private Con As New ADODB.Connection
Private Rec As New ADODB.Recordset
 
Public Sub Create_Connection(File_Path as String)
 
    Set Con = New ADODB.Connection
    Set Rec = New ADODB.Recordset
 
    Con.Provider = "Microsoft.jet.oledb.4.0; "
    Con.Open File_Path
 
End Sub
 
Public Sub Load_Records(sqlQuery as String)
 
    Rec.Open sqlQuery, Con, adOpenDynamic, adLockOptimistic
 
End Sub
 
Public Sub Close_Connection()
 
    Rec.Close
    Con.Close
 
End Sub

> How to use ?

Create_Connection (App.Path & "\db1.mdb")
Load_Records ("Select * From Table1")

Now Connection has been established, Records has been loaded to Recordset now you can easily navigate the database using Recordset.

> How to Navigate ?

Rec.MoveNext
Rec.MovePrevious
Rec.MoveFirst
Rec.MoveLast

Function Name is telling us the position of cursor in Recordset.

# if anywhere in the article, i have made any mistake please correct that.

Regards
ARUZ

[EYE4U]

> How to Add/Update a Record ?
Lets suppose there are Three Columns in Table1.
1. ID
2. Name
3. Phone No

As we have to Add record in Table and Table has been opened in Recordset (Rec) so we will use Recordset to Add a Record.

Rec.AddNew 0,12

OR

Rec.AddNew "ID",12

First parameter is Field and second parameter is the Value.
Value based on the type of Field we have set if its Integer we have to pass an integer value if string we have to pass strings and so on for other data types.

Now this code will only create a new Record in Table but not fill other fields like Name and Phone No field will remain empty. Mean now we need to update the Record 12.

So the code will be like :

Rec.AddNew 0,12
Rec.Update 1,"Ali"
Rec.Update 2,"042-0001111"

We have done this all using Functions of Recordset. We can also add a record using SQL Query in this the code will like ;

sqlQuery = INSERT INTO Table1 WHERE ID = 12, Name = "Ali", Phone_No = 042-0001111"
Rec.Open sqlQuery, Con, adOpenDynamic, adLockOptimistic

Now this Query will be used to update some record :

sqlQuery = "UPDATE Table1 Set ID=?, Name=?, Phone_No=?"

or Simply use

Rec.Update 'Field Name', 'Value'

> How to Delete a Record ?

To delete any record we have to methods to Delete some record.
One is to use Sql Query and Second is to use Delete Function.
We just need to call delete function in order to delete record like

Rec.Delete

or use this Query :

sqlQuery = "DELETE FROM Table1 WHERE ID = 12"

Regards
ARUZ

[Colanth]

It's an old thread, but

Private Con As New ADODB.Connectio n

is a bad idea. When you're through with an object, you should destroy it. If you instantiate it in the declaration, you can't. Declare it as

Private Con As ADODB.Connectio n

Then, in the code, instantiate it:

Set Con = New ADODB.Connectio n

Later in the code, when you're through with it, destroy it:

Set Con = Nothing

If you declare objects and don't destroy them, you're eventually going to have to reboot the computer. (Memory taken by objects isn't returned until you destroy the objects.)