Linking Access to SQL Server
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/awarren/linkingaccesstosqlserver.asp
-
Fantastic article, Andy! I guess you would not recommend to have a lot of links like this concurrently across the network. I am currently working the other way, transforming an Access 2K app with 170 tables linked to a Server- based .MDB file, and concurrently running on 12 workstations. The network goes like cold molasses!
My goal is to convert to an Access front-end, linked only to a "local" MDB file and for the rest, to use pass-thru queries and ADO Commands to get access to the same data in an SQL Server database. Going well so far, but a lot of conversion work.
I particularly like your article, as I have just about lived 24/7 with Access and VBA coding for the past 6 years. and what you describe is very familiar ground.
Regards,
Lester Vincent
(very much a beginner in SQL coding. Still trying to fully utilise Query Analyser features)
Sydney
-
You can also use Access Project which is part of Microsoft Access 2000,2002,and 2003. It does not require link tables. I am using Access project in thre application with number of user over 40. Access project put the process on the SQL Server.
-
OK it works I agree and sometimes Access is nice (good reports for example) but why would you choose to do this over using enterprise manager?
I cant think of anything Access can do that enterprise manager cant,and generally enterprise manager is better most thing (table changes, scriptiong out stored procs etc etc etc).
Sam
-
Sam,
Often this is a way to let your developers, or you (shrug) put together a quick ACCESS application while the data remains in SQL, with all the backups etc concerns hopefully cared for. While I don't like coding ACCESS front ends, sometimes...
Terry
-
Sam,
In answer to your questions:
1) Access is a way to ease into Enterprise Manager for those that are not SQL Server savvy. EM has many capabilities relating to administration and security that would confuse a SQL Server tyro.
2) You, yourself, acknowledge that Access reports are VERY easy to put together. It's a very mature reporting tool that can produce excellently formatted output with very little effort.
3) Access data entry forms are very easy to deal with, too. With a connection to SQL Server data someone familiar with Access can readily provide a menu-driven, forms-based SQL Server application without having to go through the travail of writing something in ASP or ASP.NET. The Access extensions to Visual Basic for Applications makes for powerful and relatively easy development using SQL Server data.
4) The Access query by example tool is easier to use than anything provided in EM.
I'm not saying that EM isn't a terrific tool -- especially in SS2005 -- but Access' native ability to connect to SQL Server data makes for very friendly application development and querying without the learning overhead of EM.
Steven W. Erbach
Neenah, WI
http://thetowncrank.blogspot.com -
Andy,
I was also surprised that you didn't mention Access Projects. For those that are EM aficionados, the Access query by example interface is much closer to that in EM, as opposed to that offered when one uses ODBC to connect.
For example, in an Access Project, the SQL Server functions and syntax are all available in the Access QBE tool. Therefore, you use '%' instead of Access' '*' as a wildcard character; and GETDATE() and CAST() are used instead of their Access equivalents.
In addition, it's lots quicker to set up an Access Project for existing SQL Server data than it is to set up an ODBC connection.
Steven W. Erbach
Neenah, WI
http://thetowncrank.blogspot.com -
Hi Terry and Steve,
I agree with both of you that there are definate advantages with access report ease of use etc. The first SQL I wrote was in Access (which I guess is true of many people)
The only thing really to say is that we have written applications in access before but time and time again all of our developers have come back to using EM to do all the database work and then using and adp/ade to give people the application.
But I do see that giving out an ADE is much quicker and easier that develping something in visual studio and releasing it (For example).
Sam
-
Terry,
A friend on the Access support forum I subscribe to once described his experience of updating an existing Access application. He had been working almost exclusively with Visual Studio and ASP.NET/ASP/VB/C#, etc., for a couple of years before he was asked to update this Access application (which he had written).
He said that very shortly after he sat down at his workstation he was gaily coding away and typing so fast that people actually came over to him to see if he was faking it!
The point is that Access is a very mature product with lots of high-level developer tools to grease the old application development skids. It certainly isn't for huge, server-draining applications, but in its niche there isn't anything that touches it.
Steven W. Erbach
Neenah, WI
http://thetowncrank.blogspot.com -
I have been using the project files (.ADP) for some time. I found them a lot quicker to set up than the .MDB method. Is there anything you can do with the .MDB method you can't do with the project files?
-
Good article, as always, Andy!
Since Access 2000, I've been a big proponent of using Access Data Projects (adp's) when possible - there's less "plumbing" between the query designer/form/report and SQL Server, and as Steve Erbach rightly mentioned, you get to use T-SQL syntax instead of Access SQL.
Having said that, linking Access to SQL Server just the way you showed us is an excellent way for people to expand their SQL knowlege, if that's what they want to do.
I started life (at age 43) as an Access developer in Jan 1993. Steve Jones - you'll get a kick out of this - it was at Calvert Cliffs Nuclear Power Plant! A year later, our large IDMS mainframe-based maintenance & equipment database was converted to Oracle 7.3, and I found Access with ODBC to be a much more useful querying and reporting tool that what was then available from Oracle at less than 1/3 the cost per seat. Over the next few years, I easily taught hundreds of people on site how to get information out of that Oracle database - something that had been a black science using arcane JCL and FOCUS before '94.
When I became a VB developer in '98 and had to program against SQL Server databases, I started by first linking with Access and using its query designer to generate SQL, which I pasted into Query Analyzer. By trying to run that SQL and correcting the mis-translations, I quickly got used to T-SQL, and soon abandoned the "via Access" route. But Access turned out to be great SQL Server Training Wheels.
Access was revolutionary in '93, and now, even after 13 years, it's still a very productive environment. I rarely do any Access project any more, but I always enjoy them when I do.
Best regards,
SteveR
-
Although I mostly use project files (adp) to connect to SQL Server, sometimes I use Linked tables in Access MDB file simply because I do not intend to save my queries as Stored Procedures. I create a lot of queries to test/validate data in production servers which I may only have select permissions. Access MDB allows me to quickly create these queries and not to worry about messing up production database.
-
Rasool,
Good point. I find that I re-use a couple saved Views and SP's in an Access Project to get around having to save each one in the production database.
Steven W. Erbach
Neenah, WI
http://thetowncrank.blogspot.com -
Hey Guys,
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
-
My own opinion on a few of the issues here:
1) An Access front end application (either MDB or ADP) is great for distributing for a small number of people who need flexible access to data in SQL. Much better than EM, especially for ones who are NOT tech-savvy
2) Performance can really suck, depending on the application and the bandwidth. It has to do with the way data is cached in Access and becomes very apparent if you have large tables (>100,000 rows). However, it doesn't justify pass-through queries (obsolutely NOTHING justifies pass-through queries). Views (in SQL server) are your friend.
3) Performance can be greatly enhanced by using a timestamp field in the SQL tables.
4) Use file DSNs instead of system DSNs. They can be copied around just like the mdb file can.
5) MDB allow local tables (useful for some settings, paths, etc.) while ADP files (Access projects) do not.
6) Security and connections are handled differently in MDB and ADP, so you can't jsut switch between them.
7) MDE files are also an option. They are compiled MDB files that don't allow the user to make modification fo the front-end objects (screens, reports, etc.)
Viewing 15 posts - 1 through 15 (of 71 total)
You must be logged in to reply to this topic. Login to reply