The request for procedure "x" failed because "x" is a table object

[radubalmus]

Hello all
I get the above error tring to create a new entry in an sql database. I am using an excel frontend with ADO connection. This happens since we have upgraded excel from 2003 to 2010
The error happens on MyRecordset.Open

this is the code:

Dim MyConnection As ADODB.Connection
Dim MyRecordset As ADODB.Recordset
Dim sqlConnString As String

sqlConnString = "Driver={SQL Server};Server=xx.xx.x.xx;Database=erotestdb;Uid=xxx;Pwd=xxx;APP=Microsoft Office 2003"Set MyConnection = CreateObject("ADODB.Connection")
Set MyRecordset = CreateObject("ADODB.Recordset")

With MyConnection
.ConnectionString = sqlConnString
'On Error Resume Next
If Err.Number <> 0 Then
MsgBox "SQLServer Connection could not be established."
Err.Clear
'On Error GoTo 0
'blnOffline = True
'GoTo ClearMem
End If
.Open
End With
MyRecordset.Open table, MyConnection, adOpenKeyset, adLockOptimistic
Set getRS = MyRecordset

Any ideea?
Thanks

There are simple solutions for almost every problem! The hard part is to see them!!!!

 

[PHV]

I'd use something like this:
MyRecordset.Open [!]"NameOfTable"[/!], MyConnection, adOpenKeyset, adLockOptimistic
or this:
MyRecordset.Open [!]"SELECT * FROM NameOfTable"[/!], MyConnection, adOpenKeyset, adLockOptimistic

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886