Re: Inserting Records Into Access Table via DAO

From: margaret bartley (may04_NOT_THIS__at_mindspring.com)
Date: 02/24/05 

Date: Thu, 24 Feb 2005 15:11:34 -0800

"Ross Culver" <rculver@alliant-solutions.com> wrote in message
news:%23uBCm$qGFHA.4088@TK2MSFTNGP09.phx.gbl...
> 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 spread***. 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.