Data Connections: Parameterized Query of Access DB... Is it possible?

It is not that straight forward..... excel doe not naturally accept parameterized queries. Using VBA coding is the most straight forward way of passing parameters but you will have to manage the connections to the database from code. However, it appears that you want a non-code way of doing it. This is not so straight foward.

Basically, when connection to your database using the Data Tab choose "From Other Sources." Then choose From Microsoft Query. You will have to create a Parameterize Query from there to your data source.... Then in excel go to Data choose connections then choose properties then click on the Definition Tab. If you have set up a proper Parameter Query the Parameters button on the bottom will be highlighted choose that and then you can reference the parameters to Excel Cells .....

Here is a reference link and solutions with same issue as you:
http://www.pcreview.co.uk/forums/exc...-t3519096.html

Thanks tk, I have dived into the VBA and it doesn't seem too daunting.

I created a dropdown and populated it using the Workbook_Open() function.

Then I used the ComboBox1_Change() function to fire off the code to change the SQL call. So here is where I am: I want to know if I can streamline my code any further. I generally like to code in the most elegant and minimal way possible. What are the minimum requirements? I used the "Record Macro" function to give me a baseline, but I feel there is a lot of extraneous code. Let me know what you think I can cut out:

 Dim SQL_Code As String
    SQL_Code = "SELECT * from table"
    With ActiveWorkbook.Connections("The Original Existing Connection to the DB").OLEDBConnection
        .BackgroundQuery = False
        .CommandText = SQL_Code
        .CommandType = xlCmdSql
        .Connection = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=G:\My Database.accdb;Mode=ReadWrite;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False"
        .RefreshOnFileOpen = False
        .SavePassword = False
        .SourceConnectionFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With
    With ActiveWorkbook.Connections("The Original Existing Connection to the DB")
        .Name = "The Original Existing Connection to the DB"
        .Description = ""
    End With
    ActiveWorkbook.Connections("The Original Existing Connection to the DB").Refresh
    
    
    Dim Sign_SQL As String
    Sign_SQL = "select * from table2"
    With ActiveWorkbook.Connections("A second connection to a different DB").OLEDBConnection
        .BackgroundQuery = False
        .CommandText = Sign_SQL
        .CommandType = xlCmdSql
        .Connection = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=G:\Another DB.accdb;Mode=ReadWrite;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False"
        .RefreshOnFileOpen = False
        .SavePassword = False
        .SourceConnectionFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With
    With ActiveWorkbook.Connections("A second connection to a different DB")
        .Name = "A second connection to a different DB"
        .Description = ""
    End With
    ActiveWorkbook.Connections("A second connection to a different DB").Refresh

It just seems like lots of that stuff is unnecessary. I would assume I just need the new SQL query, the DB connection and a refresh command.

Can I do this without having an initial DB connection? Thanks!

There are lots of ways of connecting to databases through vba each has their own requirements... say for example you want to connect through ADODB.... Make sure the VBA IDE has the Microsoft ActiveX Data Objects 2.x Library checked under references under the Tools menu....

Below is a slim'd down version of the connection and queries assuming you need two queries open at the same time.... The connection is made when you need it and closed when your finished withit

'Make sure the Tools/References  Microsoft ActiveX Data Objects 2.X is checked
'Or Modify code below if you are using something other than an ADO connection
Dim sQL1 As String, sQL2 As String
Dim cn As ADODB.Connection
Dim rs1 As ADODB.Recordset, rs2 As ADODB.Recordset

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=.\Test.accdb;Persist Security Info=False"

sQL1 = "Select * from Table1;"
sQL2 = "Select * from Table2;"

Set rs1 = New ADODB.Recordset
Set rs2 = New ADODB.Recordset

rs1.Open sQL1, cn
rs2.Open sQL2, cn

Range("A10").CopyFromRecordset rs1
Range("D10").CopyFromRecordset rs2

rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
cn.Close
Set cn = Nothing

above code was tested! Note: You will be unable to use the built in Data Refresh commands in Excel. You will have to manage the refreshing of data your self through code...