ExcelBanter - Insert to Access table

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Insert to Access table (https://www.excelbanter.com/excel-programming/391330-insert-access-table.html)

Insert to Access table

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

Insert to Access table

"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

Insert to Access table

On Jun 14, 12:40 pm, urkec wrote:
"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- Hide quoted text -

- Show quoted text -

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

Insert to Access table

On Jun 14, 1:19 pm, Matt wrote:
On Jun 14, 12:40 pm, urkec wrote:

"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- Hide quoted text -

- Show quoted text -

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"

Insert to Access table

"Matt" wrote:

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:=adCmdT xt Or
adExecuteNoRecords

--
urkec