Dyanmic Table Names

[cable]

Some of our users run our programs in the following setup, one program on a server but multiple (though few) users using it.

Now this leads to problems where the program uses temp tables. I've sorted the problem for most cases ie when they are used in code or as a simple report query, but I've just come across a report based on multiple queries that use a temporary table. (the soltuion i've got is basically appending the username to the tmp table name)

I can't combine the queries into one, and no solution seems to present it self.

Can I write a query where the table names are varibles?

Is there another solution?

 

[neileg]

Two possible solutions spring to mind, but I haven't tried to do either, before.

1) Split the db into a front end and a back end. Hold the temp tables in the front end and give each user their own front end.

2) Build your queries as SQL strings. You can certainly use variables here.

Good luck

 

[cable]

Two possible solutions spring to mind, but I haven't tried to do either, before.

1) Split the db into a front end and a back end. Hold the temp tables in the front end and give each user their own front end.

2) Build your queries as SQL strings. You can certainly use variables here.

Good luck

1, wont work, they only have one copy of the program and the backend, these aren't normal tables remember, the contents gets deleted.

2, can I store the resulting query?

 

[neileg]

1, wont work, they only have one copy of the program and the backend, these aren't normal tables remember, the contents gets deleted.

I don't understand. Why can't you have one back end and 6 (or whatever) front ends. I know they aren't normal tables. I don't think there's a restriction on having tables in a front end, since the front end is a full Access application, albeit relying on linked tables in the back end for there data. If you don't know about splitting a databse, look this up first.

2, can I store the resulting query?
? You want to develop a dynamic query so you can change the table names, and then store it? Why? Is it the query you want to store or the results?

 

[cable]

I don't understand. Why can't you have one back end and 6 (or whatever) front ends. I know they aren't normal tables. I don't think there's a restriction on having tables in a front end, since the front end is a full Access application, albeit relying on linked tables in the back end for there data. If you don't know about splitting a databse, look this up first.

Normally this is exactly what we try to do, but a few customers don't 'want' to do this, it might also be that they are running on Citrix workstations or a similar setup

? You want to develop a dynamic query so you can change the table names, and then store it? Why? Is it the query you want to store or the results?

Ok one of the reports is based on a query which in turn is based on another query and a tmp table, that query is based on another. If it was just the last query then I can easily change the recordsource in the report open event but this isn't the case.

So when the report runs I need to somehow alter all the relevant queries to use a different table name.

 

[neileg]

Normally this is exactly what we try to do, but a few customers don't 'want' to do this, it might also be that they are running on Citrix workstations or a similar setup

You can still have multiple front ends on Citrix. If you want to fancy it up, build a mini application that all users log into, and link this to the relevant front ends.

Ok one of the reports is based on a query which in turn is based on another query and a tmp table, that query is based on another. If it was just the last query then I can easily change the recordsource in the report open event but this isn't the case.

So when the report runs I need to somehow alter all the relevant queries to use a different table name.

Sorry, you've just exceeded my experience/knowledge of Access. I think, however, we're in the realms of trying to get your solution to work, rather than re-examining the original problem.

 

[cable]

Thanks for your help, I think I will strongly suggest that they do use multiple front ends, thats as much as I can do.

Oh well worth a try...