"PeteCresswell" <Go**********@FatBelly.com> wrote in message
news:74**************************@posting.google.c om...
In another thread, somebody ventured the idea that this was possible
under A2k's ADP but they did not sound sure of it.
Anybody done it?
My agenda is to use DAO for work tables bound to forms.
Pete, we do this from time to time and haven't had any problems (touching
wood). It's great for batching updates back to the server. Here';s a couple
of things we do:
1. We always name the local jet file with a .dll extension, we've found
people tend to play around with these less. But of course it's really a jet
database, (.mdb) file.
2. Remember to kill the file on the way out. Also, on start-=up you need to
check if the file already exists. Now if it does exist, and if the form is
used for data entry, it might be that someone got half-way through and the
power went off. So you need some way to recover from this.
3. Also you need some home-grown locking mechanism on the server - so that
although you're not physically holding locks open, you can still cater for
the cases when two people want to edit the same data.
4. AFAIK you can use ADO or DAO recordsets to bind to the form, but I
believe that only in Access XP onwards are the ADO recordsets updateable. We
generally use DAO..
5. If you use DAO and the current workspace, (is that the correct term?)
don't close it when you exit, otherwise you can have problems the next time
you want to bind to the external database. We learnt this one the hard way.
6. You can create a design pattren for this, so that you can re-use the code
in subsequent forms. The logic is that you create an ADO recordset from the
sql server procedure that returns you records, and use this scema to
construct the tables and columns in the Jet database, then loop throught the
ADO recordset to populate it. I don't know if there's a faster way to
populate it but anyhow, that's what we do.