VBA To Modify Existing ODBC Data Source - Excel

sthrncaliguy

Board Regular
Joined
Jul 28, 2009
Messages
213
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!
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Gary McMaster

Well-known Member
Joined
Feb 8, 2009
Messages
1,977
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

Active Member
Joined
Aug 22, 2006
Messages
334
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 & "'
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,208
Messages
5,857,920
Members
431,908
Latest member
Marjie174

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top