How to connection, change, and retrieve from an Access Database using ADO (works with other ODBC supported DBs as well)

Ok for this initial FAQ post (may edit it later), going to go over the broad basics, Connection, Commands, RecordSets

I will not discuss DataBound controls since I feel they arenÆt very helpful for most people who really want to modify and present the data in any way they want to.

Connection objects will keep a connection to the database, either through the IP of a SQL server, the direct name of the file, or the DSN (Data Source Name) which allows you to just say DSN=whatever and the ODBC setup knows the rest.

First going to explain how to set up a DSN name for your database in your ODBC32 setting in control panel, this will help it be much easier to just open a connection, thing with this though is that you would have to create a DSN connection for any database you want, also you can setup DSN on your machine to access a SQL Server. But for not just Access is going to be done.

Goto Control Panel then click on ODBC32
Then Click on the system DSN tab
Now Click on Add
We are going to chose the first one (æMicrosoft Access Driver (*.mdb)Æ)
Then click Finish
Type in the name you would like to use for your DSN, for example, IÆll use inven
Description isnÆt important, unless you want to, then on the Database frame, click select
Point it to your Access database you want to use, in my case IÆll chose C:\sites\inventory.mdb
Click Ok
And you have now added your Data Source Name to your system (there are API Calls/commands where you can automatically add a DSN from visual basic when you chose to start distributing it with an Access database)

Now to create a connection object in Visual Basic

First in Visual Basic we must make sure we have the Active Data Object Reference included, goto Project, then goto Reference, then scroll down to Microsoft Active Data Object 2.1 (if you donÆt have 2.1 , get the next highest one)

a little word of warning: if you do not have Active Data Object 2.1 (I think 2.0 works too) then you will have problems working with Access2000 Database, which is why I only use office 97, just to not have to hassle with that

Ok now we got that, in visual basic lets create the connection object with this line

[tt]
æThis will make the connection object
Dim Aconn As New ADODB.Connection

æThis will open a connection to your DSN (I know there is extra options, but this works with the least effort)
Aconn.Open "DSN=inven", "sa", ""

æSa means System admin, access usually doesnÆt have a login password, but itÆll always accept sa, and blank for your login.
[/tt]

ok so we got our connection object, what do to with it now

lets create a Command object to so commands, that alter the database, but doesnÆt return a record set
Commands are nice for SQL commands such as æUpdateÆ æInsertÆ æDeleteÆ such commands that donÆt need a recordset returned.

[tt]
æthis creates the command object
Dim Acommand As New ADODB.Command
æthis tells the Command object which connection to use, you can even make it use a connection string like ôDSN=inven; uid=sa; pwd=ö so you wouldnÆt have to create a connection object, but I use a connection object so I can use several commands, and objects, without having to reconnect.

Acommand.ActiveConnection = Aconn
ætells the Command object, what command you want to execute
Acommand.CommandText = "Delete from Inventory where UserID=5"
æthis tells the command object, that the Command is a text command, like a SQL Query
æthe reason its good to tell it, is because, itÆll try everytime, and see which one works, then uses that
æbut we donÆt want to waste time for it to try out things, we tell it exactly what it is.
Acommand.CommandType = adCmdText
æand now to execute the command, if a record in the table Inventory has a User ID of 5, that record will have been deleted, these commands, should return something if it failed or not, but I am showing you a non-error checking method of just getting to the database and doing stuff.

Acommand.Execute

[/tt]

Now to create a recordset so we can actually get something out of our database

[tt]
Dim Arecord As New ADODB.Recordset
æthis has the Select string, then what connection to use, then what kind of cursor, then what kind of lock
æCursors are as so
æStatic cursor, can go backward, forward, can count how many records there are, but cant change the data, and doesnÆt get add, delete notification
æKeyset does the same as static, only it can add, update, and delete
æDynamic can do add update, delete, and gets add, update, delete notifications, but doesnÆt know how many records there, are, they is several types of cursors, which IÆll explain all later.

Arecord.Open "select * from Inventory", Aconn, adOpenKeyset, adLockReadOnly

æthis is a loop that keeps looping until the recordset hits the end of the file(or end of the set)
While Not Arecord.EOF
æthis adds the UserID, and the Name to the Listbox, at that record
List1.AddItem Arecord("UserID") & " " & Arecord("Name") & vbCrLf
æthen moves to the next record
Arecord.MoveNext
Wend
[/tt]

with a recordset you can heavily modify it to present only the data you want, so its rather powerful, what I have shown you here is a simple, get connection, and get the data. Check your Access help, for information on SQL commands, such as the ones IÆve mentioned above, as well as the numerous ways you can use them.