Getting Automation error

M

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
 
M

Michelle

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

Thanks
 
S

steve

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

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

Thanks

Michelle said:
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
 
M

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
 
S

steve

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

Michelle said:
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

Michelle said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top