samvnw51
04-14-2008, 12:08 PM
In Excel 2007 I am trying to update an external database (Quickbooks) record using a SQL UPDATE statement by using a WorkBookConnection object:
Dim junk As WorkbookConnection
junk = ActiveWorkbook.Connections.Add("QODBC_ID_Update", "Used for incrementing Quote and Order IDs", _
"DSN=QuickBooks Data;SERVER=QODBC;OptimizerDBFolder=%UserProfile%\QODBC Driver for QuickBooks\Optimizer;OptimizerAllowDirtyReads=N;SyncFromOtherTables=N", _
"SELECT OtherName.ListID, OtherName.Name, OtherName.AccountNumber FROM OtherName OtherName")
When I try and execute this I get Runtime error '5'. Invalid Procedure call or argument.
In this example I am using a valid select statement which I will change to the UPDATE statement if I can get I to work. If the .Add method works. then I'll us the .Refresh method to execute the SQL statement.
Has anyone use the Connections.Add method successfully, and could you post an example of how to use it.
Thanks.
Dim junk As WorkbookConnection
junk = ActiveWorkbook.Connections.Add("QODBC_ID_Update", "Used for incrementing Quote and Order IDs", _
"DSN=QuickBooks Data;SERVER=QODBC;OptimizerDBFolder=%UserProfile%\QODBC Driver for QuickBooks\Optimizer;OptimizerAllowDirtyReads=N;SyncFromOtherTables=N", _
"SELECT OtherName.ListID, OtherName.Name, OtherName.AccountNumber FROM OtherName OtherName")
When I try and execute this I get Runtime error '5'. Invalid Procedure call or argument.
In this example I am using a valid select statement which I will change to the UPDATE statement if I can get I to work. If the .Add method works. then I'll us the .Refresh method to execute the SQL statement.
Has anyone use the Connections.Add method successfully, and could you post an example of how to use it.
Thanks.