How to create a temporary table in access using a query

  • Thread starter Thread starter Al
  • Start date Start date
A

Al

Hi everyone,

I have a multi-user access db which has some linked tables to a SQL Server
and by running some VBA code, I'm trying to get some data from multiple
tables and put it into a temp file which will then be used to create and
display a report.

What the user basically does is that he/she enters dates into a form and
then the query runs, gathers the data from those muliple tables and puts out
a report. After the user closes the form or exits Access, I want this temp
table to be gone, so if the user runs the application once again, then the
same query has to be run again. I was thinking of having some fixed table so
any user who runs the application would fill it up with their query results,
but then there's the problem of two users running the application at the
same time, which would produce an inaccurate report.

So my question is, how would you handle multiple temporary tables being
created by multiple users in Access? Can I create temp tables in Access?

Thanks to all the contributors.

Al

(I posted this question to the queries forum as well)
 
I have a multi-user access db which has some linked tables to a SQL Server
and by running some VBA code, I'm trying to get some data from multiple
tables and put it into a temp file which will then be used to create and
display a report.

What the user basically does is that he/she enters dates into a form and
then the query runs, gathers the data from those muliple tables and puts out
a report. After the user closes the form or exits Access, I want this temp
table to be gone, so if the user runs the application once again, then the
same query has to be run again. I was thinking of having some fixed table so
any user who runs the application would fill it up with their query results,
but then there's the problem of two users running the application at the
same time, which would produce an inaccurate report.
Click to expand...

Is your application split? Multiple users shouldn't be running the same copy of
the application. You should split the database, if you haven't already, into a
back-end file for the data and a front-end file for the application (forms,
queries, code, reports, etc.) A copy of the front-end file, which is linked to
the tables in the shared back-end data file, is provided to each user on their
own workstation. See:

Splitting your Microsoft Access MDB into a front end and back end
http://www.granite.ab.ca/access/splitapp.htm
So my question is, how would you handle multiple temporary tables being
created by multiple users in Access? Can I create temp tables in Access?
Click to expand...

Yes, but see my remark above regarding splitting the database. You might even
consider using a temporary database in which your temporary tables are created
to help reduce front-end bloating:

http://www.granite.ab.ca/access/temptables.htm
 
Al said:
I have a multi-user access db which has some linked tables to a SQL Server
and by running some VBA code, I'm trying to get some data from multiple
tables and put it into a temp file which will then be used to create and
display a report.

What the user basically does is that he/she enters dates into a form and
then the query runs, gathers the data from those muliple tables and puts out
a report. After the user closes the form or exits Access, I want this temp
table to be gone, so if the user runs the application once again, then the
same query has to be run again. I was thinking of having some fixed table so
any user who runs the application would fill it up with their query results,
but then there's the problem of two users running the application at the
same time, which would produce an inaccurate report.

So my question is, how would you handle multiple temporary tables being
created by multiple users in Access? Can I create temp tables in Access?
Click to expand...

Tony Toews has a good discussion and examples of temporary
tables at http://www.granite.ab.ca/access/temptables.htm
 
Back
Top