MDB vs ADP 3

[ttyppi]

Hi, am a newbie to MS Access, but I have many years of experience on other databases.
-------
The situation: Customer wants a small db-system to be used in LAN. They had an old single user system based on paradox. The new system would be used by about 5 persons.
-------
I am considering between these two solutions based on Access 2003 professional (customer has it installed already):
A) MS Access MDB
B) MS Access ADP with "SQL Server 2000 Desktop Engine"
-------
Can anyone give me the pros and cons please...
Thanx in advance!

Tarmo

PS
My intuition says:
A is more stabile environment (old and tried)...
B is today's technology (ANSI SQL-92 etc)...


Tried to read this

http://office.microsoft.com/en-us/assistance/HP052731031033.aspx

and other pages there ... but they are very chaotic to say the least

 

[danvlas]

I'd rather go for MSDE+ADP.
It's more reliable, it's true client-server configuration, can be further expanded for a virtually any number of users.


What you gain:

Reliability (database corruption occurs rarely - I have NOT encountered it yet)
Speed in retrieving data when the database grows significantly
TCP/IP operation (very good WAN/Internet connectivity)
Business logic implemented directly on the server, so if you change a stored procedure on the server, the client will work correctly or fail (better than give you some puzzling results). This does not happen in MDB: you change one query, you need to updata all workstations with the new version.


What you lose:

Simplicity (not very much of it though)
Built-in cross-tab queries (but you can use the Pivot forms)
Easy switching between development and production environments

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[ttyppi]

Thank you for the answer Daniel!

I was thinking too that MSDE (now called SQL Server 2000 Desktop) would be the best solution, customer has had enough db corruption problems with paradox

The size of database is rather small (less than 10000 rows in the largest table), so the speed might not be an issue,
not even in the future.
----
This customer is different from any other I've had, because they want easy access to all the data,
not just preprogrammed forms and queries etc.

This is because the database is for genetic research results, and one cannot define what they need next month or next year.

Somewhere I've read that althou MDB has ad-hoc queries and reports, ADP might not allow that, how is it?
Also now and then the customer may need to add new fields in tables, without programmer help, is that possible with ADP?

Tarmo

 

[cjowsey]

An adp will allow you to add queries (they're actually SQL views) and reports just like an MDB. The views will be stored on the database server. It's not really a good idea to let most users go wild in a raw SQL database though unless they knwo what they are doing.

End users adding columns to tables and tables to databases, likewise, is not generally recommended unless they know what they are doing. Maybe this is a cause of their Paradox database corruption?

I'd go the ADP + SQL Server Desktop way personally. It's not that different (especially for an Access newbie) and it will give you more flexible options in the future.

 

[ttyppi]

Thank you cjowsey!
It really sounds that ADP is the best for this customer.

The reason for paradox corruption is unknown.

Earlier they had all data in one single table.
Now the data will be spread in a more planned way over several tables (for example patient and research result).

So before it was easy for them just to add columns,
now it is a bit more difficult. At least it should be planned more carefully so that the entity model won't be "spoiled".

The customer is very knowledgeable what she is doing, at least she could add new columns. New tables would be a different issue, because of the table joins etc.

Anyway I think I can now build a good solution for her.

Eagerly waiting to start the project, the planning phase is just finished.

Once more thank you all for the help.

Tarmo
PS
I think I will report here any commonly interesting problems or other things noticed during the project.

Is there any "quick guide" and feature differencies list for MDB and ADP? If not, I could make one, at least the quick guide

 

[PETE314]

One other option you might want to consider is an mdb that links to the SQL Desktop Engine. If temptables on the client side will be very useful you could link your SQL tables to the mdb and the Jet Engine might be an easier interface for them to create their different queries. you still have the security of SQL, and you can make Pass Through queries using Transact SQL, while still taking advantage of some of the client side features.

Now I don't know your complete and entire situation and I would not even presume to tell you what to do. But A book that was very helpful to me and would be very helpful to you is a SAMS Publishing book....Microsoft Access Developers Guide to SQL Server. by Chapman and Baron.

It is a great book and I would consider a must have for anyone doing work in Access.

 

[wmikeh]

Hi. I had been programming in using mdb files for a few years and just recently started using the adp file. Here is a little bit of what I have found out:
In the future, Microsoft has made no plans to do any more work on Jet Sql... So like you said the adp file is going to be future of access I believe. I have found adp to be a bit harder to learn, but it also has some powerfull features such as triggers that you might find very usefull. With adp you have the ability to scale the database to a much larger amount of users then you can with the mdb files. If you start by using MSDE you can later go to SQL server if the company grows. As far as stability goes, I can only guess as I have yet to finish any large applications in adp. Anyway, I hope this helps.

 

[ttyppi]

Thank you PETE314 and wmikeh!

Last night finally emailed the offer to the customer,
hope to start this project next month, supposedly with ADP.

Tarmo