Programatically append records from one table to another table

B

Beth

Hello,
Can anyone help me with the code to perform the following. On my main form,
I want user to be able to click one button that will then copy all the
records from table A to table B. I know I can do it with an append query,
but I would like to be able to do it in code instead.
Thanks in advance for the help.
Beth
 
G

G. Vaught

Create a Command button on your form. Under the "OnClick" Event tab write
the following code.

DoCmd.OpenQuery "qryName" ' This query will get the values you want from
Table A.
DoCmd.OpenQuery "qryAppendRecords" This query will append the results of
qryName to table B. Thus when you create this query the query is used as the
source.
 
B

Beth

I was really trying to avoid using queries. I am hoping to move these
actions to VBA code. If I can't figure out how to do it in code, I'll go
with this.
Thanks,
Beth
 
J

John Nurick

You can do something like this, using a SQL statement to append the
records, without involving a QueryDef (an Access query):

Dim strSQL As String

strSQL = "INSERT INTO TableB SELECT * FROM TableA;"
dbEngine(0)(0).Execute strSQL, dbFailOnError

That said, in a well-designed database routine operations very seldom
involve copying records from table to table (unless of course you're
importing data). If you have multiple tables with the same structure,
you are effectively storing data in the table names: instead, use a
single table with an additional field to distinguish between records
from the different sources.

But except when you're importing or restructuring data, it's
 
Top