473,385 Members | 1,279 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 473,385 developers and data experts.

Access with ADODB

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
2 6706
EYE4U
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
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

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: chris | last post by:
Hi, I try to connect to Access with php with te code below. The database and table are ok. <?php include('../adodb/adodb.inc.php'); $db =& ADONewConnection('access'); $dsn =...
2
by: Josh Strickland | last post by:
I am attempting to create an Access database which uses forms to enter data. The issue I am having is returning the query results from the Stored Procedure back in to the Access Form. ...
1
by: Andrew Arace | last post by:
I scoured the groups for some hands on code to perform the menial task of exporting table data from an Access 2000 database to Oracle database (in this case, it was oracle 8i but i'm assuming this...
1
by: Ray Holtz | last post by:
I have a database in Access 2003 (Access2000 file format). There are two tables that are being used: Employees and Items. It is linked by the Employee field so that one employee can have many...
0
by: totierne | last post by:
comp.databases.ms-access, I want to know how to use Oracle views with session variables in Access. The parameterised views in access, are migrated to views with per session variables. The...
5
by: josephrthomas | last post by:
hi.. i am using ASP.NET with VB.NET to connect to a MS Access database... can someone pls tell me how do i make the sql statement to insert a new record into the existing table pls?? also...
0
by: ASP.Confused | last post by:
The old message looked a little stale, so I am re-posting it here. Anybody have any ideas of what I could do?!? The previous responses to this question are below. If you want to look at the...
2
by: Can | last post by:
I have a Microsoft Access front end, SQL server backend and I am using .dll files to complete the n-tier application. How do I reference an unbound textbox (from Microsoft Access) in the .dll? ...
7
by: gordy | last post by:
Hey all, I have a fairly simple app which goes out to the web to download data. I want to store this data in a database (1 table, ~8 fields or so). My program is written in VB.net and works...
0
by: DrewYK | last post by:
I have the project that may never end in front of me. I am creating a routine that will take SpreadSheets from Excel and bring them into Access. I am not using any "DoCmd"s because the goal...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.