ADO Beginners Tutorial

[Beacon]

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.

[BTi]

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.

[mendhak]

You can get connection strings for ADO here:

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

[Beacon]

Yeah but they arent as helpful as my tutorial!

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.

[WiseGuy]

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

first read before asking!


The WiseGuy

[The Hobo]

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

[Beacon]

hehehe

Whoops!

Keeping ya on ya toes!
Thanks hobo!
b

[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

*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

[mendhak]

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?

[Mike Collier]

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.

[Adnane]

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

[mendhak]

Store the values into variables.

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

[alx100]

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

if so, what code is it/change?

thanks

[mendhak]

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.

[alx100]

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!

[mendhak]

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

[Nove]

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...

[mendhak]

Project > References

[nacho2]

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.

[mendhak]

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

[nacho2]

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

[Broken Soul]

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

[longwolf]

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:

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?

[mendhak]

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

[longwolf]

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

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.

[techgnome]

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

Tg

[mendhak]

You're right.

It should be

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

[longwolf]

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.

[vocalmind]

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

[longwolf]

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.

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

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

[manofsteel00]

Thanks Beacon!

[lavarock09]

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

[zach007]

Originally Posted by longwolf

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???

[longwolf]

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.

[zach007]

Hi,

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

[si_the_geek]

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

[zach007]

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

[si_the_geek]

Just remove:

.AddNew 'adding new record 
 

[slaphead109]

My database is password protected.

Where in the connection string is the password inserted please?