Gossamer Forum
Home : General : Databases and SQL :

ADP (Access Database Project) Front-End/SQL Server 2000 Back-end

Quote Reply
ADP (Access Database Project) Front-End/SQL Server 2000 Back-end
Hello all,

Been awhile since I've been around and posted, hope all is well.

Anyway, I have a potential new project that may be broken up into two parts:

1) Upgrade existing MDB to SQL Server 2000 and then use ADP as front-end.

2) Convert Access Forms to web-based (most likely PHP)

I'd prefer to do the data conversion from Access to SQL Server 2000, then go right to the web-based front-end, but the potential client wants to save financial resources by incrementing the process.

Anyway, has anyone had success using ADP in a LAN environment with a user base of 40 users with an average of 10 concurrent connections?

From what I've read and also past experience using ADP is that it's as clunky and cumbersome as using one MDB file. I've read that using Jet Engine 4.0 and putting the ADP in a shared resource in a centralized server can address some of the connection/access problems, but wanted to know if anyone has successfully implementing using ADP as a front-end to a SQL Server 2000 back-end?

Thanks in advance.
========================================
Buh Bye!

Cheers,
Me
Quote Reply
Re: [Stealth] ADP (Access Database Project) Front-End/SQL Server 2000 Back-end In reply to
Just a quick update on this project (for those interested and come across this thead in the future).

Well, unfortunately, the architecture of the pre-existing Access-driven system has not allowed well for a ADP file to be created. The application consisted of two MDB files (one for the interface, containing queries, forms, and reports) and another MDB file that contained the data/tables.

What I've had to do to get this to work (without having to revamp all the forms, reports, and queries by upgrading codes to ADO) is the following:

1) Export all tables from the "data" MDB file and "interface" MDB file to a SQL Server database. The latter contained a bunch of temp tables that are best suited to be in the same SQL Server database.

2) Linked all the tables in the SQL Server database in the "interface" MDB file.

3) Edited all queries to include the new linked ODBC tables.

A lot of work to say the least, and probably won't be as effective as using an ADP file, but since the client only wants this solution for 6 weeks to carry them into the 2nd quarter, it'll have to do.

If anyone has any suggestions for improving the approach I've taken, I'm open to them.

Thanks in advance.
========================================
Buh Bye!

Cheers,
Me