ADO Beginners Tutorial-VBForums
Page 1 of 2 12 LastLast
Results 1 to 40 of 63

Thread: ADO Beginners Tutorial

  1. #1

    Thread Starter
    PowerPoster Beacon's Avatar
    Join Date
    Jan 2001
    Location
    Pub Floor
    Posts
    3,188

    Smile 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.
    Attached Files Attached Files
    Last edited by si_the_geek; Jan 28th, 2009 at 06:11 PM.
    Share on Google+

  2. #2
    New Member
    Join Date
    Apr 2002
    Posts
    3
    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.
    Share on Google+

  3. #3
    ASP.NET Moderator mendhak's Avatar
    Join Date
    Feb 2002
    Location
    Ulaan Baator GooGoo: Frog
    Posts
    38,174
    You can get connection strings for ADO here:

    http://www.able-consulting.com/ado_conn.htm
    Share on Google+

  4. #4

    Thread Starter
    PowerPoster Beacon's Avatar
    Join Date
    Jan 2001
    Location
    Pub Floor
    Posts
    3,188
    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
    Share on Google+

  5. #5
    hiyuka
    Guest
    your file used password protect.
    I can't read tell me password please.
    Share on Google+

  6. #6
    Lively Member WiseGuy's Avatar
    Join Date
    Apr 2002
    Location
    Zierikzee, The Netherlands
    Posts
    98
    It's password protected so just choose read-only when you open it.
    first read before asking!


    The WiseGuy
    I stuck my head out of the window and got arrested for mooning!

    This Post is sponsored by my PC: PIII900, 512MBDimm/133, Seagate 40GB/7200 ATA100, LiteOn 12x DVD, Lite-On 32x12x40 CDrw, Elsa Geforce2 Ultra 64MB incl tv-out, SoundBlaster Live 1024, Ilyama A702HT Vision Master Pro410 17".
    O/S: Windows XP Professional (dutch)
    Internet: Cable (1Mbit connection)
    Share on Google+

  7. #7
    Stuck in the 80s The Hobo's Avatar
    Join Date
    Jul 2001
    Location
    Michigan
    Posts
    7,256
    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
    My evil laugh has a squeak in it.

    kristopherwilson.com
    Share on Google+

  8. #8

    Thread Starter
    PowerPoster Beacon's Avatar
    Join Date
    Jan 2001
    Location
    Pub Floor
    Posts
    3,188
    hehehe

    Whoops!

    Keeping ya on ya toes!
    Thanks hobo!
    b
    Share on Google+

  9. #9
    New Member
    Join Date
    Dec 2003
    Location
    canada
    Posts
    10

    Question

    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
    ~ Hardest working newbie on the net ~
    Share on Google+

  10. #10
    ASP.NET Moderator mendhak's Avatar
    Join Date
    Feb 2002
    Location
    Ulaan Baator GooGoo: Frog
    Posts
    38,174

    Re: Question

    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?
    Share on Google+

  11. #11
    Lively Member
    Join Date
    Apr 2002
    Location
    UK
    Posts
    90
    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.
    Share on Google+

  12. #12
    New Member
    Join Date
    Mar 2004
    Posts
    7
    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
    Share on Google+

  13. #13
    ASP.NET Moderator mendhak's Avatar
    Join Date
    Feb 2002
    Location
    Ulaan Baator GooGoo: Frog
    Posts
    38,174
    Store the values into variables.

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

  14. #14
    New Member
    Join Date
    Apr 2004
    Posts
    7
    can you also use ADO to link a database to a bar chart?

    if so, what code is it/change?

    thanks
    Share on Google+

  15. #15
    ASP.NET Moderator mendhak's Avatar
    Join Date
    Feb 2002
    Location
    Ulaan Baator GooGoo: Frog
    Posts
    38,174
    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.
    Share on Google+

  16. #16
    New Member
    Join Date
    Apr 2004
    Posts
    7
    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!
    Share on Google+

  17. #17
    Share on Google+

  18. #18
    Fanatic Member Nove's Avatar
    Join Date
    Jul 2004
    Posts
    736
    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...
    Share on Google+

  19. #19
    ASP.NET Moderator mendhak's Avatar
    Join Date
    Feb 2002
    Location
    Ulaan Baator GooGoo: Frog
    Posts
    38,174
    Project > References
    Share on Google+

  20. #20
    Lively Member
    Join Date
    Nov 2004
    Posts
    70

    Re: ADO Beginners Tutorial

    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.
    Share on Google+

  21. #21
    ASP.NET Moderator mendhak's Avatar
    Join Date
    Feb 2002
    Location
    Ulaan Baator GooGoo: Frog
    Posts
    38,174

    Re: ADO Beginners Tutorial

    Use the DataAdapter's update method to update the db with changes made in the dataset.
    Share on Google+

  22. #22
    Lively Member
    Join Date
    Nov 2004
    Posts
    70

    Re: ADO Beginners Tutorial

    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
    Share on Google+

  23. #23
    New Member
    Join Date
    Dec 2004
    Posts
    2

    Re: ADO Beginners Tutorial

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

  24. #24
    Frenzied Member longwolf's Avatar
    Join Date
    Oct 2002
    Posts
    1,343

    Re: ADO Beginners Tutorial

    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?
    Share on Google+

  25. #25
    ASP.NET Moderator mendhak's Avatar
    Join Date
    Feb 2002
    Location
    Ulaan Baator GooGoo: Frog
    Posts
    38,174

    Re: ADO Beginners Tutorial

    Show all the code you've used, not just that one line.
    Share on Google+

  26. #26
    Frenzied Member longwolf's Avatar
    Join Date
    Oct 2002
    Posts
    1,343

    Re: ADO Beginners Tutorial

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

    VB Code:
    1. Option Explicit
    2.  
    3. Private cn As ADODB.Connection 'this is the connection
    4. Private rs As ADODB.Recordset 'this is the recordset
    5.  
    6.  
    7. Private Sub Form_Load()
    8.     Me.MousePointer = 11 'this makes the mouse pointer the hourglass
    9.    
    10.     Set cn = New ADODB.Connection 'we've declared it as a ADODB connection lets set it.
    11.     cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    12.       App.Path & "\db1.mdb"  'this is the connection string explained in the notes section.
    13.  
    14. 'The next line is where I get the error
    15.     cn.Open
    16.  
    17.     Set rs = New ADODB.Recordset 'as we did with the connection
    18.     rs.Open "tbl_master", cn, adOpenKeyset, adLockPessimistic, adCmdTable 'opening the recordset explained in the notes
    19.            
    20.     rs.MoveFirst 'moves to the first record
    21.     Do Until rs.EOF = True 'this is the Loop to add items to the combo box
    22.         combo1.AddItem rs.Fields("field1") 'this adds items from field1 into the combo box
    23.         rs.MoveNext 'moves next record
    24.     Loop
    25.     rs.MoveFirst
    26.     fillfields 'i'll explain this later on.
    27.    
    28.     Me.MousePointer = 0 'sets the mouse pointer to the normal arrow
    29. End Sub
    30.  
    31. Public Sub fillfields()
    32.     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.
    33.         Text1.Text = rs.Fields("Field2") 'text1 = field2 and display that data
    34.         Text2.Text = rs.Fields("Field3") 'as above
    35.         combo1.Text = rs.Fields("Field1") 'as above
    36.     Else
    37.         MsgBox "Either you are at the first record or the last record.", vbExclamation, "Cannot Move"
    38.     End If
    39. End Sub
    40.  
    41. Private Sub cmdPrev_Click()
    42.     If Not (rs.BOF = True) Then
    43.         rs.MovePrevious 'move previous record
    44.         fillfields 'fill the controls
    45.     End If
    46. End Sub
    47.  
    48. Private Sub cmdNext_Click()
    49.     If Not (rs.EOF = True) Then
    50.         rs.MoveNext 'move to next record
    51.         fillfields 'fill the controls
    52.     End If
    53. End Sub
    54.  
    55. Private Sub cmdAdd_Click()
    56.     With rs
    57.         .AddNew 'adding new record
    58.         .Fields("field2") = Text1.Text 'setting field2 = whatever is typed in text1
    59.         .Fields("field3") = Text2.Text 'as above
    60.         .Fields("field1") = combo1.Text 'as above
    61.         .Update 'this updates the recordset etc.
    62.     End With
    63. End Sub
    64.  
    65. Private Sub cmdDelete_Click()
    66.     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
    67.         Exit Sub 'exit the command
    68.     Else
    69.         If Not (rs.BOF = True Or rs.EOF = True) Then
    70.             rs.Delete 'delete the current record
    71.             If Not (rs.BOF = True Or rs.EOF = True) Then
    72.                 rs.MoveNext 'move next
    73.             If rs.EOF Then rs.MoveLast
    74.                 fillfields
    75.             End If
    76.         End If
    77.     End If
    78. End Sub
    79.  
    80. Private Sub Form_Unload(Cancel As Integer)
    81.     rs.Close 'close the recordset
    82.     cn.Close 'close the connection
    83.     Set rs = Nothing  'set them to nothing
    84.     Set cn = Nothing 'as above
    85. 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.
    Attached Files Attached Files
    Last edited by longwolf; May 15th, 2005 at 07:59 PM.
    Share on Google+

  27. #27
    PowerPoster
    Join Date
    May 2002
    Posts
    25,155

    Re: ADO Beginners Tutorial

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

    Tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *
    Share on Google+

  28. #28
    ASP.NET Moderator mendhak's Avatar
    Join Date
    Feb 2002
    Location
    Ulaan Baator GooGoo: Frog
    Posts
    38,174

    Re: ADO Beginners Tutorial

    You're right.

    It should be

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

  29. #29
    Frenzied Member longwolf's Avatar
    Join Date
    Oct 2002
    Posts
    1,343

    Re: ADO Beginners Tutorial

    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.
    Share on Google+

  30. #30
    New Member
    Join Date
    Sep 2005
    Posts
    13

    Re: ADO Beginners Tutorial

    On the tutorial. How to update the text on TEXT box by selecting the list on the COMBO box?
    Last edited by vocalmind; Sep 11th, 2005 at 05:07 AM.
    Share on Google+

  31. #31
    Frenzied Member longwolf's Avatar
    Join Date
    Oct 2002
    Posts
    1,343

    Re: ADO Beginners Tutorial

    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:
    1. Private Sub Combo1_Change()
    2.     Text1.Text = Combo1.Text
    3. End Sub
    4.  
    5. Private Sub Combo1_Click()
    6.     Text1.Text = Combo1.Text
    7. End Sub
    Last edited by longwolf; Sep 11th, 2005 at 08:50 AM.
    Share on Google+

  32. #32
    Addicted Member
    Join Date
    Sep 2005
    Posts
    200

    Re: ADO Beginners Tutorial

    Thanks Beacon!
    Share on Google+

  33. #33
    Lively Member lavarock09's Avatar
    Join Date
    Jun 2005
    Posts
    124

    Re: ADO Beginners Tutorial

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

  34. #34
    Addicted Member
    Join Date
    Nov 2005
    Posts
    185

    Re: ADO Beginners Tutorial

    Quote Originally Posted by longwolf
    VB Code:
    1. Option Explicit
    2.  
    3. Private cn As ADODB.Connection 'this is the connection
    4. Private rs As ADODB.Recordset 'this is the recordset
    5.  
    6.  
    7. Private Sub Form_Load()
    8.     Me.MousePointer = 11 'this makes the mouse pointer the hourglass
    9.    
    10.     Set cn = New ADODB.Connection 'we've declared it as a ADODB connection lets set it.
    11.     cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    12.       App.Path & "\db1.mdb"  'this is the connection string explained in the notes section.
    13.  
    14. 'The next line is where I get the error
    15.     cn.Open
    16.  
    17.     Set rs = New ADODB.Recordset 'as we did with the connection
    18.     rs.Open "tbl_master", cn, adOpenKeyset, adLockPessimistic, adCmdTable 'opening the recordset explained in the notes
    19.            
    20.     rs.MoveFirst 'moves to the first record
    21.     Do Until rs.EOF = True 'this is the Loop to add items to the combo box
    22.         combo1.AddItem rs.Fields("field1") 'this adds items from field1 into the combo box
    23.         rs.MoveNext 'moves next record
    24.     Loop
    25.     rs.MoveFirst
    26.     fillfields 'i'll explain this later on.
    27.    
    28.     Me.MousePointer = 0 'sets the mouse pointer to the normal arrow
    29. End Sub
    30.  
    31. Public Sub fillfields()
    32.     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.
    33.         Text1.Text = rs.Fields("Field2") 'text1 = field2 and display that data
    34.         Text2.Text = rs.Fields("Field3") 'as above
    35.         combo1.Text = rs.Fields("Field1") 'as above
    36.     Else
    37.         MsgBox "Either you are at the first record or the last record.", vbExclamation, "Cannot Move"
    38.     End If
    39. End Sub
    40.  
    41. Private Sub cmdPrev_Click()
    42.     If Not (rs.BOF = True) Then
    43.         rs.MovePrevious 'move previous record
    44.         fillfields 'fill the controls
    45.     End If
    46. End Sub
    47.  
    48. Private Sub cmdNext_Click()
    49.     If Not (rs.EOF = True) Then
    50.         rs.MoveNext 'move to next record
    51.         fillfields 'fill the controls
    52.     End If
    53. End Sub
    54.  
    55. Private Sub cmdAdd_Click()
    56.     With rs
    57.         .AddNew 'adding new record
    58.         .Fields("field2") = Text1.Text 'setting field2 = whatever is typed in text1
    59.         .Fields("field3") = Text2.Text 'as above
    60.         .Fields("field1") = combo1.Text 'as above
    61.         .Update 'this updates the recordset etc.
    62.     End With
    63. End Sub
    64.  
    65. Private Sub cmdDelete_Click()
    66.     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
    67.         Exit Sub 'exit the command
    68.     Else
    69.         If Not (rs.BOF = True Or rs.EOF = True) Then
    70.             rs.Delete 'delete the current record
    71.             If Not (rs.BOF = True Or rs.EOF = True) Then
    72.                 rs.MoveNext 'move next
    73.             If rs.EOF Then rs.MoveLast
    74.                 fillfields
    75.             End If
    76.         End If
    77.     End If
    78. End Sub
    79.  
    80. Private Sub Form_Unload(Cancel As Integer)
    81.     rs.Close 'close the recordset
    82.     cn.Close 'close the connection
    83.     Set rs = Nothing  'set them to nothing
    84.     Set cn = Nothing 'as above
    85. End Sub
    Where is the code for "update data"..(cmdUpdate_Click)..please???
    Share on Google+

  35. #35
    Frenzied Member longwolf's Avatar
    Join Date
    Oct 2002
    Posts
    1,343

    Re: ADO Beginners Tutorial

    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.
    Share on Google+

  36. #36
    Addicted Member
    Join Date
    Nov 2005
    Posts
    185

    Re: ADO Beginners Tutorial

    Hi,

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

  37. #37
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,116

    Re: ADO Beginners Tutorial

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

  38. #38
    Addicted Member
    Join Date
    Nov 2005
    Posts
    185

    Re: ADO Beginners Tutorial

    VB Code:
    1. Private Sub cmdAdd_Click()
    2.     With rs
    3.         .AddNew 'adding new record
    4.         .Fields("field2") = Text1.Text 'setting field2 = whatever is typed in text1
    5.         .Fields("field3") = Text2.Text 'as above
    6.         .Fields("field1") = combo1.Text 'as above
    7.         .Update 'this updates the recordset etc.
    8.     End With
    9. End Sub

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

    Thanks,

    Jennifer
    Share on Google+

  39. #39
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,116

    Re: ADO Beginners Tutorial

    Just remove:
    VB Code:
    1. .AddNew 'adding new record
    Share on Google+

  40. #40
    Member
    Join Date
    Oct 2002
    Posts
    54

    Re: Database Password Protection

    My database is password protected.

    Where in the connection string is the password inserted please?
    Share on Google+

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.