help inserting records into an access table

[slescure]

Hi - I'm new and have a simple question. from within a VBA procedure
w/in an Access db, i want to read another database (not access) and
dump records into an ACCESS table. I can successfully read the other
database:

Dim con As New ADODB.Connection
Dim rec As New ADODB.Recordset
Dim strConnection As String
Dim strSQLQuery As String
Dim objField As ADODB.Field
Dim lOffset As Long
Dim temp As String

strConnection = "DSN= MyFiles;PROMPT=2"
strSQLQuery = "SELECT field names," & _
etc...
"FROM file name WHERE etc..

con.Open strConnection
rec.Open strSQLQuery, con

BUT - I have no idea how to get them into Access. Do I open anther
connection to the Accces file and do an INSERT? is there a better way?
Performance is a concern....

if anyone can provide some examples/guidance i would be most grateful.

thanks

 

[Ron Weiner]

Here is one way!!!

... Your code that opens the Source
rec.Open strSQLQuery, con

strSql = "SELECT * " _
& "FROM TableInTheCuurrentDB " _
& "WHERE yyy='zzz' " _
& "ORDER BY 1,2,3"
Set rs = New ADODB.Recordset
rs.Open strSql, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
If Not rec.BOF Or Not rec.EOF Then
rec.MoveFirst
Do While Not rec.EOF
rs.AddNew
rs!someField1 = rec!someField1
rs!someField2 = rec!someField2
rs!someField3 = rec!someField3
rs!someField4 = rec!someField4
rs.Update
rec.MoveNext
Loop
End If
rs.Close
Set rs = Nothing
rec.close
set Rec = Nothing
con.Close
set Con = Nothing

You could also build a string for an INSERT INTO Sql statement and execute
it, depends on what makes sense.

 

[slescure]

Thanks! I'll give that a try.

any idea where i can find a reference manual that provides the syntax
for doing, for example, the INSERT INTO statement....i don't see it in
the VBA help.

thanks again

steve

 

[Douglas J. Steele]

Multiple-record append query:

INSERT INTO target [IN externaldatabase] [(field1[, field2[, ...]])]
SELECT [source.]field1[, field2[, ...]
FROM tableexpression

Single-record append query:

INSERT INTO target [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...])

See
http://office.microsoft.com/assista...in=EC010227091033&QueryID=BXx7Nqe0y0&respos=1

 

[slescure]

THANKS!