1. Introduction
2. Getting Started
3. The Example Database
4. Creating the module
5. Adding Records
6. Deleting Records
7. Editing Records
8. RefreshSQL
9. A Worked Example

Comments

1. mohammed minhaj ali
   12 Jun 2008 at 18:32

   I am working in VB6 with an Access db and would like to delete records from a join table. Can you help me? I have a table of Products, and table of Materials and a table to join the two using Product and Material IDs. I want to delete the records for a selected material to then delete the material.

    Thanks

2. faisal wadood
   20 Feb 2007 at 05:21

   hi sir!

   i hav read your article fully,i also read many articles on vb databases i understand it well yet i hav have a problem if you kindly help me.

   i want to develop a vb application that will takes data as input ,interpret that data and then display that data in complex spreadsheat like Excel. would you kindly till me what i do with it.

   thanks

   Regards  Faisalwadood.      Email id: faisalwadood2002@yahoo.com

3. N.K. Rathnayke
   13 Jul 2006 at 13:39

   Dear Sir,

   I want to know How to Open Password  with MsAccess DataBase at Runtime in DAO (Data Acsses Object)

   Please send me kind reply to my E-Mail  (I am wating...)

   My E-mail Address   : nishantha_213@yahoo.com

   Thank you,

   Nishantha.

    

4. Stephen Mckie
   14 Jul 2004 at 05:05

   This tutorial has been really good for helping me understand how to use access databases, which is really cool, but unfortunately I'm having a problem.
   

   
   When writing the code for moddefaultdatabase I set rstpeople and rstanimals as RecordSet's as shown, but when I come to set them, as show lower down in the example, I can't. The actual problem occurs during run time (error code 13) and I'm told i have a type mismatch.
   

   
   I've tried editing out the "set rstpeople ="/"rstanimals =" bits and just left the db.OpenRecordset(strSQL) and the program will compile past this point. I've also put an if staement in that will only run the offending code IF the db is actually an opened database, so I'm pretty certain the database is open to access.
   

   
   I just can't set the recordset's to the any tables in the database.
   

   
   Any ideas?
   

   
   Ste

5. bruwmac
   22 Jun 2004 at 14:14

   What? Where the heck did that come from?
   

   
   

6. Kirk
   22 Jun 2004 at 05:47

   Sorry My response to the issue at hand was overwritten when I pasted the last statement.
   

   
   I found it; it was just a typo.
   

   
   Who will guard the guards?
   The Special Forces ie. Navy Seals, Rangers, Para-rescue, and the Green Berets.
   You dont mess with them.

7. Mitch Dusina
   21 Jun 2004 at 19:52

   Post the highlighted statement from the debugger

8. Bambo Baderin
   01 May 2004 at 05:24

   
           I really want to commend the effort of the managers of developer fusion, I have really benefitted alot from this site.
   

   
           It is only the good Lord that can reward you, please keep up the good work. You won't breakdown but you will breakthrough and break records.
   

   
                                                                                             BRAVO.

9. Louis Rose
   20 Mar 2004 at 05:16
   Quote:

   Thanks for your help. Could you tell me how do I join two tables using recordset.findnext ?

   
   
   You can't.
   
   As I said, the string passed to FindNext is the SQL predicate. That's the bit after the WHERE clause. JOIN clauses come before the WHERE clause, so you can't do a JOIN with FindNext.
   
   Do the JOIN when you open your recordset:
   
   Code:

   Set myRecordset = myDatabaseConnection.OpenRecordset("SELECT * FROM Animals JOIN People ON Animals.OwnerID = People.ID")

   
   
   If you feel you need to brush up on your SQL, there's some good tutorials on the web. James Crowley has one for this very site. And there's a good tutorial  from w3schools.
   
10. njpal
    19 Mar 2004 at 08:23
    Thanks for your help. Could you tell me how do I join two tables using recordset.findnext ?
    
    Regards,
    NJ
11. Louis Rose
    19 Mar 2004 at 04:21
    If I remember rightly, the parameter to FindNext should be a string, which takes the form of an SQL predicate (the bit after the WHERE keyword).
    
    So, the SQL, "SELECT * FROM Animals WHERE Animals.Name = 'Fido'" becomes:
    
    Code:

    myRecordset.FindNext("Animals.Name = 'Fido'")

    
    
    where myRecordset has previously been bound to the Animals table of your database. Using a statement similar to:
    
    Code:

    Set myRecordset = myDatabaseConnection.OpenRecordset("SELECT * FROM Animals")

    
    
12. njpal
    18 Mar 2004 at 12:35

    Hi Louis,
    

    
    Thanks for informing me about it. But it would be great if you could also provide with an example. I mean where do we write the Select * from... statement, in case I am accessing the database using the Jet engine and searching through the database using the Recordset.FindNext command.
    

    
    Regards,
    NJ

13. Louis Rose
    17 Mar 2004 at 15:29

    Quote:

    You can also use the LIKE connector as:
    

    
    quer = "Name" & " LIKE '" & Text1.text & "*'"
    data1.recordset.findnext(quer)

    
    

    
    In fact, you can use any valid SQL syntax. Search this site and you'll come up with at least one good article on SQL. Alternatively, Google for it or get a decent Database book. Any of the following are superb reads...
    

    
    An Introduction To Database Systems - CJ Date (Date is the Database Guru).
    Fundamentals Of Database Systems - Elmasri & Navathe
    Database Principles, Programming And Performance -  O'Neil & O'Neil
    

    
    

14. njpal
    17 Mar 2004 at 03:54

    You can use a variable instead of a value...
    

    
    Just declare a variable, say quer as string, and let's say you would like to use the value typed in text box, Text1 and the field name is 'Name'. Use the following:
    

    
    quer = "Name" &  "='" & Text1.text & "'"
    data1.recordset.findnext(quer)
    

    
    You can also use the LIKE connector as:
    

    
    quer = "Name" & " LIKE '" & Text1.text & "*'"
    data1.recordset.findnext(quer)

15. Trunks9809
    23 Feb 2004 at 15:35
    I think that what he wants is a database with 2 tables: one with user name & one with details... and he wants the details to be automatically entered (by an input of some sort) in both tables correctly, when a new user is added...
16. Kirk
    25 Nov 2003 at 10:29

    I did as instructed and Still get the error any sugestions??
    

17. Sascha Ehrentraut
    14 Apr 2003 at 13:18

    It could be quit a lot of things. First I would check my connection string.
    I wants had some error when working with access. The reason was that the acces database was desighn in a newer ver. then to the one i had on the computer. Im not sure if that will help you?
    To check connection string try using the adodc control to make a connection to your DB if that works copy and past the connection string into your code check if it is the same.
    

    
    hope that helps..
    

    
    sascha

18. Sascha Ehrentraut
    14 Apr 2003 at 13:12

    Id like to help you. I think I could but im to to sure what you mean?
    Do you want A table to be created for each separet user ? If so why and maby I can give you a solution?
    

    
    sascha

19. genius_supreme
    06 Mar 2003 at 09:54

    i planning to do a database
    

    
    actually i found out that this method would be easier but i dont know how to do it!
    

    
    this is what i want.
    im developing a user-login-database
    

    
    1. when a user logs in.. the tables for his id should be displayed.
    

    
    let me make it simple :when a new user is added to the existing table (tblUserList)
    

    
    2. another table(tlbuserIDDetail) should be created using the UserId as the table name
      (including the fields like FirstName, MidName,LastName and so on) automaticly when
      the New user id is added to table tblUserList.
    

    
    i think i can do the part (1) if you guys can help me with the part (2).
    

20. RMcHugh
    14 Jan 2003 at 16:30
    You can access an id field using the data control:
    data1.recordeset.findfirst ("ID=3") assuming ID to be a field in the recordset.
    I would like to know if you can use a variable instead of a value - can't find the correct syntax.
21. JH
    09 Jan 2003 at 09:32

    I just started my first database project, and this article helped me understand alot of the basics involved with DB programming.  I would just like to say thanks first.
    

    
    Now, to the point.  I am using a Database created in access 2000, with 3 tables.  Whenever the program tries to bind the DB variable to the database, I keep getting an error that says:
    

    
    RunTime Error:'3423'
    

    
    You cannot use ODBC to import from, export to, or link an external Microsoft Jet or ISAM database table to your database.
    

    
    Can you please explain why it's doing this, and what I have to do to fix this?

22. bruwmac
    27 Dec 2002 at 18:47

     Thanks much. Happy New Year.

23. aramsuve
    26 Dec 2002 at 20:43

    The functions referenced in the example require the DAO3.6 library. To access this library you need to :
    

    
    a. Go to Tools->References in your VB Editor
    b. Locate "Microsoft  DAO 3.6 Object Library" and check the box next to it.
    

    
    If you do not find it, you can click the Browse button to search for [C/D/E]:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll.
    

    
    

24. bruwmac
    22 Dec 2002 at 19:50

    ...error message. All I get is Error message: User-defined type not defined when I try to run this example.   Any thoughts?
    

    
    bruwmac

25. r00miss
    20 Dec 2002 at 22:41
    hello!
    I didn't get how can i view information from the database and how can i check it... i mean let's say i have login page, so i write:
    Code:

    
    if UCase$(txtLogin.text) = LoginInDataBase then ...
    

    
    
    what should i put instead of LoginInDataBase
    
    PLEASE HELP!!! i gotta hand in my project soon!!!
26. lammie
    05 Dec 2002 at 10:33

    What is the code for moving to a record using the ID field.  For example, I was to display information for the record with the ID 3.  What code would move to the record with the ID 3.  I know everything else I need. Thanks.

27. fanen
    09 Nov 2002 at 04:44
    to start using DAO...i find it better since it allows you the familiar "object-property" programming style.
    
28. Vincent Chan
    19 Jun 2002 at 00:48

    I refresh back that when I delete a recordset in database. if the few row of the data same all GONE.....

29. Louis Rose
    11 Jun 2002 at 17:14

    Thanks webjose... Some very good points. I shall revise this article when I have finished my exams to include your points. Thanks again.

30. Jose Pablo Ramirez Vargas
    11 Jun 2002 at 14:57

    Good explanation, although I must point a few things:
    

    
    1.  To delete all records from a database, going 1 by 1 can be a very time-consuming procedure.  Instead, you should consider using a Delete SQL statement.
    2.  The ChangeField function can become repetitive:  What if I have to change several fields.  You could consider using ParamArray for the parameters of the ChangeField function so you can update more than one field at once.
    3.  There is no need (that I know of) to open a table using SQL if you're not using where or order by or that sort of stuff.  You can open the table directly using the table name (query names too) in the call to openrecordset.  Also, you should always specify the cursor type, as some people might expect one type but could get another.  I think the default is snapshot?  Dynaset cursors are best, if you don't have a problem with resources.
    4.  The AddNewrecord function can take a long time to run?
    5.  You use the integer data type for handling ID's, but you should use Long data types because ID's can go beyond 32767, the upper limit of a integer value in VB.

31. Developer Fusion Bot
    01 Jan 1999 at 00:00

    This thread is for discussions of DAO 3.6 Library.