Alain said:
Hello to all,
I would like to know how I can use the
"CurrentProject.Connection.Execute" command to add new record in a
table and also to delete record in a table. I am faily new at the ADO
so I need to learn. BTW I'm using Access 2000 so I beleive this is
the best technology for the time being or am I wrong ?, The company
I'm currently working for is not quite ready to upgrade to a newer
version
Thanks for the learning info
Alain
It's really quite simple, provided that you can build an appropriate SQL
statement to do the insert or append. For the append query, you have to
be able to provide the values for the fields of the record you want to
insert, either as literal values or by selecting them from another
table; for the delete query, you have to be able to identify by key the
record you want to delete. For example:
CurrentProject.Connection.Execute _
"INSERT INTO MyTable (ID, Description) " & _
"VALUES(123, 'blah, blah')"
CurrentProject.Connection.Execute _
"DELETE * FROM MyTable WHERE ID=123"
Or, picking up field values from variables:
Dim lngID As Long
Dim strDescription As String
lngID = 123
strDescription = "blah, blah"
CurrentProject.Connection.Execute _
"INSERT INTO MyTable (ID, Description) " & _
"VALUES(" & lngID & ", '" & strDescription & "')"
CurrentProject.Connection.Execute _
"DELETE * FROM MyTable WHERE ID=" & lngID
As for whether this is the best technology to learn, that depends. If
you are storing your data in Access .mdb files (Jet databases), not in
SQL Server or some other massive client-server database, then using DAO
instead of ADO will be more efficient and more powerful. The code isn't
much different for these particular operations:
'*** DAO Code ***
CurrentDb.Execute _
"INSERT INTO MyTable (ID, Description) " & _
"VALUES(123, 'blah, blah')",
dbFailOnError
CurrentDb.Execute _
"DELETE * FROM MyTable WHERE ID=123",
dbFailOnError
Note that in Access 2000 and 2002 -- but not 2003 nor 97 -- you have to
add a reference to the DAO 3.6 Object Library to your project before you
can declare DAO objects or use DAO constants like dbFailOnError in your
code.
If you anticipate upsizing your applications to use a client-server
back-end database, then go ahead with ADO; otherwise, I'd stick with
DAO. Or learn both, and choose the right tool for the job.
