make query temp table

[slimjen1]

All using access 2010. I have a multiuser database that I feel would benefit from splitting into a backend with multiple user frontends. My problem is that there are tables from make table queries processed every two weeks that all users need access to. As far as I know; you can not put a table in the backend that you will delete and remake or a temporary table and link to it in the front end. Is there any other way I would be able to split the database and have temporary tables linked from backend to frontend that I am not aware of? Is there another way to creating temporary tables and tables from make queries? This database has 9 users and counting and really needs to be split. really need help on this

 

[pr2-eugin]

slimjen1, you can use MAKE Table query to add to an external Database.. Then use the connect method to connect to the table in that database..

DISCLAIMER: This is only a suggestion. I have not performed this sort of operation before. So I am not saying this is the only way.

 

[RainLover]

Is there another way to creating temporary tables and tables from make queries? This database has 9 users and counting and really needs to be split. really need help on this

You are talking Temp Tables and a Split End.

Do you what both? What do you have now.

I am not sure what your eventual objective is.

 

[Galaxiom]

Instead of Make Table you could have a database with the table structures already in place and linked. Then use Append queries to add records.

Copy a new temp database as required and delete it when you have finished.

 

[slimjen1]

Thanks for all replies. I apologize I wasn’t clear. My backend contains all the tables. In my frontend; the tables are linked from the backend. Also in the frontend are the queries. I have two make table queries that are run by a user approx. every two weeks. Also there is some import code to import a table from another database. The previous table is always deleted. This is done because the owner of the other database does not want to risk any changes to the data by linking from it. These tables never get transferred to the backend. Because the user will run these processes from their front end if I split it, I was wondering how the tables could be transferred to the backend and links refresh the next time other users open their front end so everyone could still work on the same table and data. I know I can make the make table query to put the table in the back in by choosing another database in the popup but how do I have the new table always linked for all other users. With the imported table; I would just redirect to the backend instead of the front end. But again; how do I use vba to link tables that are not already linked and refresh the ones that are?? Thanks for your help.

 

[pr2-eugin]

I second Galaxiom's suggestion.. Create a permanent Backend table and link to it.. Instead of using Make table Query use APPEND.. This will avoid complications to your problem..

 

[slimjen1]

Noted. Ok how do I refresh links programmatically to make sure all users have the latest data each time they open their frontend. Thanks

 

[pr2-eugin]

That is the beauty of this method.. Since the link is already established, you do not need any method or refresh/relink the link.. The table is ready to be accessed..

Every user will be ADDING records to the table on a bi-weekly basis.. That's it..

 

[RainLover]

The previous table is always deleted. This is done because the owner of the other database does not want to risk any changes to the data by linking from it.

If you delete the front end table you will loose the link. But if you just delete the data then you don't have a problem.

Sorry but I was just not sure if your problem is covered.

With both Paul and Galaxiom helping I am sure it will be worked out if indeed something requires working out.

I think I am having one of those nights. I might watch TV.

 

[slimjen1]

Thanks all for replying and making suggestions. I will try in a test database and post back when Im done. Again thanks

 

[slimjen1]

Quick question. Can I delete the data and add new data in the append query? I’d like to prevent the user from having to do two steps. Something like RunSQL Delete * from tblOne Select tbl….”(go on to create the append query).

 

[pr2-eugin]

Yes.. You can do that..

    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE * FROM yourTableName"
    DoCmd.OpenQuery "UpdateQueryName"
    DoCmd.SetWarnings True

 

[RainLover]

Yes.. You can do that..

    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE * FROM yourTableName"
    DoCmd.OpenQuery "UpdateQueryName"
    DoCmd.SetWarnings True

Paul

By turning off the warnings you do not know if the queries are run successfully. They might not run at all.

 

[slimjen1]

Sorry if i wasn't clear. If the user wants to run the from their object window, how would I set the query to do both or can I? I guess I didn't mention this would not be an .accde to prevent them from creating and running their own queries. thks

 

[slimjen1]

Thanks to all for your help. I followed the suggestion of converting the make queries to append queries. I also created a form so the user can run the queries any time they want. Again thank you.