I am migrating an Access mdb which has the tables in one mdb on our
server and the mdb front end installed on each workstation with the
tables on the server linked to an adp with a sql server 2000 backend.
However, with the mdb I have used a local table to temporarily hold a
list to enable users in this multiuser environment to select items to
insert records into one of the linked tables. I thought I had the
solution when I read up about temporary tables (#table), which seemed
to offer the opportunity to create a unique table for each user.
However, although I have been able to create and view the contents of
my temporary table in Query Analyser, I do not seem to be able to
create a stored procedure to make this table the record source of my
adp form. In addition, does the temporary table last all the time the
user has the adp open, disappearing only when the adp is closed? I need
to be able to present a fresh record set to the use each time the form
is entered so that he/she can run down the items in the continuous form
ticking off the items they want to add to the normal table, so with
temporary tables I would need to drop and recreate the same #table for
that particular user each time the form is opened during the same
session. Can all this be done with temporary tables in an adp? Or is
there some better way of achieving my goal?