PDA

View Full Version : How to use the Connections.Add method



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.

paulirwin
04-15-2008, 12:07 PM
i have been banging my head over this too... and the problem will make you angry.

If it's an ODBC connection, you need to add "ODBC;" to the beginning of the connection string. If it's OLEDB, you need to add "OLEDB;" to the beginning.

So for a simple ODBC DSN, it would look like:

"ODBC;Dsn=MyDsn"

hope this helps. i registered on this forum just to post this answer since i was going crazy too.