Insert Record to Access via ADO

[BMarkham]

I am attempting to open an empty recordset from an Access 97 table. Then use the AddNew method of the recordset to add a new record. I'm getting a message saying the OLE DB provider doesn't support this operation or I've chosen the wrong CursorLock.

Code sample:

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51; " & _
"Data Source=" & DBPath

cn.Open

rs.CursorLocation = adUseServer
rs.LockType = adLockOptimistic


strSQL = "SELECT * FROM [tblWorkRequests] WHERE 1=2"
Set rs = cn.Execute(strSQL)

rs.AddNew 'This is where it crashes


Does the Access OLE DB not allow you to do this or am I simply doing it incorrectly? The reason I was trying to add the record this way instead of just running a SQL INSERT statement is because there is an autonumber field for the record ID and I want to grab the ID number after it has been generated. If there's another way to do that please let me know.

Thanks,
Brett

 

[Ed2020]

Brett,

I'm not sure what is wrong with this code but when accessing Jet data tables in an MS Access database you are much better off using DAO rather than ADO - ADO is slower when accessing Jet data sources.

Ed Metcalfe.

 

[dvannoy]

try

rs.addnew

rs.Fields("fieldname&quot = txtTextBox.Text

rs.update

you need to specify what you want to add..

 

[BMarkham]

Sorry. The way I wrote it was confusing. There is more code after the AddNew method is called, but it never gets a chance to run. An error occurs telling me the AddNew method can not be used on that recordset.

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51; " & _
"Data Source=" & DBPath

cn.Open

rs.CursorLocation = adUseServer
rs.LockType = adLockOptimistic


strSQL = "SELECT * FROM [tblWorkRequests] WHERE 1=2"
Set rs = cn.Execute(strSQL)

rs.AddNew 'This is where it crashes

rs.Fields("FirstName&quot = Joe
rs.Fields("LastName&quot = Shmoe

rs.Update


I'm guessing that ADO recordsets from Access tables are not updatable. I was hoping someone would confirm this or tell me I'm a fool and show me what I'm doing wrong.

Thanks,
Brett

 

[petermeachem]

Instead of

Set rs = cn.Execute(csql)

Use

rs.Open csql, cnn, adOpenStatic, adLockOptimistic
Peter Meachem
peter@accuflight.com

 

[dvannoy]

You have to open the recordset before you can add to it..try this before your rs.addnew...Just replace the dbName in the app.path to your dbname. and this should work.


Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String

With cn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=" & App.Path & "\dbName.mdb;Persist Security Info=False"
.Open
End With

strSQL = "SELECT * FROM [tblWorkRequests] WHERE 1=2"

Set rs = New ADODB.Recordset
rs.CursorType = adOpenDynamic
rs.CursorLocation = adUseClient
rs.LockType = adLockOptimistic
rs.Open strSQL , cn

rs.AddNew

rs.Fields("FirstName&quot = Joe
rs.Fields("LastName&quot = Shmoe

rs.Update


Hope this helps..