Linking Access to SQL Server
-
"...Just wanted to see if anyone considered using ms access pass through queries to eliminate the translation overhead caused by access odbc to sql server? I would recommend this method over the link tables method for that reason alone. This way - you're writing queries in SQL Server SQL vs. MS Acces SQL...."
Chris - I would go the Access Data Project route rather than use pass-through queries with an .mdb. The ADPs are more straightforeward, and the connection to SQL Server is via OLEDB rather than ODBC.
With and .mdb and pass-through queries, you still are carrying around the weight of the Jet engine and most of the table-linking "plumbing" - the only thing you're really cutting out is the Access-to-TSQL translation step.
With an .adp, there's no Jet engine overhead, and the "plumbing" connecting the front end to Access is much leaner and more efficient.
There are other benefits to using .adp's as well- just one example - you can use stored procedures directly as the recordsource for a form or report, or even as the rowsource of a combo box or listbox - how cool is that!
Best regards,
SteveR
-
One think I particularly like is that you can cut and paste into (or from) Excel. I often have to send adhoc data samples in excel to our test team to look at and getting them through Access rather than using query analyser or writing a dts, means the data is already nicely formatted and has column headers included.
-
Has anyone seen a tool for connecting to SQL server with the capabilities of Access that would run on the MAC platform as well as a PC? We have a large MAC population here. I often use Access as a front end for staff assistants to enter data into SQL and print reports. I would like to be able to write one application that would work on both MAC and PC. Any thoughts or ideas?
-
This can also be done with and Access ADP rather than a MDB project the ADP allows SQL pass through so that any queries written against the data can take advantage of the regular SQL server syntax rather than the convuluted Access Syntax.
-
Joe,
I'm afraid you're SOL there, son. There is a Microsoft Office for the Mac, but it doesn't include Access.
Steven W. Erbach
Neenah, WI
http://thetowncrank.blogspot.com -
Derek,
You make some excellent points. Regarding pass-through queries, I've never used them since I skipped right to ADP's and Views.
Steven W. Erbach
Neenah, WI
http://thetowncrank.blogspot.com -
Lovely article.
Two problems I've come across:
1) Nice three table view with calculated fields fully updatable in SQL server - lots of lovely triggers. Put it in an adp - not a chance. Put it in an mdb with the primary key set, fully updatable. Hmm!
2) Same view, all the base tables have audit triggers to check who changes important fields. T SQL is based on
- IF UPDATE(fieldname)
- write to audit table
This works fine with T SQL, but use Access and every field is marked as dirty and you get all the relevant fields written to the audit table. I've had to resort to
IF (SELECT fieldname from inserted) != (SELECT fieldname from deleted)
I'd love to use a project if only it worked
John McCabe
-
I tend to use the mdb over the adp format, just from habit as much as anything. I could entirely be wrong, but doesnt using the ADP format remove the use of module code? It does expose procs and triggers, which the mdb's do not.
I'll do a follow up article on the ADP stuff, I need to do the research anyway and it should make for a good discussion.
-
Just as a quick follow-up, Andy. (What a great discussion on this point! Very helpful.)
I have chosen to create an unlinked MDB file, using ADO Commands to drive the Stored procs, using a global connection object which remains open while the user is logged in. Reason is that I still want to be able to create local tables and copy lookup data down to local tables to fill combo boxes, etc. You can't do that with ADP files, but you CAN store the same data in XML format on the local machine with ADPs. BY the way, you CAN still write VBA code to run within ADP files.
Andy, as you said you were going to research this more, can I suggest a reference book for you, which covers Access, SQL Server 2000, and especially the MDB vs ADP options. The book is loaded with code examples for both MDBs and ADPs. Here's the details: -
Title: "Microsoft Access Developer's Guide to SQL Server"
Authors: Mary Chipman and Andy Baron
SAMS Publication, 2000
ISBN: 0-672-31944-6
I hope others find this is much help as I have.
Lester Vincent
-
Quite agree.
Absolutely excellent book.
Source code available on line.
Interesting when I did a Google on this I got
http://www.databaseadvisors.com/gazette/sqlexpress.htm
Which relates to Yukon and its lack of support for adps.
-
What in increadibly dangerous thing to suggest. If one of my DBA's - or anyone else in the department of company - linked MS Access to a production database they would be lucky to be in a job at the end of the day.
Where is the control? Where is the accountability? Where is the testing? The proving that the change works? We aren't messing about with our CD collections here. Any business that thinks it's OK to mess around with data like this is heading for a fall.
regs
Neil
-
>Where is the control?
You do have permissions set on tables and views don't you?
>Where is the accountability?
You do keep an audit log don't you?
>Where is the testing?
Agreed. Perfectly valid point. Depends how much work you are actually doing. For one or two simple search and replace type options should be OK. You do have backups and transaction logs don't you.
>The proving that the change works?
That's why we have backups.
Part of the reason for linking a database to access is to take account of its reporting features as much as its editing capabilities.
I'm in the process of creating a College Level MIS with Access as a front end. One of the things senior management like is that even though users can see the data they can't do anything too silly either because they don't have write access or because we have lots and lots of constraints on the data. Even if they do something, we can see exactly who, when and what via the audit logs.
-
What have control and permissions to do with each other? I have SA right on every one of my 165 produciton SQL databases and DBA on the 40 Oracle databases that my team manage. Root privs on all of my Unix servers. Any one of my team could log in and do whatever they wanted to a database. Why don't they? Control mechanisms. All changes to data are scripted, ran against copies of the Production system, the output recorded, the copy of the system tested and then, once we are satisfied that the data change is acceptable we re-run the exact same code into production, check that it still updates exactly what we expected and then commit. and it is always done transactionally. Not hacked-in through a linked table.
Auditing does not equal accountability. It merely show you who messed the system up. Unless they hide their tracks - quite easy to do if you are local admin and SA.
> Testing
you always test, test and test again. No matter how "minor" the change. I have seen the smallest of changes take systems down.
> That's why we have backups.
Not very useful on a 24x7 system. How can you restore from your mistake if users are continually entering transactions?
regards
Neil
-
Surely this is to do with size.
When you are a large company with 5000 users you need the safety and security of scripting in and out changes and accountability.
If you are a small or medium company the speed and ease of use of something like access is more important and more beneficial.
Horses for courses.
(Personally I prefer the database work in EM and then the reports of access but we cant have it all)
Sam
-
Neil,
» > That's why we have backups.
» Not very useful on a 24x7 system. How can you restore from your mistake if users are continually entering transactions? «
You beg the question, "Well, what do you do for backup on a 24/7 system?" I presume you have some sort of massive mirroring or duplexing going on, yes?
I can see absolutely that your concerns about the availability and reliability of the data are paramount in the systems you're talking about. For others, like one of my clients, whose SQL Server data is only accessed during business hours and then only sporadically, Access front ends can be very useful, besides being quicker to set up than, say, an ASP or ASP.NET web page.
Regards,
Steven W. Erbach
Neenah, WI
http://thetowncrank.blogspot.com
Viewing 15 posts - 16 through 30 (of 71 total)
You must be logged in to reply to this topic. Login to reply