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
æ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.
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.
æ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.
Now to create a recordset so we can actually get something out of our database
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
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.