Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

ADP vs Linked Tables

ADP vs Linked Tables

ADP vs Linked Tables

I just found out after using the upsizing wizard that if you are using Access 2000/2003 and SQL Server 2005  you cannot save any new queries in Access.

So ........

What are the differences (gatchya's) between the two solutions??

I have a SQL 2000 server but it is much slower than the current SQL 2005 server and because we are a government agency I don't have the ability to simply upgrade the version of Access on the workstations.

This is an important question because we just recieved new server hardware and I am trying to decide what version of SQL Server to install on the new hardware.  Not sure if I want to install SQL 2000 and 2005 on the same server.



John Fuhrman

RE: ADP vs Linked Tables

I would suggest installing SQL 2005, as it has some excellent features that do not exist in SQL 2000: exception (error) handling, the Row_Number function, case-sensitive passwords and some more.

There are 2 ways to migrate an Access application to SQL Server (the Access version is not important):

1. Use SQL server just for storage and use Access for all programming/reporting purposes
This is done through linked tables (ODBC) and require little to no changes in the Access application you already have.
Good thing for the development team.
Bad thing for security and speed. You will need to give all permissions on the tables to have the application run smoothly. This way you leave your SQL Server open to external attacks and it will be just a matter of time until someone screws up your database.

2. Use SQL Server for storage and business rule enforcement and Access just as a front-end to search records and display information.
This is done moving all data to SQL Server tables and business logic to stored procedures and triggers. The ADP file would be only the interface through which you send commands to the server and receive the results, without any data processing on the client machine.
Bad thing for the development team, as they will have to re-write many of the existing queries and VBA code to stored procedures, views and functions.
Good thing for security, speed and scalability of the system. If you take this route, NEVER allow dynamic SQL to be run in the procedures.

You can't change the table or any other server object definition through Access, you'll need either SSMS or scripting routines to run DDL statements on the server.

If you asked me, I would suggest the ADP way.
However, keep in mind that SQL Server is very different from Access, so try to discuss with the developers to see what is best for you.

Daniel Vlas
Systems Consultant


RE: ADP vs Linked Tables

As I am the one doing this an am more familiar with system administration this will be quite the challenge.

That being said, your description, although quite accurate, was not quite what I was looking for.  I know the basic differences between the 2 options.

What I wanted to know about was if there are things outside the 'normal' differences that would swing a decision one way or the other.

To use your explanations.

If using linked tables.
    SQL server table security is difficult to establish at best.
    Can migrate data with minimal amount of effort.
    Lack of table security will most likely lead to data corruption.

If using ADP project
    Database, table and data security is managed much tighter at the SQL server.
    All stored procedures, views, and functions must be created and maintained through SSMS

Also, this works on SQL2000 with ADP


INSERT INTO dbo.tblTrackingParse
     (Tracking_ID, NetworkLogonID
     , MachineName, BoxNumber
     , FileNumber, TrackingDate)
SELECT Tracking_ID
     , NetworkLogonID
     , MachineName
     , BoxNumber
     , FileNumber
     , TrackingDate
FROM dbo.TrackingTableArchive
But doesn't on SQL2005 (view)

How do I get around this??


John Fuhrman

RE: ADP vs Linked Tables

Ah HA...

Do it in a stored procedure.



John Fuhrman

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close