Library tutorials & articles
DAO 3.6 Library
By Louis Rose, published on 11 Jun 2002
A Worked Example
So now you have a wonderful library that helps you to add, edit and delete records.
You've written a Default Database module too that links the library with your
Access database. But how does it all tie together?
Below, I'll show you the code that will add two records to the people table, edit their fields and then delete one of the records. See if you can figure out how to do the same to the animals table. For now, you can just stick this in your Form_Load procedure. It may be useful to use the debugging tools to step through the code and see exactly what is going on.
If you check your database in Access now, you'll see that VB has added one person to the People database and the values of the fields (from left to right) will be: 2, Laura, Hickson
In fact, we added me to the database too, but then promptly deleted me. (Humph!)
I hope this library will give you some help and make using DAO 3.6 a doddle.
Happying DAOing!
Louis Rose
Below, I'll show you the code that will add two records to the people table, edit their fields and then delete one of the records. See if you can figure out how to do the same to the animals table. For now, you can just stick this in your Form_Load procedure. It may be useful to use the debugging tools to step through the code and see exactly what is going on.
Dim strPath as String
Dim intNewRecord As Integer
Dim intReturn As Integer
Dim intRecordToDelete As Integer
strPath = "C:\My Documents\Test.mdb"
LoadDatabase strPath
intNewRecord = AddNewRecord(rstPeople)
intReturn = ChangeField(rstPeople, intNewRecord, "FirstName", "Louis")
intReturn = ChangeField(rstPeople, intNewRecord, "LastName", "Rose")
intRecordToDelete = intNewRecord
intNewRecord = AddNewRecord(rstPeople)
intReturn = ChangeField(rstPeople, intNewRecord, "FirstName", "Laura")
intReturn = ChangeField(rstPeople, intNewRecord, "LastName", "Hickson")
intReturn = DeleteRecord(rstPeople, intRecordToDelete)
If you check your database in Access now, you'll see that VB has added one person to the People database and the values of the fields (from left to right) will be: 2, Laura, Hickson
In fact, we added me to the database too, but then promptly deleted me. (Humph!)
I hope this library will give you some help and make using DAO 3.6 a doddle.
Happying DAOing!
Louis Rose
Related articles
Related discussion
-
VB6 Runtime error 381 subsript out of range Error
by Uncle (2 replies)
-
passing and reading parameters from using Shell
by jigartoliya (0 replies)
-
Convert C++ code to VB6
by mawcot (4 replies)
-
listbox scrollbar
by Dennijr (10 replies)
-
Can you describe Above simple VB6 code?
by pramodmca09 (0 replies)
Related podcasts
-
Christian Beauclair
14 mai 2008 (�mission #0074) ::.Christian Beauclair: Stratégies de migration VB6 vers .NET Nous discutons avec Christian Beauclair des stratégies de migration VB6 vers .NET. Entre autres, nous discutons comment utiliser le "VB 6 Code Advisor" et le "Interop Forms Toolkit" pour ajouter la puiss...
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
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
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.
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
What? Where the heck did that come from?
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.
Post the highlighted statement from the debugger
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.
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:
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.
Regards,
NJ
So, the SQL, "SELECT * FROM Animals WHERE Animals.Name = 'Fido'" becomes:
where myRecordset has previously been bound to the Animals table of your database. Using a statement similar to:
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
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
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)
I did as instructed and Still get the error any sugestions??
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
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
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).
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.
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?
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.
...error message. All I get is Error message: User-defined type not defined when I try to run this example.
Any thoughts? 
bruwmac
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:
if UCase$(txtLogin.text) = LoginInDataBase then ...
what should i put instead of LoginInDataBase
PLEASE HELP!!! i gotta hand in my project soon!!!
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.
I refresh back that when I delete a recordset in database. if the few row of the data same all GONE.....
Thanks webjose... Some very good points. I shall revise this article when I have finished my exams to include your points. Thanks again.
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.
This thread is for discussions of DAO 3.6 Library.