INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Come Join Us!
Are you a
Computer / IT professional?
Join Tek-Tips now!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts
- Keyword Search
- One-Click Access To Your
Favorite Forums
- Automated Signatures
On Your Posts
- Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
Feedback
"...Congratulations on a brilliant idea and a great site..."
Geography
Where in the world do Tek-Tips members come from?
|
Visual Basic(Microsoft): Version 5 & 6 FAQ
|
Database
|
How to connection, change, and retrieve from an Access Database using ADO (works with other ODBC supported DBs as well)
Posted: 27 May 00
|
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.
Acommand.Execute
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.
|
Back to Visual Basic(Microsoft): Version 5 & 6 FAQ Index
Back to Visual Basic(Microsoft): Version 5 & 6 Forum
|
|
 |
|
Talk To Other Members
