Multiple record append SQL syntax ?

M

mscertified

I have two tables with identical structure.
I need to copy all records with a certain date from table to table
I use this SQL but it gets syntax error:

rs.Open "INSERT INTO tblAddress SELECT * FROM tblAddressOnline" & _
" WHERE RecordDate < Date", _
CurrentProject.Connection, adOpenDynamic, adLockOptimistic
rs.Close

Hopefully, there is a way to do this without having to name every column?
 
M

Maurice

Try this:

docmd.runSQL "INSERT INTO tblAddress SELECT * FROM tblAddressOnline" & _
" WHERE RecordDate < Date", _
CurrentProject.Connection, adOpenDynamic, adLockOptimistic

remember that if the date fields are actual datefields you have to place
poundsigns around them (#)

hth
 
M

Michel Walsh

.... you mean date CONSTANT, you need # around immediate date constant. You
don't need to place # around containers of a date value, you don't need to
place # around FIELD name or FUNCTION retuning a date value. And if you use
#, it is HIGHLY preferable to use MM-DD-YYYY format.


" WHERE RecordDate < DateField"

" WHERE RecordDate < Now() "

" WHERE RecordDate < #12-31-2006# "



and also:

? format(#31-12-2007#, "long date")
Monday, December 31, 2007

? format (#12-31-2007#, "long date")
Monday, December 31, 2007

? format(#2-10-2007#, "long date")
Saturday, February 10, 2007



While the first date is NOT in the USA format, the context is explicit: 31
cannot be a month, so, OLE automation assumes it is in the MM-DD-YYYY
format. But for the last case, 2 and 10 can both be month or date,... so the
two date formats: mm-dd-yyyy and dd-mm-yyyy are possible, but the one
selected is the USA one.... independently of the regional setting! (one
exception, in Access 2003, is the grid editor for a query, the # - - # seems
to follow, sometimes, the regional setting, not the USA format, just to add
some confusion, I guess).




Vanderghast, Access MVP
 
C

Chris2

mscertified said:
I have two tables with identical structure.
I need to copy all records with a certain date from table to table
I use this SQL but it gets syntax error:

rs.Open "INSERT INTO tblAddress SELECT * FROM tblAddressOnline" & _
" WHERE RecordDate < Date", _
CurrentProject.Connection, adOpenDynamic, adLockOptimistic
rs.Close

Hopefully, there is a way to do this without having to name every
Click to expand...
column?

When using recordsets, you "open" a normal select query (it can't be a
snapshot or similar can't-be-edited recordset type).

Set your recordset record, rs.first, rs.next, etc.

Begin a loop.

Then it's rs.edit

Then you use VBA code to update the recordset fields for the current
record.

Then it's rs.update

Then it's back to the beginning of the loop.

DoCmd is MS Access specific, and while convenient, is less portable.


Sincerely,

Chris O.
 
M

Maurice

Ok, point taken. I just added the comment because in SQL especially when
creating querydefs dates are presented the wrong way (as you stated in your
examples and additional notice). I use the 'dutch' settings and this can
present you with some surprises from time to time...
 
Top