Code to open a table with a unique name

[projecttoday]

How could I create a temporary table with VBA code that has a name unique to the user? If I use the time as part of the table name it's possible 2 users could hit at exactly the same second.

Robert

 

[RoyVidar]

The operative question isn't how, but why.

I don't see any scenario where this should be necessary. Can't you rather tell us what you wish to achieve, then someone might assist you in finding the appropriate approach.

Roy-Vidar

 

[projecttoday]

That would be if the database is shared by multiple users on a network and you had a report that needs a temporary table and 2 of them were running it at the same time user no. 1 could make the table and user no. 2 could erase it and replace the data with his own.

 

[RoyVidar]

My first impression, from what you're saying, is that more than one user is sharing the same physical mdb file. This is a big no-no, and will bring you grief - it's only a matter of time. This is probably the most reliable way of ensuring database corruption, I'm afraid.

It should be split into a Back end (BE) containing only the tables, and a Front end (FE), which contains the rest (queries, forms, reports, code...), where you link to the back end tables (can be done through both a splitting wizard, or with the Get external data method from the File menu).

Then - should need like this arise, you can create (or have permanently) a temporary table in the FE (which is usually only containing queries, forms, reports, code...), and you have no such problem with more than one user, as each user will have their own FE file (on their desktop, application data...).

Temporary tables have a tendency of bloating the application, and some will advocate the method of having a separate temporary database locally (for instance the same place as the FE file), where one have, and link to, the temporary tables one need.

Again - splitting into a FE/BE setup is essential, among several reasons, to avoid database corruption. This is also why I posted the initial question.

Roy-Vidar

 

[projecttoday]

This is what I want: a split database with only one front end. I don't see the need for multiple front ends. Temporary tables should go on the backend with the other tables.

 

[PHV]

1) To avoid corruption have each user with a local FE
2) TEMP tables should be local for each user

 

[RoyVidar]

> I don't see the need for multiple front ends.

With concurrent usage, sooner or later you will, but then it might be to late - your decision, of course

> Temporary tables should go on the backend with the other tables.

No

Roy-Vidar