I'm having difficulty transferring data entered in forms into the
relevant tables of my database. For reasons I'm not going into here I
am trying to do it by creating a form in design view with various text
boxes on it. I am writing code behind the button to send the data in
the text boxes on the form to the fields in the database. The problem
I am having is with this code. This is what I expected to work:


Private Sub Command4_Click()

Dim db As Database
Dim rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("Employees") 'Employees is the table

rst.AddNew
Text0.SetFocus 'We have to set focus for each field
MsgBox ("Text0=" & Text0.Text) 'Just see what we got
rst!FirstName = Text0.Text
Text2.SetFocus
MsgBox ("Text2=" & Text2.Text)
rst!LastName = Text2.Text
rst.Update
rst.Close

End Sub



However it won't let me write

dim db as database

as there is no databse option in the list that drops down. I have also
noticed that there is also not a DAO option. I don't understand enough
about access to understand why this is. I've tried converting it to
different formats, i.e. to access 1997 but these options are still not
available. I think I am using Access 2000.
I have another database in access 2000 which i wrote a while ago which
does have all these options available!
Would somebody PLEASE help me I'm tearing my hair out!!
Caroline

Access 2000. The Default database access method is ADO. You will need to (in the VBA IDE) go to
Tools>References. DeSelect the Microsoft ActiveX Data Access 2.x Library and Select Microsoft DAO
3.x or 4.x (depends on what you have) . Also, when you set an object reference in your code use the
format:

Dim db as DAO.Database
Dim rst as DAO.Recordset

instead of:

Dim db As Database
Dim rst As Recordset

Although, you have DeSelected the ADODB library, I still use the above format to make it patently
obvious to me when I revisit code at a future time, which access method was employed. If you
utilized both access methods in the same project, you Absolutely MUST specify DAO.Database or
ADODB.Database or your code will snarf.



"caroline" <caroline.goatly (AT) clearview-cctv (DOT) com> wrote

ADO is referenced and not DAO. this is the default for newer versions
of Access. to change it, you just need to go to the Visual Basic
Environment (VBE) and select Tools:References and find Microsoft DAO
[some number] object library. you need to use the highest number that
is consistently available between all boxes that will use the
database.

Consider, however, using ADO. ActiveX Data Objects is the replacement
for DAO, which may not always be supported. ADO is also more
consistently available across other products, should you choose to use
SQL Server, Visual Basic, etc.(or even and Acess ADP). it is a little
more wordy(since it requires explicitly making a connection to the
db), but provides better seperation of client and server, and in my
experience, is faster - especially across a network. if you already
know DAO, there is certainly some advantage in not learning the newer
technology, but if you don't know either, you may be better off
learning ADO.

hope that helps.

tom

caroline.goatly (AT) clearview-cctv (DOT) com (caroline) wrote in message news:<f8dae329.0308180633.180e458f (AT) posting (DOT) google.com>...

Thanks a lot, that was a great help!
Caroline