By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,950 Members | 1,019 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Access with ADODB

P: 75
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 :
Expand|Select|Wrap|Line Numbers
  1. Private Con As New ADODB.Connection
  2. 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 :
Expand|Select|Wrap|Line Numbers
  1. Con.Provider = "Microsoft.jet.oledb.4.0; "
  2. 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 :
Expand|Select|Wrap|Line Numbers
  1. 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.

Expand|Select|Wrap|Line Numbers
  1. Private Con As New ADODB.Connection
  2. Private Rec As New ADODB.Recordset
  3.  
  4. Public Sub Create_Connection(File_Path as String)
  5.  
  6.     Set Con = New ADODB.Connection
  7.     Set Rec = New ADODB.Recordset
  8.  
  9.     Con.Provider = "Microsoft.jet.oledb.4.0; "
  10.     Con.Open File_Path
  11.  
  12. End Sub
  13.  
  14. Public Sub Load_Records(sqlQuery as String)
  15.  
  16.     Rec.Open sqlQuery, Con, adOpenDynamic, adLockOptimistic
  17.  
  18. End Sub
  19.  
  20. Public Sub Close_Connection()
  21.  
  22.     Rec.Close
  23.     Con.Close
  24.  
  25. End Sub
> How to use ?

Expand|Select|Wrap|Line Numbers
  1. Create_Connection (App.Path & "\db1.mdb")
  2. 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 ?
Expand|Select|Wrap|Line Numbers
  1. Rec.MoveNext
  2. Rec.MovePrevious
  3. Rec.MoveFirst
  4. 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
Mar 29 '09 #1
Share this Article
Share on Google+
2 Comments


P: 75
> 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.

Expand|Select|Wrap|Line Numbers
  1. Rec.AddNew 0,12
OR
Expand|Select|Wrap|Line Numbers
  1. 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 :
Expand|Select|Wrap|Line Numbers
  1. Rec.AddNew 0,12
  2. Rec.Update 1,"Ali"
  3. 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 ;
Expand|Select|Wrap|Line Numbers
  1. sqlQuery = INSERT INTO Table1 WHERE ID = 12, Name = "Ali", Phone_No = 042-0001111"
  2. Rec.Open sqlQuery, Con, adOpenDynamic, adLockOptimistic
Now this Query will be used to update some record :
Expand|Select|Wrap|Line Numbers
  1. sqlQuery = "UPDATE Table1 Set ID=?, Name=?, Phone_No=?"
or Simply use
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. Rec.Delete
or use this Query :
Expand|Select|Wrap|Line Numbers
  1. sqlQuery = "DELETE FROM Table1 WHERE ID = 12"
Regards
ARUZ
Mar 30 '09 #2

P: 1
It's an old thread, but

Private Con As New ADODB.Connection

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.Connection

Then, in the code, instantiate it:

Set Con = New ADODB.Connection

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.)
Aug 19 '09 #3