We help IT Professionals succeed at work.

Difference between ADP, MDB & SQL

bmtech asked
From experimentation it would seem that an Access ADP is a Microsoft Access front Ended Database (that can be) served from SQL Server, which is fine. Where as an MDB file is a true Access Database served and run by Access.

Now where I am having trouble is that I want to use the Access front end (because of the forms reports macros etc), but it would seem that each user of the Database needs their own version of the ADP file, is this correct? (If multiple users try to use the same ADP the second one in gets a 'Read-Only error'). If so what are the implications of this? Is there anything I need consider.

Can anyone also explain what exactly the ADP is, does it hold any actual data? Do I need to create a seperate one for each user, or can I give out multiple copies of the same file?

Thanks I know its a long question.
Watch Question

here's my take. i've never gotten to use an ADP but i use a97 as a frontend to sql server in the same manner that an adp does.

the best way i've found to handle this is to create one "MASTER" application and then distribute (through your favorite means) a "locked" version or a "front end" version which contains only linked tables, forms, modules and reports. i only distribute those four objects because once they are secured (an .mde for instance) they are NOT editable (execpt for the tables which is a whole OTHER matter) in any way. this is pretty good, basic security.

i wouldn't imagine an ADP holds any data (though i can't be certain as i've never used one) but personally, i wouldn't store any data there as a general rule. especially if you distribute your app to more than one individual. the only "data" the adp holds are the objects that you create (forms etc.) which isn't "data" but it depends on your definition of data. lol.

as i stated earlier, you can easily create one master db and distribute that.

i welcome any further comments that other experts have. i've never used an adp, but i do exactly what an adp does with a97 and i look forward to the day when i "get" to convert my old db's to adp format.

good luck


>If multiple users try to use the same ADP the second one in gets a 'Read-Only error'

That error says that user can not make changes to the design and that error displays even when you open ADE in shared mode, not that's no problem at all, moreover, for end-users you'd better make ADE file, not ADP and they'll never see that message in shared mode.

>i wouldn't imagine an ADP holds any data
Well, indeed, unfortunately it does not , I'd be happy if I could have local tables too for cacheing locally

>i do exactly what an adp does with a97
So seems in Acc97 you use pass-through queries that indeed are executed by SQL server or use ADO (DAO) in your modules to manipulate data. I suppose ADP is nothing but the same done by Microsoft, so why code Connection object, stored procedure calls in MDB, when there are ready-made objects in ADE?
Well, that's my understanding of situation, which may differ from official view of MS, :))
Good luck


Sorry forgot about this one. :-)