What I am rying to do is use a stored proc in SQL Server and feed it some parameters making it dynamic. What the stored proc does is a query to an excel spreadsheet and compares whats different in a SQL database. The crazy part was that
- using ADODB in VBA worked once under certain conditions,
- MSQuery works in others and …
- doing it with eh Import Data Wizard from the Data tab for SQL Server did NOT work – which is the one you would expect WOULD work best (the native one)
So – I noticed that MSQuery worked – and frankly I don’t care what method – I need answers – not slick solutions for other people in this case – this is me doing engineering to get an answer and not making a solution for someone else. Anyway, what I did is recorded all the scenarios after they worked. What did not work is doing it all by hand in VBA but this was to a different server using a trusted connection – the old way that did work in VBA was done with a username/password combo.
MS Query to the rescue. Here is the fellow that inspired me to try this. THen once I had it all working, I understood and edited the VB.
Some tips/learning along the ways was
- a) you record making a QueryTable – not a ListTable BUT to reuse the QueryTable , look through the ListTables collection and use the myListTable.QueryTable item (a very tight connection between the two.
- b) follow this article to make a basic table query to ANY table