Inserting Records Into Access Table via DAO

[Ross Culver]

I'm used to using the command object in ADO to insert records into an SQL
table; but I'm having trouble finding the same object in DAO. I want to
insert a new record into an Access (mdb) database using variables collected
in an Excel spreadsheet. Is there an equivalent of adodb.command in DAO? I
tried using a DAO recordset (like you can do with ADO) but that didn't work.

Thanks in advance.

Ross

 

[Brendan Reynolds]

Using a recordset does work, although there are differences in the syntax
used. Or you could use the Execute method of the DAO.Database object to
execute a SQL statement. Probably the closest equivalent to an ADO Command,
though, would be a QueryDef with parameters. Here's an example ...

Public Sub UpdatewithQueryDef()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdf = db.CreateQueryDef("", "PARAMETERS [TheNumber] Long, " & _
"[TheText] Text ( 255 ); INSERT INTO tblTest ( TestNumber, " & _
"TestText ) VALUES ([TheNumber], [TheText]);")
qdf.Parameters("[TheNumber]") = 42
qdf.Parameters("[TheText]") = "jabberwocky"
qdf.Execute dbFailOnError

End Sub

 

[margaret bartley]

I'm used to using the command object in ADO to insert records into an SQL
table; but I'm having trouble finding the same object in DAO. I want to
insert a new record into an Access (mdb) database using variables collected
in an Excel spreadsheet. Is there an equivalent of adodb.command in DAO? I
tried using a DAO recordset (like you can do with ADO) but that didn't

work.

Another way is to use the RunSQL command object.


Sub AddARecord
'This procedure adds a record to a table called "Table1", which has three
fields, [FI],[FDate] and [FText].
'The record added will have the values 101, 12/5/03, and "Car",
respectively.

Dim strSQL as string
Dim iIntVal as integer
Dim DVal as date
Dim strTextVal as string

iIntVal=101
dVal=#12/5/03#
strTextVal="Car"

strSQL="Insert into [Table1] (F1,FDate,FText) SELECT " & iIntVal & ", #"
& DVal & "#, '" & strTextVal & "'"
doCmd.SetWarnings False
doCmd. runSQL strSQL
doCmd.SetWarnings True

End Sub

This is a tedious way to add many many records. A query would be faster.
But if you are only adding a few records, this is, in my mind, easier to
conceptualize.

The downside is that the query will not be optimized. Again, if you are
using very large amounts of data, you would want to create a query that
could be compiled, for quicker processing time, and just execute that query.

On the other hand, this has a value, to me, of making it easier to document
and keep track of what I'm doing.

I find applications with hundreds of queries to be a nightmare, and I prefer
my queries to be in the code. Easier to debug, and keep track of what's
going on.

 

[Ross Culver]

Thanks guys! I found it easier to use the queryDef approach.

Thanks a million!

Using a recordset does work, although there are differences in the syntax
used. Or you could use the Execute method of the DAO.Database object to
execute a SQL statement. Probably the closest equivalent to an ADO
Command, though, would be a QueryDef with parameters. Here's an example
...

Public Sub UpdatewithQueryDef()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdf = db.CreateQueryDef("", "PARAMETERS [TheNumber] Long, " & _
"[TheText] Text ( 255 ); INSERT INTO tblTest ( TestNumber, " & _
"TestText ) VALUES ([TheNumber], [TheText]);")
qdf.Parameters("[TheNumber]") = 42
qdf.Parameters("[TheText]") = "jabberwocky"
qdf.Execute dbFailOnError

End Sub

--
Brendan Reynolds (MVP)

I'm used to using the command object in ADO to insert records into an SQL
table; but I'm having trouble finding the same object in DAO. I want to
insert a new record into an Access (mdb) database using variables
collected in an Excel spreadsheet. Is there an equivalent of
adodb.command in DAO? I tried using a DAO recordset (like you can do
with ADO) but that didn't work.

Thanks in advance.

Ross