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

  • Thread starter Thread starter J
  • Start date Start date
J

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
 
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
 
Thanks, Ken. I really need to invest in a teach yourself book!

~J

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 said:
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
Click to expand...
Click to expand...
 
< g > You're welcome.

--

Ken Snell
<MS ACCESS MVP>

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

~J

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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
You will find dozens of articles with the novice in mind. Follow links
available at:
http://hodentek.blogspot.com and look at 'All you need to know about Access'

ADO has been beaten to death.
--
mysorian


Ken Snell (MVP) said:
< g > You're welcome.

--

Ken Snell
<MS ACCESS MVP>

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

~J

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>



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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top