VBA To Modify Existing ODBC Data Source - Excel

[sthrncaliguy]

Alright I have an existing data connection to an ODBC data source. I am curious how I can modify one of the query parameters programatically.

So for instance, my data connection properties which already has a connection setup includes this statement.statement:

SELECT "DataTable"."User Name"
FROM "DataTable" "DataTable"
WHERE ("DataTable"."Office Location"= 'Dallas TX')

And I can refresh this query via VBA with Activeworkbook.RefreshAll or something similiar.

My question is, is there any way to programmatically change the queries parameter on the fly?

So I would like to define a variable called "Location" and then insert that into the Office Location part of the query...



I think if someone could help me get my data connection completely into VBA, instead of on the Data Connections tab, I could probably figure it out myself.

Thanks!

 

[Gary McMaster]

You can start the macro recorder and then edit your existing query. I believe the code that the macro recorder generates will contain the connection and record selection code.

Gary

 

[millhouse123]

You can also just substitue a variable for whatever field you are pulling.
So create a variable call location and set it = to a field in your worksheet or create a message box that you can enter it into when you run the macro - replace the section of code that currently says Dallax, TX with your variable name be sure t put a double quote after the single quote then the & symbol then your variable name and another &. then a double quote before the last single quote.

somthing like '" & VariableName & "'