Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Linking Access to SQL Server


Linking Access to SQL Server

Author
Message
Andy Warren
Andy Warren
SSCrazy Eights
SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)

Group: Moderators
Points: 9163 Visits: 2725
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/awarren/linkingaccesstosqlserver.asp

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Lester Vincent
Lester Vincent
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 134

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


Bridget Elise Nelson
Bridget Elise Nelson
Old Hand
Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)

Group: General Forum Members
Points: 390 Visits: 72
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.
sam metland
sam metland
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 3
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
TDuffy
TDuffy
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1376 Visits: 57

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





Steve Erbach
Steve Erbach
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 14
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
Steve Erbach
Steve Erbach
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 14
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
sam metland
sam metland
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 3
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
Steve Erbach
Steve Erbach
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 14
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
Joe Bigler
Joe Bigler
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 4
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search