Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Linking Access to SQL Server Expand / Collapse
Author
Message
Posted Tuesday, November 29, 2005 8:28 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Monday, September 15, 2014 9:16 AM
Points: 6,784, Visits: 1,895
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
Post #240596
Posted Wednesday, January 25, 2006 4:55 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 27, 2010 1:33 AM
Points: 67, 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

 

Post #253339
Posted Wednesday, January 25, 2006 5:17 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, August 25, 2014 10:04 AM
Points: 381, Visits: 64
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.
Post #253343
Posted Wednesday, January 25, 2006 6:44 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, November 27, 2007 2:15 AM
Points: 75, 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
Post #253359
Posted Wednesday, January 25, 2006 7:08 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 15, 2008 12:02 PM
Points: 1,318, 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




Post #253372
Posted Wednesday, January 25, 2006 7:18 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 7:40 AM
Points: 57, Visits: 10
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
Post #253380
Posted Wednesday, January 25, 2006 7:26 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 7:40 AM
Points: 57, Visits: 10
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
Post #253385
Posted Wednesday, January 25, 2006 7:33 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, November 27, 2007 2:15 AM
Points: 75, 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
Post #253390
Posted Wednesday, January 25, 2006 7:44 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 7:40 AM
Points: 57, Visits: 10
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
Post #253398
Posted Wednesday, January 25, 2006 7:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 23, 2007 1:27 PM
Points: 4, 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?
Post #253401
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse