Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Manually split a database into a frontend - backend
How To
It is very simple how to BEST split the database. (please DON'T use the Database Splitter!!!!!!!!!!!!!- some people have gotten into trouble when their electricity was not stable, because the Splitter is doing a Cut and Paste)
FIRST, LAST, and IN BETWEEN RULE WITH ACCESS: ALWAYS backup your databases at least once a day, preferably after 2 or 3 hours of heavy data input (especially true if you are using the switchboard).
BEFORE YOU BEGIN, YOU SHOULD USE THE CODE IN THIS FAQ TO PRINT ALL THE TABLES AND THE TOTAL NUMBER OF RECORDS IN EACH TABLE. (you can modify the code to print all the field names too)
http://www.tek-tips.com/faqs.cfm?spid=700&sfid=4310 (GetTableInfo procedure)
This technique is the safest because if your network should fail while doing the Database Splitter, you could lose LOTS of data.
1) Create a new database with "DB" appended to the name of the original, e.g., "FixedAssets" is original, so backend is "Fixed AssetsDB" as your new backend MDB.
2) Enter the original database, make sure that everyone else is out, then run the GetTableInfo procedure and print out the results.
3) After printing, do a Compact and Repair. Exit the database.
3) Open the new database, and click on Tables.
4) Click on File, Get External Data, Import.
5) Navigate to your original database (e..g, FixedAssets), and click Select All. (you are essentially doing a Copy and Paste, so the original tables are still in your main database).
6) Using your GetTableInfo printout, open each table and insure that the number of records imported is correct. (time consuming, but worth it)
7) You are now able to enter the main database, and delete all the tables. (this might be scary, but you do have a backup of the database, don't you?)
8) This is a good time to do another Compact and Repair.
9) Click on Tables, then click on File, Get External Data. This time you will select Link Tables, then click on Select All.
10) You will now see all the tables in your original database with arrows before the table name, indicating they are linked.
HTH, Randy Smith
rsmith@cta.org
![[pc2]](/data/assets/smilies/pc2.gif "[pc2] [pc2]")
FIRST, LAST, and IN BETWEEN RULE WITH ACCESS: ALWAYS backup your databases at least once a day, preferably after 2 or 3 hours of heavy data input (especially true if you are using the switchboard).
BEFORE YOU BEGIN, YOU SHOULD USE THE CODE IN THIS FAQ TO PRINT ALL THE TABLES AND THE TOTAL NUMBER OF RECORDS IN EACH TABLE. (you can modify the code to print all the field names too)
http://www.tek-tips.com/faqs.cfm?spid=700&sfid=4310 (GetTableInfo procedure)
This technique is the safest because if your network should fail while doing the Database Splitter, you could lose LOTS of data.
1) Create a new database with "DB" appended to the name of the original, e.g., "FixedAssets" is original, so backend is "Fixed AssetsDB" as your new backend MDB.
2) Enter the original database, make sure that everyone else is out, then run the GetTableInfo procedure and print out the results.
3) After printing, do a Compact and Repair. Exit the database.
3) Open the new database, and click on Tables.
4) Click on File, Get External Data, Import.
5) Navigate to your original database (e..g, FixedAssets), and click Select All. (you are essentially doing a Copy and Paste, so the original tables are still in your main database).
6) Using your GetTableInfo printout, open each table and insure that the number of records imported is correct. (time consuming, but worth it)
7) You are now able to enter the main database, and delete all the tables. (this might be scary, but you do have a backup of the database, don't you?)
8) This is a good time to do another Compact and Repair.
9) Click on Tables, then click on File, Get External Data. This time you will select Link Tables, then click on Select All.
10) You will now see all the tables in your original database with arrows before the table name, indicating they are linked.
HTH, Randy Smith
rsmith@cta.org