Re: ADP vs. MDB?

I just re-converted my .adp project back to an .mdb. Lots of reasons for
it - primarily we found .adp was just full of irratating bugs. Works faster
in an .mdb which I just can't understand!

One major problem we had was that in an adp all calls to the database seem
to run in the same connection context (i.e. adp opens one connection to the
db, and then runs everything down that connection - when you close the db
the connection closes). This caused us all sorts of problems - not least
when certain idiots (i.e. me) omitted a certain "commit tran" after a
"rollback tran" I ended up with ado throwing away the error message (it
seemed to go into the recordset returned by the command!?!) and then every
subsequent insert / update / delete command ran inside that open-ended
transaction context. When you then shut the db all of the changes you made
since the rollback tran get thrown away. Led me to make the decision to
revert to an .mdb, open and close my own connections as required, implement
asynchronous data interaction with ado (i.e. background processing &
fetching of recordsets), client side cursors (no more endless recordlocking
for people who've left the admin client open) and a proper error handling
system - and I also went away and closed up that stupid open ended
transaction.

Still get a few crashes though. Has anyone else had this sort of experience
making heavy use of ado, access 2003 & sql server? Seemingly pretty random
(sometimes get proper crashes, sometimes access just closes itself without
any warning).

Nick




"Malcolm Cook" <malcook@xxxxxxxxxxxxxxxx> wrote in message
news:%23W2lPS6zFHA.3780@xxxxxxxxxxxxxxxxxxxxxxx
> "Mary Chipman [MSFT]" <mchip@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:l2tqk1tq000q11h4jb4tlg936upp6u7dpi@xxxxxxxxxx
> > The Access team at Microsoft recommends that for new Access-SQL
> > projects you use an mdb for the front-end.
>
> Do you have a quote or other reference for this statement? Do they give
reasons?
>
> Inquiring minds, etc...
>
>
> --Malcolm
>
>


.

Relevant Pages

  • Re: Upsizing questions
    ... Should I keep using the mdb as frontend or switch to an adp (access ... Nothing prevents you from creating a new connection using code, ...
    (comp.databases.ms-sqlserver)
  • RE: Access Front End & SQL Server Back End Connection
    ... I believe that if you're using SQL Server, unless you have a need for the ... additional capabilities afforded by use of a .mdb (e.g., ... tabling, heterogenous data source linking, etc.), then stick with an .adp. ... > grips with the options and settle on a good approach to making the connection ...
    (microsoft.public.access.modulesdaovba)
  • Re: Access 2003 to SQL Server 2000 over a VPN
    ... I don't know about performance with ADPs; but I have an MDB (with an MDB ... general, seems to have some problems with performance, whether ADP or MDB. ... I'm using a fairly complex database using and ADP ... the front end over a VPN connection. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: mdb read only - not the usual problems
    ... Connection gets closed even if an error occurs ... ... > I have an mdb that I open using asp.net. ... this resolves itself auto-magically in a couple of hours. ... > code opens a db con, gets data, and closes it. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Access Project (adp) with Sql Server 2000 - First Connection Latency
    ... I close the adp, wait for 1 minute, open it again and it takes 15 sec. ... I close it again, don´t wait, open it again and it opens immediately. ... Created new ADP and the Connection opens always ... Customer will try to install Access XP/ 2003 on a machine with the ...
    (microsoft.public.access.adp.sqlserver)