Getting Automation error
 

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

Getting Automation error
 

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

Thanks

"Michelle" wrote:

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

Getting Automation error
 

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

"Michelle" wrote:

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

Thanks

"Michelle" wrote:

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

Getting Automation error
 

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" wrote:

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

Getting Automation error
 

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

"Michelle" wrote:

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" wrote:

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