Simple ADODB recordset append doesn't work, too novice to know why

[J]

Hello All:

I can't get the following code to work, and it doesn't generate an
error why either. I can run the append sql from inside the mdb
successfully, but I want to append from OUTSIDE the mdb. The table
being appended to is NOT the backend, it's the old version of the
database I'm working on (I want it to auto update the old one in case
we have to go back to it).

What are some things I could be doing wrong?
~J
PS. WinXP SP2, Access2k3 SP2, ADO 2.8, VBA 6.04, Jet 4.0,

Set objRecordSet = CreateObject("ADODB.Recordset")
objRecordSet.Open "SELECT * FROM [this other table]", _
"Provider=Microsoft.Jet.OLEDB.4.0; Data Source='C:\DB.mdb'",
adOpenDynamic, adLockOptimistic

Dim strSQL As String
strSQL = "INSERT INTO [this other table] ([Field1], [Field2],
[Field3]) VALUES (1, 2, 3)"
'DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

objRecordSet.Close
Set objRecordSet = Nothing

[Ken Snell (MVP)]

You open a recordset to the table, and then don't use it for updating the
table? Add a new record through the recordset, not through the SQL
statement:

Set objRecordSet = CreateObject("ADODB.Recordset")
objRecordSet.Open "SELECT * FROM [this other table]", _
"Provider=Microsoft.Jet.OLEDB.4.0; Data Source='C:\DB.mdb'",
adOpenDynamic, adLockOptimistic

With objRecordSet
.AddNew
!Field1 = 1
!Field2 = 2
!Field3 = 3
.Update
End With

objRecordSet.Close
Set objRecordSet = Nothing

--

Ken Snell
<MS ACCESS MVP>

"J" <DamnYo...@gmail.com> wrote in message
news:1162755739.4...@e3g2000cwe.googlegroups.com...

[J]

Thanks, Ken. I really need to invest in a teach yourself book!

~J

[Ken Snell (MVP)]

< g > You're welcome.

--

Ken Snell
<MS ACCESS MVP>

"J" <DamnYo...@gmail.com> wrote in message

news:1162757504.9...@h48g2000cwc.googlegroups.com...