ADO Beginners Tutorial

G'day folks,

Just thought i'd attach my "An introduction to ADO" tutorial for anyone wanting to learn how to work with databases or wanting to learn how to use ADODB.

It is for beginners so it's not too complicated hopefully it's easy enough to read and understand.

It's password protected so just choose read-only when you open it.

cheers
beacon :)



added by si_the_geek:

Note that there are some bugs in this tutorial, you can find a corrected version of the tutorial in the thread: ADO Tutorial for Classic VB


If anyone wants to ask a question relating to the tutorial, the chances are that it has already been answered in this thread (so please read the replies!), or is in the thread ADO Beginners Tutorial, Some Further Steps.

If you cannot find an answer to your question, please create a new thread in the Database Development forum - several people who can help will read it.

Your ADO tutorial was unbelievably helpful. Hats off to you.

One question:

If my database file(DB1.mdb) is not on my harddrive and is actually on the internet, what changes do I have to make to the code in order to access it?

I've tried everything and nothing worked.

Thank you so much.

Again, your tutorial was superb.

You can get connection strings for ADO here:

http://www.able-consulting.com/ado_conn.htm

Yeah but they arent as helpful as my tutorial! :p ;)

Anyways instead of the pc name put in the ip address of the computer with the db in it!

Later
b :)

your file used password protect.
I can't read tell me password please.

Quote:

---

It's password protected so just choose read-only when you open it.

---

first read before asking! ;)


The WiseGuy

Great tutorial Beacon! You might wanna run through it with a spell check though ;) Also, first you say that you're going to refer to the table as 'table1' then you say 'tbl_master' throughout the document.

But well done. Very helpful :)

hehehe

Whoops!

Keeping ya on ya toes! :)
Thanks hobo!
b :)

Hiya Beacon
Loved the tutorial, was a big help.
Question I have tho, is I'm trying to make a table bigger than what access will allow me to create.
My table on my form is 20 rows deep by 10 columns wide, and each textbox in it needs a spot in the database.
You know a way around that?
Thx in advance

*by the way...something I added to yours on my own, (I was proud cuz I'm as newbie as newbie gets hehe), is that if you dont have a pre programmed database record when you go to start the project it'll come up with an error and shut down. So on Form_load I did this at the bottom....

If (rs.BOF = True Or rs.EOF = True) Then
Call Init
Else:
rs.MoveFirst 'moves to the first record
fillfields
End If

Sub Init() is where i have all my textboxes set at zero...for when a user first uses a program or is starting a new file, etc etc
Opens up fine with a record in it or not. :)
I've been teaching myself over the past 3 or 4 months with a few books and forums like this as I muddle my way thru my first project

Quote:

---

Originally posted by Kracked
Hiya Beacon
Loved the tutorial, was a big help.
Question I have tho, is I'm trying to make a table bigger than what access will allow me to create.
My table on my form is 20 rows deep by 10 columns wide, and each textbox in it needs a spot in the database.
You know a way around that?
Thx in advance

---

Hmm... I don't know if Beacon still visits this place.

AFAIK, Access has no such limits. Perhaps you're talking about the form you're creating in VB being limited by size? If such is the case, how about using a grid?

ADO help can be obtained here too now:

http://www.adoanywhere.com

You can get online help in the forum and download the worlds most powerful ADO Browser:

Mike Collier.

How can I submit to a different table or query, I used beacon's tutorial and I got the information from 2 different tables but now I need to submit to a different table.

Thank you

Store the values into variables.

Then, use the connection object's EXECUTE method to execute the SQL INSERT query.

can you also use ADO to link a database to a bar chart?

if so, what code is it/change?

thanks

Quote:

---

Originally posted by alx100
can you also use ADO to link a database to a bar chart?

if so, what code is it/change?

thanks

---

A chart is a separate control (and issue). If the chart in question has a provision of working with recordsets, then you probably need to just set the recordset there.

Otherwise, it probably works off arrays, so you should store the values from your recordset into an array and then pass the array to the chart.

hi, thanks there,

do you have any examples, or show how this code should be written?

i have never done this or MS Chart before,

thank you!

http://msdn.microsoft.com/library/de...artcontrol.asp

Hey can anyone point me to where I can download the adodb control? It's not in my components list. I've been searching and I can't find it...

Project > References

Don't know if this thread is still in use but there it goes anyway:

I got all my data in a dataset and now I'm trying to change 'field1' in 'table1'. I know there is a command dataset.select(..filter...) isn't there anything like dataset.update or something like that?

And what could I do to change a value of a row of a table?

Thanks in advance.

Use the DataAdapter's update method to update the db with changes made in the dataset.

Nope, I don't think that helps in my case. My database is an xml database, so I'm using no dataadapter, just the dataset. And the thing is that the change in the data comes from the user he clicks the button that says 'erase entry' and then I want the 'erased' field to be set to true. So I need to be able to do Dataset.Table.Change(...) if it existed, and then run Dataset.WriteXml(..).

So I wanna change the dataset and then write the xml.

Am I missing something I should be using like a dataadapter or something like that?

Thanks

what if you dont have ms access 2000. how can i save things to lables or text boxes or something?

This thread looks dead but I thought I'd try anyway.
I'm completely new at working with DBs.
I copied the code from Beacon's tut and added the reference.
Made a DB in Access 2002
One table with three fields.
Stored the table in the same folder as the VB project and altered the path in the form load event.

WHen I get to line:

Code:

---

cn.Open

---

I get:
'Run tome error -yada yada
Authentication failed'

Clicking 'Help' just says 'no help on subject'

Not a great start.
What do I do?

Show all the code you've used, not just that one line.

It all comes strait from the tutorial.
The only line I changed was the path to db1.mdb in Form_Load.

VB Code:

Option Explicit
 
Private cn As ADODB.Connection 'this is the connection
Private rs As ADODB.Recordset 'this is the recordset
 
 
Private Sub Form_Load()
    Me.MousePointer = 11 'this makes the mouse pointer the hourglass
    
    Set cn = New ADODB.Connection 'we've declared it as a ADODB connection lets set it.
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      App.Path & "\db1.mdb"  'this is the connection string explained in the notes section.
 
'The next line is where I get the error
    cn.Open
 
    Set rs = New ADODB.Recordset 'as we did with the connection
    rs.Open "tbl_master", cn, adOpenKeyset, adLockPessimistic, adCmdTable 'opening the recordset explained in the notes
           
    rs.MoveFirst 'moves to the first record
    Do Until rs.EOF = True 'this is the Loop to add items to the combo box
        combo1.AddItem rs.Fields("field1") 'this adds items from field1 into the combo box
        rs.MoveNext 'moves next record
    Loop
    rs.MoveFirst
    fillfields 'i'll explain this later on.
    
    Me.MousePointer = 0 'sets the mouse pointer to the normal arrow
End Sub
 
Public Sub fillfields()
    If Not (rs.BOF = True Or rs.EOF = True) Then 'Checks if we are at the first or last record. This is use a lot.
        Text1.Text = rs.Fields("Field2") 'text1 = field2 and display that data
        Text2.Text = rs.Fields("Field3") 'as above
        combo1.Text = rs.Fields("Field1") 'as above
    Else
        MsgBox "Either you are at the first record or the last record.", vbExclamation, "Cannot Move"
    End If
End Sub
 
Private Sub cmdPrev_Click()
    If Not (rs.BOF = True) Then
        rs.MovePrevious 'move previous record
        fillfields 'fill the controls
    End If
End Sub
 
Private Sub cmdNext_Click()
    If Not (rs.EOF = True) Then
        rs.MoveNext 'move to next record
        fillfields 'fill the controls
    End If
End Sub
 
Private Sub cmdAdd_Click()
    With rs
        .AddNew 'adding new record
        .Fields("field2") = Text1.Text 'setting field2 = whatever is typed in text1
        .Fields("field3") = Text2.Text 'as above
        .Fields("field1") = combo1.Text 'as above
        .Update 'this updates the recordset etc.
    End With
End Sub
 
Private Sub cmdDelete_Click()
    If MsgBox("Are you sure you want to delete this record?", vbYesNo + vbQuestion, "Delete?") = vbNo Then      'check if you really want to delete this record
        Exit Sub 'exit the command
    Else
        If Not (rs.BOF = True Or rs.EOF = True) Then
            rs.Delete 'delete the current record
            If Not (rs.BOF = True Or rs.EOF = True) Then
                rs.MoveNext 'move next
            If rs.EOF Then rs.MoveLast
                fillfields
            End If
        End If
    End If
End Sub
 
Private Sub Form_Unload(Cancel As Integer)
    rs.Close 'close the recordset
    cn.Close 'close the connection
    Set rs = Nothing  'set them to nothing
    Set cn = Nothing 'as above
End Sub

I've also attached the DB that I made to match the one Beacon discribes.
There's no info in it, just the structure.

P.S. I believe the cmdDelete_Click sub needs correcting.
But I didn't get far enough to test it.
It also comes strait from the tutorial.

It's been a while since I used Access, but the connectionstring doesn't look quite right to me.

Tg

You're right.

It should be

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
App.Path & "\db1.mdb"

That fixed it, I cut too much off when I changed the path.

Another question.
Is there a a help file for ADO?
If so where do I get it and how would you set it so that, when you get a error msg from a line with DB code, it will check the ADO help file?

I'm one of those weird people that actually use the Help Button.

On the tutorial. How to update the text on TEXT box by selecting the list on the COMBO box?

Quote:

---

Originally Posted by vocalmind

On the tutorial. How to update the text on TEXT box by selecting the list on the COMBO box?

---

I haven't looked at that code in a while.
Change the name of the textbox and combox in the code below so they match the tutorial code.

VB Code:

Private Sub Combo1_Change()
    Text1.Text = Combo1.Text
End Sub
 
Private Sub Combo1_Click()
    Text1.Text = Combo1.Text
End Sub

Thanks Beacon!

How do I edit it so that I can search the database for a term and it displays all the info for that term?

Quote:

---

Originally Posted by longwolf

VB Code:

Option Explicit
 
Private cn As ADODB.Connection 'this is the connection
Private rs As ADODB.Recordset 'this is the recordset
 
 
Private Sub Form_Load()
    Me.MousePointer = 11 'this makes the mouse pointer the hourglass
    
    Set cn = New ADODB.Connection 'we've declared it as a ADODB connection lets set it.
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      App.Path & "\db1.mdb"  'this is the connection string explained in the notes section.
 
'The next line is where I get the error
    cn.Open
 
    Set rs = New ADODB.Recordset 'as we did with the connection
    rs.Open "tbl_master", cn, adOpenKeyset, adLockPessimistic, adCmdTable 'opening the recordset explained in the notes
           
    rs.MoveFirst 'moves to the first record
    Do Until rs.EOF = True 'this is the Loop to add items to the combo box
        combo1.AddItem rs.Fields("field1") 'this adds items from field1 into the combo box
        rs.MoveNext 'moves next record
    Loop
    rs.MoveFirst
    fillfields 'i'll explain this later on.
    
    Me.MousePointer = 0 'sets the mouse pointer to the normal arrow
End Sub
 
Public Sub fillfields()
    If Not (rs.BOF = True Or rs.EOF = True) Then 'Checks if we are at the first or last record. This is use a lot.
        Text1.Text = rs.Fields("Field2") 'text1 = field2 and display that data
        Text2.Text = rs.Fields("Field3") 'as above
        combo1.Text = rs.Fields("Field1") 'as above
    Else
        MsgBox "Either you are at the first record or the last record.", vbExclamation, "Cannot Move"
    End If
End Sub
 
Private Sub cmdPrev_Click()
    If Not (rs.BOF = True) Then
        rs.MovePrevious 'move previous record
        fillfields 'fill the controls
    End If
End Sub
 
Private Sub cmdNext_Click()
    If Not (rs.EOF = True) Then
        rs.MoveNext 'move to next record
        fillfields 'fill the controls
    End If
End Sub
 
Private Sub cmdAdd_Click()
    With rs
        .AddNew 'adding new record
        .Fields("field2") = Text1.Text 'setting field2 = whatever is typed in text1
        .Fields("field3") = Text2.Text 'as above
        .Fields("field1") = combo1.Text 'as above
        .Update 'this updates the recordset etc.
    End With
End Sub
 
Private Sub cmdDelete_Click()
    If MsgBox("Are you sure you want to delete this record?", vbYesNo + vbQuestion, "Delete?") = vbNo Then      'check if you really want to delete this record
        Exit Sub 'exit the command
    Else
        If Not (rs.BOF = True Or rs.EOF = True) Then
            rs.Delete 'delete the current record
            If Not (rs.BOF = True Or rs.EOF = True) Then
                rs.MoveNext 'move next
            If rs.EOF Then rs.MoveLast
                fillfields
            End If
        End If
    End If
End Sub
 
Private Sub Form_Unload(Cancel As Integer)
    rs.Close 'close the recordset
    cn.Close 'close the connection
    Set rs = Nothing  'set them to nothing
    Set cn = Nothing 'as above
End Sub

---

Where is the code for "update data"..(cmdUpdate_Click)..please???

I'm sorry zach007 , it's been some time since I was on this code.

I think I started with the code from the tutorial then was making changes to it to learn.

Hi,

What do you mean by that? Would you please provide "update" codes for everybody to learn..??? :wave:

For "update" just use the same code as cmdAdd_Click, but without the AddNew line.

VB Code:

Private Sub cmdAdd_Click()
    With rs
        .AddNew 'adding new record
        .Fields("field2") = Text1.Text 'setting field2 = whatever is typed in text1
        .Fields("field3") = Text2.Text 'as above
        .Fields("field1") = combo1.Text 'as above
        .Update 'this updates the recordset etc.
    End With
End Sub

Hi, for "update" existing data....which code should I delete please..???

Thanks,

Jennifer :)

Just remove:

VB Code:

.AddNew 'adding new record

My database is password protected.

Where in the connection string is the password inserted please?

You need to add the part in bold (where *** is your password):

VB Code:

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source= c:\my documents\DB1.mdb[b];User Id=admin;Password=***;[/b]"

Thanks for the reply Si, I think I may have the Database incorrectly saved.

When I try to open it in code using the connection string I get the error message
"Cannot start your application. The workgroup information file is missing or opened exculsively by another user."

Any ideas?

Is the database open in another program? (if so, close it)

If not, it is possible that the connection string is still not right, see the link in my signature for examples of variations.

Thanks, I used a connection string from the link, and it seems to connect no bother now (doesn't complain anyway).

I still have an issue though, code is briefly:-

Dim cnn1 As New ADODB.Connection
Dim rstDetails As New ADODB.Recordset

MyPath = App.Path
MyApp = "\MyDb.mdb;"

Connstring = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & MyPath & MyApp & "Jet OLEDB:Database Password=MyPass;"

cnn1.Open Connstring

rstDetails.Open "Mailing List", cnn1, , , adCmdTable

***This line throws up an error saying it can't find this.
strName = rstDetails.Fields("First Name")
***
I pasted the cnn1 connection info below in case it is of interest.

I am assuming the Database has been connected to at this point, though it would be nice to extract some info confirming this.


Provider=Microsoft.Jet.OLEDB.4.0;Password="";User ID=Admin;Data Source=C:\DEEWorkingFolder\Encryption\Encrypt2.mdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password=MyPass;Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False

The database is connected, otherwise you would have got an error at the "cnn1.open" line.

The error about not being able to find "First Name" is due to that not being a field in your "Mailing List" table. Check you have the spelling correct, and that you are using the right table.

Like in adodc there is adodc1.refresh so whats in ado connection is it cn.refresh or any other thing ?

Quote:

---

My database is password protected.

Where in the connection string is the password inserted please?

---

conn="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & app.path & "/db1.mdb ;Persist Security Info=False;Jet OLEDB:Database Password=mikee_phil

Quote:

---

Originally Posted by mendhak

Store the values into variables.

Then, use the connection object's EXECUTE method to execute the SQL INSERT query.

---

Can you please give an example of how to do this is i have been stuck on this for days. :thumb:

See How can I add a record to a database? from the DB FAQ.

Quote:

---

Originally Posted by Muhammad Haris

Like in adodc there is adodc1.refresh so whats in ado connection is it cn.refresh or any other thing ?

---

You don't need to do anything other than open the recordset. You then simply use any code you want to work with/show the data.

I have used this tutorial and it has been great, but i was wondering how to Query another table

would i need to open a totaly new connection and have second connection to the database running ?

I have table1 which has a primary key ID this holds basilcy name and e-mail address i want to use this ID to link other tables.

I have table2 which i want to reference by the ID of the first table

So using this tutoral i have got the part working to display the info from table 1
now i also want to have it look up the ID in table2 and display any infomation it finds

I would assume the query could be somthing like

Select * from table2 where ID = "ID im looking up"

then basicly out put the data the same way as in table 1?

Im new at this sooo simple is good ;)
Any help on this would be apreciated

Welcome to VBForums! :wave:

You only need one connection - you can have multiple recordsets open thru it if you want.

Assuming that the data is "master-detail" (1 row in the first table relates to multiple entries in the second) then two recordsets is the way to go. Just declare another recordset object, and open it using the previous connection object.

If the data has a one-to-one relationship, you can use SQL to return both lots of data in the same recordset.

ok thanks i shall give it a go :)

Ok having a problem with searching, is there a like in built way to search the database, or am i going to have to dump it out to an array and loop the data to find a match ??

a code example would be helpful :)

Quote:

---

Originally Posted by AllanM

Ok having a problem with searching, is there a like in built way to search the database, or am i going to have to dump it out to an array and loop the data to find a match ??

a code example would be helpful :)

---

You can check my recent post, I asked something like that !

http://www.vbforums.com/showthread.php?t=390117

I hope this will help you !

yes looks pritty good, thanks for the reply

This part of the code, where it fills the combo box with all the record numbers

rs.MoveFirst 'moves to the first record
Do Until rs.EOF = True 'this is the Loop to add items to the combo box
combo1.AddItem rs.Fields("field1") 'this adds items from field1 into the combo box
rs.MoveNext 'moves next record

Can you do rs.move to a specific record in the record set? so if they were all indexed via a number i could just jump stright to that record or would i have to say so somthing like rs.bof and then increment a loop of rs.MoveNext how ever many times i wanted to get to the correct record

i currenly have a search which will display a record number and from this would like to be able to just called up that record for editing

You can move to a specific record using the .Move method, however this is not the recommended approach - as data taken from databases should not care about the position of records (theoretically if you return the same set of data twice it may not be in the same order).

It is better to use something unique about the record instead, and load data based on that. If you have an Autonumber or Identity field, that is the perfect candidate for this, and can be used as part of an SQL statement to load (or edit) only that particular record.

hello

i need step by step instruction about connection strings for ADO..

i want to build database using vb6 and ms access..

my problem now i cant link vb6 to ms access.

The tutorial shows how to connect to an Access database, all you should need to change is the file name/location.

If you need further help, see the Connection Strings link in my signature.

I'm trying to open a database w/ a password, I can open it fine when there's no password, but what code do I use to add the password?

this is the mods I made to the code so far, but is says that "Could not find installable ISAM"

VB Code:

Set cn = New ADODB.Connection 'we've declared it as a ADODB connection lets set it.
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source= c:\DB1.mdb;Jet OLEDB;Database Password=mypassword" 'this is the connection string explained in the notes section.
    cn.Open

Thanx

p.s. code snippets help me, I'm a real noob, and a visual learner :wave:


also, is there a way to change the password on the .mbd file inside the program? The problem I actually have is that the messaging program I'm making uses a text file to store lots of user settings, the problem is is that an idiot in my class tries to screw it up constantly, so when I boot him off of it, he has already made a copy of the program storring the info, so he just replaces it back, and he's back on. I want a way to change the password without having to re-compile the program to change the password, to render his old copy uselesss