Temporary tables and calling subroutines

[Mark]

I have two questions. In the past, I've created several
front/back end databases that use temporary tables to hold
some of the data manipulation. In the past, I ran into
the problem of multiple users accessing the data, causing
various errors and problems.

I resolved this by placing the temporary tables in the
front end so there wasn't a sharing problem and then made
sure the routines cleaned up the temp tables and then did
a compact/repair when the database is closed. This can be
problematic if the user has multiple sessions of the front
end open though.

What I would like to do would be to create temporary files
in the Windows TEMP directory and place the temp tables
there. That way each session would have a

Could someone point me to an example that show me how to
do the coding for something like this?

The second question regards calling subroutines. I know
how to call a subroutine in normal VB coding. The kink is
that I want to read a record from a table that contains
the name of a subroutine to run. The code to loop through
a table is easy, but how do you make the call to the
subroutine itself?

Thank you in advance.

Mark

 

[Sandra Daigle]

Here's a link to a web site created by MVP Tony Toews.
http://www.granite.ab.ca/access/temptables.htm

This should give you all the info you need to figure out how to create the
temporary table in a temporary database - thus eliminating the bloating that
occurs when done inside the front-end mdb.

Regarding the indirect call to a subroutine you might try using the Eval
function - I haven't actually tried it but it might work for you.

 

[Mark]

Thanks Sandra, I'll check them out.

Have a great day.

Mark

-----Original Message-----
Here's a link to a web site created by MVP Tony Toews.
http://www.granite.ab.ca/access/temptables.htm

This should give you all the info you need to figure out how to create the
temporary table in a temporary database - thus eliminating the bloating that
occurs when done inside the front-end mdb.

Regarding the indirect call to a subroutine you might try using the Eval
function - I haven't actually tried it but it might work for you.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

I have two questions. In the past, I've created several
front/back end databases that use temporary tables to hold
some of the data manipulation. In the past, I ran into
the problem of multiple users accessing the data, causing
various errors and problems.

I resolved this by placing the temporary tables in the
front end so there wasn't a sharing problem and then made
sure the routines cleaned up the temp tables and then did
a compact/repair when the database is closed. This can be
problematic if the user has multiple sessions of the front
end open though.

What I would like to do would be to create temporary files
in the Windows TEMP directory and place the temp tables
there. That way each session would have a

Could someone point me to an example that show me how to
do the coding for something like this?

The second question regards calling subroutines. I know
how to call a subroutine in normal VB coding. The kink is
that I want to read a record from a table that contains
the name of a subroutine to run. The code to loop through
a table is easy, but how do you make the call to the
subroutine itself?

Thank you in advance.

Mark

.

 

[WSF]

Hello Mark,
In my case I am using temp tables in a temp mdb created each session
(usually a day's activity) on the users C:\drive.
My code first checks for the existence of a "TempReports" directory, and if
it does not exist it creates it.
I elected to do this so that if there were issues it was a simple enough
place to look, rather than buried with the users rubbish in their Windows
temp directory.

I then ask if the previous session's temp mdb exists and if so kill it.
Then I create a new empty temp mdb to which the front end sends it's working
tables for that session. I am working in a multiple-user networked
environment.
I also, as part of the creation of the temp mdb, copy across to it any
tables that hold relatively static reference data to which my queries may
refer, re-linking them to the front end. This takes a few extra seconds -
the users are informed of progress so as not to be totally bored - but the
time savings later are significant. So the queries then cross-reference
tables in the local drive rather than through a busy network - and the
improved speed is noticeable. (these reference tables in my case are
generally updated overnight, but my code allows for the user to update
any/all of them at will if required (e.g. if advised of an update of data
therein).
One issue I have found is that as soon as Access mdb's are shared (two or
more) there is a significant degradation in linking and data extraction
speed. Localising as much of the reference data as possible overcomes that
issue.

That way my queries, the data crunching, only access through the network
what is the volatile or ever-changing data.
Clunky? It works well for me. I got a fair bit of inspiration and help from
Tony Toews' site, and this newsgroup in particular.
So my front end only holds code to create, assimilate and query data.
Any admin issues (like differentiating user functionality in my program,
logs etc) are held in another shared admin.mdb tucked away on the network.

I have no bloat issues with the front end.

Hope this helps

WSF

Thanks Sandra, I'll check them out.

Have a great day.

Mark

-----Original Message-----
Here's a link to a web site created by MVP Tony Toews.
http://www.granite.ab.ca/access/temptables.htm

This should give you all the info you need to figure out how to create the
temporary table in a temporary database - thus eliminating the bloating that
occurs when done inside the front-end mdb.

Regarding the indirect call to a subroutine you might try using the Eval
function - I haven't actually tried it but it might work for you.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

I have two questions. In the past, I've created several
front/back end databases that use temporary tables to hold
some of the data manipulation. In the past, I ran into
the problem of multiple users accessing the data, causing
various errors and problems.

I resolved this by placing the temporary tables in the
front end so there wasn't a sharing problem and then made
sure the routines cleaned up the temp tables and then did
a compact/repair when the database is closed. This can be
problematic if the user has multiple sessions of the front
end open though.

What I would like to do would be to create temporary files
in the Windows TEMP directory and place the temp tables
there. That way each session would have a

Could someone point me to an example that show me how to
do the coding for something like this?

The second question regards calling subroutines. I know
how to call a subroutine in normal VB coding. The kink is
that I want to read a record from a table that contains
the name of a subroutine to run. The code to loop through
a table is easy, but how do you make the call to the
subroutine itself?

Thank you in advance.

Mark

.