ODBC, Access, and Power Query

[cmcreynolds]

Backstory - New job as an analyst and our account management software (an ODBC) is "older" I suppose. The other analyst has been using Access 2013 for a while and many queries are run there. Even though I used Power Query a lot at my last position, our chief information officer wasn't familiar with it and wasn't comfortable with how it would work or affect our AMS.

So, I decided to do something that I thought was "safer"? I used Excel 2013 and Power Query to grab an Access query (instead of querying directly to the AMS database). These are just five queries with about 5-7 fields in them and maybe 20,000 rows. Plus, I like PowerPivot's user interface.

Anyway, yesterday I literally broke our entire AMS - it came to a literal standstill. I got an OBDC failed error in Access that froze out all of our customer service reps from the AMS. This apparently happened last week, too, but they finally narrowed it down to one user - me. So, now I'm wondering what I did wrong.

I did have Access and one of the queries open in design view (no other tables, no other queries, just this one) as well as the Excel file with the Data Model window open. Should I not have done one (or more) of these things? Does PQ force a "weird" type of connection to Access?

Our IT department wants to only use SSMS, but I am not completely familiar with writing out SQL (that's why I liked PQ and PP, very easy to understand). So, have I destroyed any possible PQ relationship here?

Just wondering. Thanks for any insight.

 

[Matt Allington]

Well I'm sorry that this has happened to you. I know you love PQ and PP and there is nothing worse than crashing a system and getting IT off side (for your crusade to improve things). But I'm not clear what has happened here. You ask if PQ does a wired connection to Access, but your post suggests you are connecting to the accounting system. So are you connecting to a Access or the accounting system?

 

[cmcreynolds]

It was kind of a "chain" - I was connecting PQ to Access which was connected to the AMS.

 

[Matt Allington]

I guess it is possible that Access has live connections to the AMS. If so, then it would presumably create queries itself to connect in real time. Conceivably I guess if you queried Access and that then queried then AMS, then bad things could happen. But I would hardly blame you for that. In my view, no IT department worth its salt would do a direct connection to a production AMS.

 

[cmcreynolds]

Yes, we are in "communication" about just that Another analyst does not feel comfortable with running queries on a "snapshot" obtained at 4 a.m. every morning. He wants absolute "last minute" versions of all tables.

 

[Matt Allington]

Presumably then a snap shot at 7am is useless at 7:05

 

[propi_]

Hey, here some Ideas:

First ask your IT for a shadow copy from the past. If they have the shadow copy service enabled there should be backup versions at every xxx minutes / hours /days.
Then work with this and test with an "non productive" version of that (recommended if its that important )

Otherwise there was a repair funtion for Access DB once in past versions...

I would always go directly on the DB via ODBC or else, because there will be always insecurity in what your Access does in Background. Also the Data needs to be loaded in Access first which cost time an performance.

Hope it helps a bit and good luck

regards,
Andi

 

[cmcreynolds]

@MattAllington - Yes, the issue stems from "I just ran a query and I noticed data input errors (e.g. member DOB as 12/13/2056) and I want to correct it in the AMS and re-run my query with corrected data" Waiting until the following day is too long to wait, I guess?

@propi_ - I think that's where we are headed. I would LOVE to just connect to the shadow copy via PowerQuery and leave Access out of it, but we as a company are not yet on the same page.

Thanks for your help, though.