Making same change to multiple++ Data Connections. Can VBA help?

[Rachel Garrett]

Greetings,

I have to update a collection of a couple dozen Excel scorecards that pull their data from Access. Each scorecard has three Data Connections to the database; the database contains three different queries constructed to supply their piece of the data.

One thing I discovered while playing around is that when I set up a data connection by telling Excel to look at an Access query, it just copies in the SQL -- it doesn't actually point to the query. So instead of just being able to tweak three queries in the database in order to make a change, I will have to manhandle about 90 data connections.

The operation I have to do on each connection is identical:

1) Stop returning fields A, B, and C when the connection refreshes.
2) Add fields X, Y, and Z in their place.

Does anyone have suggestions on a good approach? I was thinking maybe VBA could edit the connection strings for me. (I don't consider VBA part of my skillset, so if you used advanced concepts, example code would be extremely helpful.)

Thank you very much for the help.

--Rachel

 

[Rachel Garrett]

I may have found a solution. I just found a lengthy tutorial from Microsoft on how to do this, using OpenXML.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Not sure what that is or how to use it – yet.

 

[xenou]

I have to update a collection of a couple dozen Excel scorecards that pull their data from Access. Each scorecard has three Data Connections to the database; the database contains three different queries constructed to supply their piece of the data.

One thing I discovered while playing around is that when I set up a data connection by telling Excel to look at an Access query, it just copies in the SQL -- it doesn't actually point to the query. So instead of just being able to tweak three queries in the database in order to make a change, I will have to manhandle about 90 data connections.

Can you post a few sample data connections? Leave out passwords if they are found in the strings please - and anything else potentially sensitive.

The link you found requires Microsoft Visual Studio for creating custom forms (if you are going to follow their tutorial) - that's not standard with your Office suite.