Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Why a ADP
- Thread starter ykfc
- Start date
-)- Thread starter
- #3
There must be having some misunderstanding. I am still having a question.
If we start a new file, MSAccess give me an option to create one with either a normal .mdb file extension or one with a .adp extension.
I used to work with .mdb only. I could also start a developent project using a .mdb file and create my application using links to sql table, using DAO or ADO objects.
Until recently I install a evaluation copy of SQL server onto my PC, I started to notice the choice of .ADP (which stands for Access Development Project I suppose). When I select ADP it proceeds usually. I believe I could also write most of my applications under that file. But just wonder what are the benefits there and/or on the other hand, do I need to let go some features with a .mdb file.
If we start a new file, MSAccess give me an option to create one with either a normal .mdb file extension or one with a .adp extension.
I used to work with .mdb only. I could also start a developent project using a .mdb file and create my application using links to sql table, using DAO or ADO objects.
Until recently I install a evaluation copy of SQL server onto my PC, I started to notice the choice of .ADP (which stands for Access Development Project I suppose). When I select ADP it proceeds usually. I believe I could also write most of my applications under that file. But just wonder what are the benefits there and/or on the other hand, do I need to let go some features with a .mdb file.
ADP stands for Access data Project and is just a client tool to work natively with SQL Server. Nothing else.
Therefore, all data is passed to the adp file by SQL Server by using ADO and stored procs + views + functions.
In my opinion, it's very fast and reliable, but it can't use data from other sources (unless you write ADO code to connect to other providers).
MDB uses the Jet database engine and ODBC to link the tables. In this case, the data is first loaded in the mdb file, then it's processed through the Access queries.
It's rather slow and not very reliable.
The advantage of this approach is that you can use all sorts of databases as one unit.
HTH
![[pipe]](/data/assets/smilies/pipe.gif "[pipe] [pipe]")
Daniel Vlas
Systems Consultant
Therefore, all data is passed to the adp file by SQL Server by using ADO and stored procs + views + functions.
In my opinion, it's very fast and reliable, but it can't use data from other sources (unless you write ADO code to connect to other providers).
MDB uses the Jet database engine and ODBC to link the tables. In this case, the data is first loaded in the mdb file, then it's processed through the Access queries.
It's rather slow and not very reliable.
The advantage of this approach is that you can use all sorts of databases as one unit.
HTH
Daniel Vlas
Systems Consultant
ykfc,
yes, agree with dvlas, also here's a little anecdote of adp that may help you.
I wrote a little insurance policy app in mdb file. It used standard queries. a little later I wrote dao code. as time went on, it got bigger, and i wrote a lot more dao code.
now the app is gaining some popularity, and I want to move it to sql server for the added benefits, but now there's a headache involved. SQL server doesn't support DAO, only ADO. so now i have to figure out how to make it work - either that or re-write all of my query code - which will be a drag -
If i had been thinking long term I would have just started out from the get-go with an adp file and created my tables in sql server.
I'd say if you plan to have your app scalable for more than 3 or 4 people, or forsee wanting the advanced features that SQL server provides - then use adp and sQL server - if not, just use jet.
I am a nobody, and nobody is perfect; therefore, I am perfect.
yes, agree with dvlas, also here's a little anecdote of adp that may help you.
I wrote a little insurance policy app in mdb file. It used standard queries. a little later I wrote dao code. as time went on, it got bigger, and i wrote a lot more dao code.
now the app is gaining some popularity, and I want to move it to sql server for the added benefits, but now there's a headache involved. SQL server doesn't support DAO, only ADO. so now i have to figure out how to make it work - either that or re-write all of my query code - which will be a drag -
If i had been thinking long term I would have just started out from the get-go with an adp file and created my tables in sql server.
I'd say if you plan to have your app scalable for more than 3 or 4 people, or forsee wanting the advanced features that SQL server provides - then use adp and sQL server - if not, just use jet.
I am a nobody, and nobody is perfect; therefore, I am perfect.
![[smarty]](/data/assets/smilies/smarty.gif "[smarty] [smarty]")
- Thread starter
- #8
The point of my previous comment was that the statement is completely incorrect. I would expect DAO to remain an option for 32 bit software but I suspect it won't make the move to 64 bit applications. But I doubt if ADO will make that jump either - it will all be .net.
(In the context of ADP then ADO is the route to follow but i felt that the No-DAO statement was being put forward as a general position which is just not true).
(In the context of ADP then ADO is the route to follow but i felt that the No-DAO statement was being put forward as a general position which is just not true).
wait- i don't mean to post misinformation here
- so if it supports dao then how come none of my dao code works?
also, i could have misunderstood, but I thought I had read in several places that what i posted above was true
if SQL server does work with DAO then i will be very glad to be wrong, b/c it would make my life a lot easier right now and I would certainly like to see more information on how you get it to work. Are there any web tutorials?
thanks
I am a nobody, and nobody is perfect; therefore, I am perfect.
- so if it supports dao then how come none of my dao code works?
also, i could have misunderstood, but I thought I had read in several places that what i posted above was true
if SQL server does work with DAO then i will be very glad to be wrong, b/c it would make my life a lot easier right now and I would certainly like to see more information on how you get it to work. Are there any web tutorials?
thanks
I am a nobody, and nobody is perfect; therefore, I am perfect.
As I've understood, you have to use ADO when using ADP. But if you link to SQL server from an mdb, you can use DAO as much as you like.
It seems there are a lot of developers prefering to use mdb's linked to SQL server, in stead of using ADP's.
Roy-Vidar
It seems there are a lot of developers prefering to use mdb's linked to SQL server, in stead of using ADP's.
Roy-Vidar
ok - i guess i should have said SQL server does not support DAO when used with ADP and that i have in fact tried using SQL server with mdb without changing my DAO code with mixed results.
The problem i ran into was poor performance. if you'd care for some elaboration check out the discussion on one of my posts
thread962-805951
my fault for being unclear - i hope i'm more accurate this time.
I am a nobody, and nobody is perfect; therefore, I am perfect.
The problem i ran into was poor performance. if you'd care for some elaboration check out the discussion on one of my posts
thread962-805951
my fault for being unclear - i hope i'm more accurate this time.
I am a nobody, and nobody is perfect; therefore, I am perfect.
Outside the little I know, I'm afraid. It seems you are not the only one suffering with this, here's a Google-thread
Roy-Vidar
Roy-Vidar
Thanks Roy - that's really good to know, i appreciate this discussion.
on the flip side, i was in fact able to set up the exact same mdb file with SQL server on an entirely different network and it worked well.
go figure.
however, i never solved my problem here, at this location, and in fact am working on it today, and through this week.
I am a nobody, and nobody is perfect; therefore, I am perfect.
on the flip side, i was in fact able to set up the exact same mdb file with SQL server on an entirely different network and it worked well.
go figure.
however, i never solved my problem here, at this location, and in fact am working on it today, and through this week.
I am a nobody, and nobody is perfect; therefore, I am perfect.
What happens if you set a reference in adp to DAO? It should work just fine...
Open a VBA window, Tools-References, scroll down till you find DAO and check the box.
Didn't try the functionality, but I don't see what could prevent it from working...
HTH
Daniel Vlas
Systems Consultant
Open a VBA window, Tools-References, scroll down till you find DAO and check the box.
Didn't try the functionality, but I don't see what could prevent it from working...
HTH
Daniel Vlas
Systems Consultant
boy, it would be cool if that worked.
ok danvlas, I tried adding that and then I closed-reopened the adp file and uncommented out one of my dao procedures that is called on afterUpdate of a combo box.
the dao code blows up on this line:
Set rs = db.OpenRecordset(strSQL)
with the error message "object variable or with block variable not set"
i don't get it - this code never errored out in my mdb file.
Any ideas?
I am a nobody, and nobody is perfect; therefore, I am perfect.
ok danvlas, I tried adding that and then I closed-reopened the adp file and uncommented out one of my dao procedures that is called on afterUpdate of a combo box.
the dao code blows up on this line:
Set rs = db.OpenRecordset(strSQL)
with the error message "object variable or with block variable not set"
i don't get it - this code never errored out in my mdb file.
Any ideas?
I am a nobody, and nobody is perfect; therefore, I am perfect.
The usual way of instantiating a db variable, set db = currentdb won't work, cause currentdb isn't a member of an ADP project.
You would need to use other means of assigning. You should be able to find something through a search, here are two, don't know how relevant they are, though thread705-930883, thread705-575337,
Roy-Vidar
You would need to use other means of assigning. You should be able to find something through a search, here are two, don't know how relevant they are, though thread705-930883, thread705-575337,
Roy-Vidar
but if the adp lets me install the dao 3.6 library as danvlas suggests (which it did), then shouldn't I then have access to all of the objects and scripting tools that I did before?
Also, how come then, the code isn't erroring out on
Dim db As Database
or
Set db = CurrentDb
It seems like it get's by that ok, only to error out on the set statement for the rs
also, autocomplete still gives me all the choices from the dao library i had before, which would insinuate I can use them, but maybe not.
I am a nobody, and nobody is perfect; therefore, I am perfect.
Also, how come then, the code isn't erroring out on
Dim db As Database
or
Set db = CurrentDb
It seems like it get's by that ok, only to error out on the set statement for the rs
also, autocomplete still gives me all the choices from the dao library i had before, which would insinuate I can use them, but maybe not.
I am a nobody, and nobody is perfect; therefore, I am perfect.
- Status
Similar threads
- Locked
- Question
- Locked
- Question