Moving from mdb to adp

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

I am thinking of moving my mdb to an adp, primarily to
take advantage of SQLs ability to send only the requested
records.

ie I will use stored procedures to pass across records
for today, this week, this year etc - I am assuming this
is quicker than using a criteria driven query to an
access database.

(my table has 4000 records, today might only be 5 records)

Firstly - do I understand correctly the benefit of moving
to adp?

Second if the above is an advantage to me, does anyone
have a sample of a good way of recreating an access logon
for an adp, the first thing my mdb does if recognise who
the current user is and assign the appropriate global
variables and permissions.

I would need to do something similar for the adp (
including a logon password)

Also what happens to the permissions held on the access
workgroup file??

Thanks in advance Paul
 
Paul said:
I am thinking of moving my mdb to an adp, primarily to
take advantage of SQLs ability to send only the requested
records.
Click to expand...

If you want to experiment with ADPs I think that's a great idea and you
might very well find many benefits of doing so, but you should know before
starting that using an MDB with ODBC would still allow you to use Stored
Procedures, Views, and Pass-Throughs to force server-side processing and
only return the results. In fact standard Access queries against links
also do this most of the time.
ie I will use stored procedures to pass across records
for today, this week, this year etc - I am assuming this
is quicker than using a criteria driven query to an
access database.
Click to expand...

Every particaular case would need to be tested. It is not a foregone
conclusion that anything will be faster than what you have now.
(my table has 4000 records, today might only be 5 records)

Firstly - do I understand correctly the benefit of moving
to adp?
Click to expand...

The primary benefits of an ADP is very tight integration with the server
and its objects. This might translate to performance improvements, but
that should not be your primary reason for converting.
Second if the above is an advantage to me, does anyone
have a sample of a good way of recreating an access logon
for an adp, the first thing my mdb does if recognise who
the current user is and assign the appropriate global
variables and permissions.
Click to expand...

AIUI ADPs would utilize the security on the server, not anything built into
Access.
I would need to do something similar for the adp (
including a logon password)

Also what happens to the permissions held on the access
workgroup file??
Click to expand...

See above.
 
Thanks Rick --

Currently - I have two versions an mdb to access and a
adp,
I have thought of going with the mdb to SQL but the work
seems prohibitve.

The adp I am happy with apart from two things - firstly
how do I recreate the CurrentUser function to set my
permissions? Second my db lets users see records in
continuous forms which are not editable (this is to be
the only place I use strored procedures as a
recordsource)- they have to select a record and then open
another form to edit a record - which uses the code
Set wR = New ADODB.Recordset
Set cn = Application.CurrentProject.Connection
wR.Open wSQL, cn, adOpenForwardOnly, adLockOptimistic
to get its data.
In pure access this change is passed back to the
continous form automatically, with the adp it is not. I
could requery - but then I move of the record I was on! (
should i do this and use bookmarks or something).

All of the application seems to run far quicker as an adp.

Thanks for the help. Paul
 
In pure access this change is passed back to the
continous form automatically, with the adp it is not. I
could requery - but then I move of the record I was on! (
should i do this and use bookmarks or something).
Click to expand...

Try using me.refresh. It will update the current records, but not loose your
spot.

Also, you can't use bookmarks when using me.Requery. When you re-query, all
bookmarks are now invalid because a requery causes the recordset to be
re-loaded from scratch.

So, try a me.Refresh.
 
Thanks superb answer
-----Original Message-----

Try using me.refresh. It will update the current records, but not loose your
spot.

Also, you can't use bookmarks when using me.Requery. When you re-query, all
bookmarks are now invalid because a requery causes the recordset to be
re-loaded from scratch.

So, try a me.Refresh.


--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.attcanada.net/~kallal.msn



.
Click to expand...
 
Back
Top