Library tutorials & articles
DAO 3.6 Library
Adding Records
Now we'll add a function which will allow use to add a new record to any table
we wish.
NB: A function is a special type of procedure, which returns a value. Most of you will have used functions in VB, maybe unwittingly. MsgBox is a function but most of the time, its return value is disregarded. Because a function provides a return value, it (like a variable) has a data type.
Here's the code we'll use for our AddNewRecord Function:
As you can see, this function returns an Integer. This is determined by the As Integer statement at the end of the first line. This function will be passed just one variable, a reference to the RecordSet (a posh name for a table) which we want to add the record to.
The With statement just means that everywhere I put a . VB replaces it with a rstTable. This makes the code smaller and easier to read.
The next thing the code does is to requery the table - this just means VB makes sure it is looking at the latest copy of the table. The IF statement checks to see if there are any records in the current table, if not then obviously the record number of the new record will be 1. Otherwise, VB cycles through every record and looks for a gap. Eg, say we have records 1,2,3,5,6,7 - this code will spot that there is a gap - ie record number 4.
If no gaps are found, the new record is given the number of the highest record + 1.
The next chunk of code, adds a new record and sets the ID field to the value that we decided to use and updates the changes.
The last line ensures that the function returns the ID of the new record.
So, in summary to add a new record to a table use:
Here, intReturn will contain the ID of the record which has been added. rstTableToAddRecordTo is the recordset of the table which you wish to add the record to. Don't worry about this too much, we'll cover this later.
NB: A function is a special type of procedure, which returns a value. Most of you will have used functions in VB, maybe unwittingly. MsgBox is a function but most of the time, its return value is disregarded. Because a function provides a return value, it (like a variable) has a data type.
Here's the code we'll use for our AddNewRecord Function:
Public Function AddNewRecord(rstTable As Recordset) As Integer
Dim intCount As Integer
Dim intNextRecordID As Integer
With rstTable
.Requery
If .BOF = True And .EOF = True Then
intNextRecordID = 1
Else
.MoveLast
.MoveFirst
For intCount = 1 To .RecordCount
If .Fields("ID").Value
<> intCount Then
intNextRecordID
= intCount
Exit
For
End If
.MoveNext
Next intCount
If intNextRecordID = 0 Then
intNextRecordID = .RecordCount + 1
End If
.AddNew
.Fields("ID").Value = intNextRecordID
.Update
End With
AddNewRecord = intNextRecordID
End Function
As you can see, this function returns an Integer. This is determined by the As Integer statement at the end of the first line. This function will be passed just one variable, a reference to the RecordSet (a posh name for a table) which we want to add the record to.
The With statement just means that everywhere I put a . VB replaces it with a rstTable. This makes the code smaller and easier to read.
The next thing the code does is to requery the table - this just means VB makes sure it is looking at the latest copy of the table. The IF statement checks to see if there are any records in the current table, if not then obviously the record number of the new record will be 1. Otherwise, VB cycles through every record and looks for a gap. Eg, say we have records 1,2,3,5,6,7 - this code will spot that there is a gap - ie record number 4.
If no gaps are found, the new record is given the number of the highest record + 1.
The next chunk of code, adds a new record and sets the ID field to the value that we decided to use and updates the changes.
The last line ensures that the function returns the ID of the new record.
So, in summary to add a new record to a table use:
Dim intReturn As Integer
intReturn = AddNewRecord(rstTableToAddRecordTo)
Here, intReturn will contain the ID of the record which has been added. rstTableToAddRecordTo is the recordset of the table which you wish to add the record to. Don't worry about this too much, we'll cover this later.
Related articles
Related discussion
-
Regarding Visual Basic Programme
by manjunathsl2007 (0 replies)
-
how do you hide all in VB6
by CapnJack (1 replies)
-
Problem with Input File
by novavb6 (3 replies)
-
How to produce a txt file with a table??
by novavb6 (1 replies)
-
VB6 compatability from XP to Vista
by bronx (1 replies)
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.
Thanks for your help. Could you tell me how do I join two tables using recordset.findnext ?
Regards,
NJ
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:
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 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...
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).
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.
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
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:
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.
to start using DAO...i find it better since it allows you the familiar "object-property" programming style.
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.