ADP vs Linked Tables

[sparkbyte]

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.



Thanks

John Fuhrman

 

[danvlas]

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

 

[sparkbyte]

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??


Thanks

John Fuhrman

 

[sparkbyte]

Ah HA...

Do it in a stored procedure.



Thanks

John Fuhrman