Access with ADODB

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 :
* 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 :
* 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 :
* 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.

> How to use ?

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

> How to Navigate ?
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

re: Access with ADODB

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

OR
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 :
We have done this all using Functions of Recordset. We can also add a record using SQL Query in this the code will like ;
Now this Query will be used to update some record :
or Simply use
> 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
or use this Query :
Regards
ARUZ

re: Access with ADODB

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

re: Access with ADODB

Hi everyone,

I have a problem in deleting a data that is connected on the database.. I tried this code but it shows me an error: Run-time error '-2147217900(80040e14)': Syntax error in string in query expression 'ID=". Here is the code that i did:

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim ab As String

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Administrator\My Documents\sample adding, deleting data\sample123.mdb;Persist Security Info=False"
rs.Open "Delete from Sample2 Where ID ='", cn, adOpenDynamic, adLockPessimistic

ab = MsgBox("Are you sure you want to delete a record?", vbInformation + vbYesNo, "Delete record")

If ab = Yes Then
If Not rs.EOF Then
rs.Delete
rs.Update
MSFlexGrid1.RemoveItem (MSFlexGrid1.Row)
MsgBox "You Deleted a record!", vbOKCancel + vbInformation, "Delete"
Else
MSFlexGrid1.Rows = MSFlexGrid1.FixedRows
End If
End If

Also I tried other solution but only the flexgrid deletes the data and not on a database.. Here's the code:

If MSFlexGrid1.Rows > MSFlexGrid1.FixedRows + 1 Then
MSFlexGrid1.RemoveItem (MSFlexGrid1.Row)
Else
MSFlexGrid1.Rows = MSFlexGrid1.FixedRows
End If

I'm using Microsoft Access 2003 as my database/backend..

Can someone help me in my problem? In adding a record, I don't have any problem because it works already.. but in deleting a data, i can't.. I will very appreciate to those who replied on my message..

Very Thanks,
Kyosuke =)