Insert to Access table

[Matt]

I am trying to run the following but I am getting the following error
"object variable or With Block Variable not set", any ideas why??


Sub Insert()

Dim objCommand As ADODB.Command
Dim rsData As ADODB.Recordset
Dim lRecordsAffected As Long
Dim lKey As Long
Dim sConnect As String

' On Error GoTo ErrorHandler

'connection string
sConnect = "Provide=Microsoft.ACE.OLEDB.12.0; Data Source=U:
\intranet\pmdata.mdb; Mode=Share Exclusive"

'command object for statements
Set ojbCommand = New ADODB.Command
objCommand.ActiveConnection = sConnect

'insert statement
objCommand.CommandText = "INSERT INTO table1(Test1, Test2)
VALUES('test456', 'test4567');"

'execute statement
objCommand.Execute 'RecordsAffected:=lRecordsAffected,
Options:=adCmdTxt Or adExecuteNoRecords

If lRecordsAffected <> 1 Then Err.Raise Number:=vbObjectError +
1024, Description:="Error executing Insert Statement."

ErrorExit:

'Destroy ADO objects
Set objCommand = Nothing
Set rsData = Nothing

Exit Sub

ErrorHandler:

MsgBox Err.Description, vbCritical
Resume ErrorExit

End Sub

 

[Guest]

I am trying to run the following but I am getting the following error
"object variable or With Block Variable not set", any ideas why??


Sub Insert()

Dim objCommand As ADODB.Command
Dim rsData As ADODB.Recordset
Dim lRecordsAffected As Long
Dim lKey As Long
Dim sConnect As String

' On Error GoTo ErrorHandler

'connection string
sConnect = "Provide=Microsoft.ACE.OLEDB.12.0; Data Source=U:
\intranet\pmdata.mdb; Mode=Share Exclusive"

'command object for statements
Set ojbCommand = New ADODB.Command
objCommand.ActiveConnection = sConnect

'insert statement
objCommand.CommandText = "INSERT INTO table1(Test1, Test2)
VALUES('test456', 'test4567');"

'execute statement
objCommand.Execute 'RecordsAffected:=lRecordsAffected,
Options:=adCmdTxt Or adExecuteNoRecords

If lRecordsAffected <> 1 Then Err.Raise Number:=vbObjectError +
1024, Description:="Error executing Insert Statement."

ErrorExit:

'Destroy ADO objects
Set objCommand = Nothing
Set rsData = Nothing

Exit Sub

ErrorHandler:

MsgBox Err.Description, vbCritical
Resume ErrorExit

End Sub

You misspelled objCommand here:

Set ojbCommand = New ADODB.Command

Use adCmdText instead of adCmdTxt.

Also, try using ADODB.Connection object:

Dim objCommand As ADODB.Command
Dim objConnection As ADODB.Connection

---

'connection string
sConnect = "Provide=Microsoft.ACE.OLEDB.12.0; Data
Source=U:\intranet\pmdata.mdb; Mode=Share Exclusive"

Set objConnection = New ADODB.Connection
objConnection.Open sConnect

'command object for statements
Set objCommand = New ADODB.Command
Set objCommand.ActiveConnection = objConnection


Regards

 

[Matt]

You misspelled objCommand here:

Set ojbCommand = New ADODB.Command

Use adCmdText instead of adCmdTxt.

Also, try using ADODB.Connection object:

Dim objCommand As ADODB.Command
Dim objConnection As ADODB.Connection

---

'connection string
sConnect = "Provide=Microsoft.ACE.OLEDB.12.0; Data
Source=U:\intranet\pmdata.mdb; Mode=Share Exclusive"

Set objConnection = New ADODB.Connection
objConnection.Open sConnect

'command object for statements
Set objCommand = New ADODB.Command
Set objCommand.ActiveConnection = objConnection

Regards

Thanks for the reply. I corrected the misspleeling and made the
suggested changes. Now I am getting an 'Automation Error'.

 

[Matt]

Thanks for the reply. I corrected the misspleeling and made the
suggested changes. Now I am getting an 'Automation Error'.- Hide quoted text -

- Show quoted text -

with the error handling turned on I am getting
"Multi-Step OLE DB operation generated errors"

 

[Guest]

with the error handling turned on I am getting
"Multi-Step OLE DB operation generated errors"

Looks like you also misspelled "provider":

Dim objConnection As ADODB.Connection
Dim objCommand As ADODB.Command
Dim rsData As ADODB.Recordset
Dim lRecordsAffected As Long
Dim lKey As Long
Dim sConnect As String

' On Error GoTo ErrorHandler

'connection string
sConnect = "Provider=Microsoft.ACE.OLEDB.12.0; Data
Source=U:\intranet\pmdata.mdb; Mode=Share Exclusive"

Set objConnection = New ADODB.Connection
objConnection.Open sConnect

'command object for statements
Set objCommand = New ADODB.Command
objCommand.ActiveConnection = objConnection

'insert statement
objCommand.CommandText = "INSERT INTO table1(Test1, Test2) VALUES('test456',
'test4567');"

'execute statement
objCommand.Execute 'RecordsAffected:=lRecordsAffected,Options:=adCmdTxt Or
adExecuteNoRecords