November 14, 2017 at 9:05 am
Is it just me, or once you've used SQL Server, is using Access again (especially for reasonably complex queries) just brutal?
I guess I kinda like having parameters that I can see and use to branch (if necessary) in my stored procedures...
And while I'm ranting, how much data is reasonable in Access? (Depends on your design... I know!) I'm looking at a job posting, and they use SAP and Office, so Excel/Access etc. Can't help but think a few stored procedures or views inside SAP would be extremely handy.
They're tracking employee ASE certifications... so it's like Students & Classes meets resource management. (Think Banner... <shudder>). Anybody ever use Access to talk to SAP?
November 14, 2017 at 10:51 am
pietlinden - Tuesday, November 14, 2017 9:05 AMIs it just me, or once you've used SQL Server, is using Access again (especially for reasonably complex queries) just brutal?
I guess I kinda like having parameters that I can see and use to branch (if necessary) in my stored procedures...And while I'm ranting, how much data is reasonable in Access? (Depends on your design... I know!) I'm looking at a job posting, and they use SAP and Office, so Excel/Access etc. Can't help but think a few stored procedures or views inside SAP would be extremely handy.
They're tracking employee ASE certifications... so it's like Students & Classes meets resource management. (Think Banner... <shudder>). Anybody ever use Access to talk to SAP?
A few things that might help you along the way:
- SAP has several OLE DB providers which, if appropriate, you could use to get data from SAP.
- Even with the above, my experience of getting data out of SAP was still much cleaner to go old school (file based extract, import into your UI, and re3verse the flow in the other direction). SAP's data model is fairly insane so trying to directl navigate was a bloody mess.
- Access was and always has been ugly in a multi-user setting. Even if it's possible the data access is horrible, and record locking is patchy at best. As a result, I would recommend AGAINST storing any form of data in Access itself.
- Using Access as a UI to get data into or out of an RDBMS is actually not half bad (as long as you do NOT use the "table link" option). Over the years it has increasingly incorporated more and more of .NET coding, so lots of the "standard" .NET data access objects are available (including calling stored procedures to retrieve, store or update data.
- linked tables on the other hand are horrendous, given the mess they make of locking rows, etc...
In short - don't go back, go forward.
Unless there's a HUGE amount of data to pull, you might care to talk them into SQL Express or an actual DB solution. Just sticking to Access usually means your non-functional stuff (availability, recoverability, etc...) goes by the wayside. If the org isn't willing to make sure that the data can survive, then the job isn't worth pursuing.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 14, 2017 at 2:30 pm
Matt,
Thanks for the pointers!
Okay, so I could basically use Access as a temporary "container" for the data, and then do something like...
1. export from SAP to some kind of delimited text file.
2. import to Access table(s).
3. push to SQL Server using a passthrough query? (I've always used linked tables for that part... might not be a great idea).
Then I could create views and stored procedures and use them as data sources for reporting...
I don't think the company and I agree on what constitutes a "big" database. 4000 "students" in what is essentially a "students and classes" database isn't really that much. Some good indexing will sort that right out. Then I could just run a stored procedure in Access or Excel or whatever to bring down just the data I needed.
November 14, 2017 at 6:22 pm
pietlinden - Tuesday, November 14, 2017 2:30 PMMatt,
Thanks for the pointers!Okay, so I could basically use Access as a temporary "container" for the data, and then do something like...
1. export from SAP to some kind of delimited text file.
2. import to Access table(s).
3. push to SQL Server using a passthrough query? (I've always used linked tables for that part... might not be a great idea).Then I could create views and stored procedures and use them as data sources for reporting...
I don't think the company and I agree on what constitutes a "big" database. 4000 "students" in what is essentially a "students and classes" database isn't really that much. Some good indexing will sort that right out. Then I could just run a stored procedure in Access or Excel or whatever to bring down just the data I needed.
Yup - that's a good starting point. Pass-through queries for data updates (writing the data back) made all of the difference over the issues with opportunistic locking.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 14, 2017 at 6:47 pm
Okay, got it. Thanks! I'll watch a video or two until I get my head around it. Just want to have some idea of what I might be doing before going to the interview.
November 16, 2017 at 5:48 pm
I think I should be able to do this with SQL Server Express (provided I could even install it somewhere there). 10GB of space should be plenty for bare minimum info on 40K technician records, and maybe 1 million exam records (generous allowance for "exam scores", so the table would be pretty narrow). Then I should be able to use Access as a front end for that, right? Just use pass-through queries if I'm using stored procedures on the back end?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply