Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
juno
Starting Member
11 Posts |
 Posted - 2003-09-26 : 18:03:57
|
| My file size is getting larger and larger because of all the Forms and Reports. I was thinking, would it be a good idea to segregate the Interface stuff from the Database itself. What do you all think? Has anyone try doing something like that, and is it ease?At this point the size is about 900KB and that’s without the Database (I haven’t started putting the data yet) |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-09-26 : 19:44:31
|
Pffffffffft, when you start dealing with 1 GB Access databases, then you can complain about too big.  Seriously, 900 KB is nothing, even for Access, but yes you can alleviate some of the bloat and improve performance a bit by using linked tables in Access. Put the data in its own MDB file with no forms or reports. Then create the front end database and link all (or as many as you need) of the tables in the back-end database. This should help concurrency a little bit because the data will not be stored in the same DB as the interface and reports.Also, VERY IMPORTANT, you need to REGULARLY compact and repair both databases, especially the front end database, ESPECIALLY if you make lots of changes to reports and forms. Access makes entire copies of them whenever you save them, and the older versions are not removed unless you compact the database (even if you delete a form or report). Making lots of changes to a complicated report can very quickly add tens or hundreds of megabytes to an MDB file.I just recently compacted a front end database from 760 MB to 14 MB.  And no, that's not unusual either, unfortunately. |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2003-09-27 : 02:12:28
|
| And of course another benefit to splitting your front-end from your data tables is that it's easier to install updates to the system after it's been put into production. If you haven't changed the table structure, you can just copy in your new front-end mdb and re-link the tables. (You do have a separate copy that you do your development and testing in, don't you?)------------------------------------------------------The more you know, the more you know you don't know. |
|
|
|
juno
Starting Member
11 Posts |
Posted - 2003-09-29 : 18:59:35
|
quote:
Originally posted by AjarnMark
....You do have a separate copy that you do your development and testing in, don't you?.....
Yes I do, but I am still at the development stage of the database.I tried compacting the file and , that was cool .Thanks everyone that was very helpful. |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-09-30 : 13:42:42
|
| Backup, Compact, and Repair often. Be sure to make backups. I got burned back in the day when I did a compact or repair and it killed the DB.Did I mention do backups??Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-09-30 : 14:20:11
|
quote:
Originally posted by MichaelP
Backup, Compact, and Repair often. Be sure to make backups. I got burned back in the day when I did a compact or repair and it killed the DB.Did I mention do backups??Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
Did you try and compact onto itself?NEVER do this. Always make a new one.I traditionally have 3 folders.> Development (where the work is done)> Backup (Where the previous release is stored)> Compress (Where I compress in to so I don't have to change the name> Development has archive folders that store each previous releaseWhen development is done, the app is reparied and compacted in to the compress folder.IF all goes well, the back app is archived, and the one in the compressed folder is copied to the backup.A bat script is run to copy the code to each of the desktops....This script is run periodically to provide the users with a clean copy.You'll notice some users, if Access is not repaired and compacted will get HUGE...Don't know why...maybe because it's likes to roll around in the mud and squeal....or is that sequeeeeeeeeel....Good luck, and it's already past time to upgrade to SQL ServerBrett8-)SELECT @@POST FROM Brain ORDER BY NewId()That's correct! It's an AlphaNumeric! |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-09-30 : 14:28:09
|
| Yeah, I think that's what I did. I compacted the Database onto itself. I was young and dumb then, but now I know SQL.<Yoda>Access, path to the Dark Side it is. Corrupt databases it does.</Yoda>Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
|
|
|
|
|
Access file size getting to big