none
Excel vba connections RRS feed

  • Question

  • Hi All,

    I have a excel vba file which has two connection to the same sql server database as below.

    A. A is oledbconnection(created using MS query) created using Data Sources -> From MS Query and executes a Stored procedure.
    B. B is ADODB Connection which uses connection string through sql server native client and fetches information from the table to load into combobox.

    Can i use the connection from A for connection in B instead of creating a new connection again.  so that i can have only one
    connection to the Database.  

    Thanks in Advance,
    Arun.

    Thursday, December 11, 2014 8:41 AM

All replies

  • Am not sure where this question to be posted.  I have posted the same question in VBA forum.
    Thursday, December 11, 2014 9:43 AM
  • Can i use the connection from A for connection in B instead of creating a new connection again.  so that i can have only one connection to the Database.  


    Not in that direction, because you have no control whether and when the query closes the connection.

    But a macro can open a ADODB Connection, make several SQL calls and close the connection at the appropriate point.

    Andreas.

    Friday, December 12, 2014 8:12 AM
  • It's an interesting issue. It appears Excel marries querytables with their connection thus making it near impossible to "move" the destination results of the query from one sheet to another. I've never attempted to change the destination of a connection. I believe a new connection must be established if the target is to be changed.

    Also note that ListObjects have an optional Querytable property, but I believe it is read-only.

    Friday, December 12, 2014 4:24 PM
  • Thanks Andreas and syswizard.

    Excel has existing connection to the database for executing stored procedures  and i have VBA which uses ADODB connection for other purpose.    In VBA, can i used the same existing connection for other purpose instead of creating a new connection again.


    Friday, December 19, 2014 1:26 PM
  • Of course you can. However, you will have to create the query table dynamically via VBA as well.
    Monday, December 22, 2014 6:33 PM