3
   

when I compact & repair an access database I lose all the tables

 
 
Sun 19 Jul, 2015 02:19 am
when I compact & repair an access database I lose all the tables and just end up with a msyscompacterror table. The error code is 1907 something about me not having the necessary permissions to use the tables. The database is very simple - 5 tables but it is large 1.8GB....thus the reason for the compacting.
  • Topic Stats
  • Top Replies
  • Link to this Topic
Type: Question • Score: 3 • Views: 1,524 • Replies: 1
Topic Closed
No top replies

 
jespah
 
  2  
Sun 19 Jul, 2015 06:34 am
@Johngibon,
Are you the owner of the database? It's been a while since I used MS Access, but there should be permissions.

Another idea is that if the database is this large, then the tables have got to be enormous. That's not a great use of Access, or of databases. If your tables have too many rows (I recall Access used to vomit at 1 M rows but you might want to be on the safe side and halve that as your max), then split by date or account # or name on account as A - M and N - Z or whatever is a logical way to split the information. You then run a query to grab the first half (the top half) of the database, check the query to be sure the data is okay, then run a make table query. Do the same with the bottom half. Then you should be able to delete the larger parent table and just keep the two smaller tables. When you need to run a query, you will need to account for both tables' worth of data, often by running a query on the first one, saving the results as a temp table and then running an append query to add the results of the second to your temp table.

If the number of rows is fine, then it's the number of columns. Using a primary key (a unique, nonrepeating key), split the tables by running a query first to pull out the primary key plus half of the columns, then run a make table query to make that as a table. Do the same for the other half of the columns, making sure that they also get the primary key. Run the query, check to see if it's okay and then run a make table query. You should be able to delete the huge parent table and just keep the two fraternal twin smaller tables. Then, when you need to run a query, it's just a straightforward even join between the two newer, smaller tables.

Do yourself a favor and create a new Access database with either solution you choose, as that will assure that you are the file owner and you won't lose the original data in case anything goes caca.

Like I said, it's been a while since I used MS Access, but these ideas should work.
0 Replies
 
 

Related Topics

 
  1. Forums
  2. » when I compact & repair an access database I lose all the tables
Copyright © 2019 MadLab, LLC :: Terms of Service :: Privacy Policy :: Page generated in 0.03 seconds on 10/15/2019 at 12:37:25