Getting Automation error

[Michelle]

Hi,
Thank you for your help in advance.
Can you tell me where I'm going wrong with my code?
Running Excel 2007
I'm getting an -2147217904 automation error on the recordset.Open line.

strSelectedCommodity = Range("EquipmentCommodity").Value


'strSQLCommodityDesc = "SELECT [Code of Accounts].COA, [Code of
Accounts].Description FROM [Code of Accounts] WHERE [Code of Accounts].COA= "
& strSelectedCommodity & " ;"
Set recordset = New ADODB.recordset
recordset.CursorType = adOpenStatic
recordset.LockType = adLockReadOnly
'recordset.Options = adCmdText
recordset.Open strSQLCommodityDesc, UsageTracking

 

[Michelle]

Sorry. The strSQLCommodityDesc line was commented out in the original post.
Please consider it an active line.

Thanks

 

[steve]

Michelle, Did you ever get this working. I am having the same problem.

Sorry. The strSQLCommodityDesc line was commented out in the original post.
Please consider it an active line.

Thanks

Hi,
Thank you for your help in advance.
Can you tell me where I'm going wrong with my code?
Running Excel 2007
I'm getting an -2147217904 automation error on the recordset.Open line.

strSelectedCommodity = Range("EquipmentCommodity").Value


strSQLCommodityDesc = "SELECT [Code of Accounts].COA, [Code of
Accounts].Description FROM [Code of Accounts] WHERE [Code of Accounts].COA= "
& strSelectedCommodity & " ;"
Set recordset = New ADODB.recordset
recordset.CursorType = adOpenStatic
recordset.LockType = adLockReadOnly
'recordset.Options = adCmdText
recordset.Open strSQLCommodityDesc, UsageTracking

 

[Michelle]

Hi Steve,

Thanks for getting back to me. No, I never did figure out what was wrong
with it. I even stripped it down to nothing! Select * from [Code of Accounts]
and STILL couldn't get it to work! Do you have any idea what the syntax would
be if I add a variable in an SQL statement?

Ex: Set adoRecordset = New ADODB.recordset
adoRecordset.Open _
Source:="Select [COAID], [Description] From [Code of Accounts] Where
[COAID] = " & SelectedContractNumber & ";"
ActiveConnection:=CodeofAccounts, _
CursorType:=adOpenStatic, _
LockType:=adLockReadOnly, _
Options:=adCmdText
adoRecordset.MoveFirst

 

[steve]

Where are you pulling the data from...SQL Server, Access?

Hi Steve,

Thanks for getting back to me. No, I never did figure out what was wrong
with it. I even stripped it down to nothing! Select * from [Code of Accounts]
and STILL couldn't get it to work! Do you have any idea what the syntax would
be if I add a variable in an SQL statement?

Ex: Set adoRecordset = New ADODB.recordset
adoRecordset.Open _
Source:="Select [COAID], [Description] From [Code of Accounts] Where
[COAID] = " & SelectedContractNumber & ";"
ActiveConnection:=CodeofAccounts, _
CursorType:=adOpenStatic, _
LockType:=adLockReadOnly, _
Options:=adCmdText
adoRecordset.MoveFirst

Hi,
Thank you for your help in advance.
Can you tell me where I'm going wrong with my code?
Running Excel 2007
I'm getting an -2147217904 automation error on the recordset.Open line.

strSelectedCommodity = Range("EquipmentCommodity").Value


'strSQLCommodityDesc = "SELECT [Code of Accounts].COA, [Code of
Accounts].Description FROM [Code of Accounts] WHERE [Code of Accounts].COA= "
& strSelectedCommodity & " ;"
Set recordset = New ADODB.recordset
recordset.CursorType = adOpenStatic
recordset.LockType = adLockReadOnly
'recordset.Options = adCmdText
recordset.Open strSQLCommodityDesc, UsageTracking