"Matt" wrote:

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 he

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

--
urkec