I put my database in my network and everytime I compact it, it always crashes/hangs. However, if I copy the database to my hard drive and compact it, it works fine. It also works slower if I run the the database from network. Does anyone know why these occur ?
when you work directly on the server, you cause memory leaks to it. Because it need to release more memory to send your local machine the intire database. If one or more users besides you, are working with the network, you will notice your aplication very slow, for example when you just close a form it takes an eternity to close. In this case when you're trying to compact the database the leak of memory causes bad conectvity, to your local machine, and your database crashes.
If you compact directly on the server machine, not from your computer you will see that no crashes ocurs. Try to automate the compactation, doing that your database compcts by itself.
Thanks for the help. How do I compact the database on the server machine ? How do I also automate it ? I am not that familiar with these issues. What is the good way to distribute shared database ? What I did was put the .mdb file on network drive so everyone can access it.
That's true, it becomes so slow and whenever it crashes, it creates a .mdb file called db1 and so on, like a duplicate.
if your number of users is less than 8, than you can replicate the master database, you have this option in the tools menu. The replication creates copys of the original database, and link the tables. If your n.º of users for example is 5, th replication creayes 5 replicas linked to the original database. After you replicate you should do the following:
-Move the replicas to the local machines that will work with the database.
-Move the original Database to a directory on the server
-On the replication option, choose synchronize, (I think it spels like that, because my Access is in Portuguese.), this will synchronize all Databases.
If the number of users is greater than 8 or 10 then is better upsize to SQL Server.
-Normally the replication changes your autonumber primary key, to random number, for the reason that, no duplication of primary key occurs. If you've got errors related to this issue, just change your relations or your primary keys.
-You also have a " troubleshooting" replication option on tools menu
-Your database must be designed to denie changes to records, only administrator, in this case you, can change, or else, everybody can change a record that has been edited without your permission.
-You must have ODBC drivers installed, you can see if it is, just go to control panel, click ODBC and search for "ms access databases"
-Other issues, you have good explanation in ms access help for replication.
concerning to automate compactation:
use "DoCmd.RunCommand acCmdCompactDatabase", based on a open forms OnOpen event procedure, comparing dates. For example:
make a table with a primary key field name="CompactDate"
use a form based on that table
use the control name="CompactDate"; datasource=CompactDate
put the value, 10/02/2002 this will be changed in the future
If me![CompactDate]=Date then
[CompactDate]=DateAdd("d",30,me![CompactDate]) ' This Adds 30 days to the first compactation day, the next compactation day will be next month
Thanks again for the comprehensive explanation. I might have to skip the replication at this moment and maybe pursue it later. In your explanation 'concerning to automate compactation', besides automating, does it also compact directly on the server machine so the crash will not come anymore (the one you told me earlier) ? So, if I just use my current method (put .mdb in network drive and no replication) + using your compacting code, there should be no error when compacting ? How do make the database faster when accessed from network (besides replica) ?
About the automation, I am thinking of everytime users finish using the database, the compact command should run because I have big queries, so it takes a lot of spaces.
R u Portuguese ? If yes, Forza LUIS FIGO and RUI COSTA
Here goes some tips for you to put your database functional and more speedy
-Your original database is in the server, right? Ok let it there.
-On your computer open a new database, select a blank one. Give the same name as the original plus your computer name, to distiguish them. (ex.: original database name Milan.mdb; your new database Milan_MyComputer.mdb)
-Click on File menu option and chose the option "Add external data...", should be the third option that appears on file option menu. Note, here you can import data from Word, Excell, Txt files or mdb files.
-Select "Link Tables",(I suppose it is the second option of two). Now it will appear a dialog box, for you to chose the directory and file. You will chose the database stored on the server. Click OK
-It will open a dialog box similar to the access witch contains the tables, querys, forms, etc...
-Click on the tab "Tables", select the tables you want to be linked. I suppose in your case you want all of them, then select all. Click Ok.
-Now you will see the same tables that are in the original database, with an arrow at the left side, this means they are linked.
-Now you need your forms and querys. Click again in file menu option, and select the option "Add External Data..." and then "Import".
-The same dialog box for chosing the file will appear, and then again you chose your original database.
-Now click on the tab querys, and select all, Click on the tab forms and select all, click on the tab reports and select all, and so on. (do not select the tables, because you already have them linked.). Click OK.
The process reach the end, and now you have the exact copy of your database, but linked. The only diference is that your querys run on your local machine accessing the data to the server, and not accessing all to the server.
This process is similar to do a replica, the replica is for a group of users send data at same time to the database, witch means synchronism. With this we can also send data at the same time, but it is more ineficient, probably lots of messages of the kind "Another user Has now edited the related record. do you want to change it?".
But if you want the database for one or two users add data and the rest for querying it, then this is a solution to resolve your problems of sleepy server.
Note: In the last reply i told you to use the function docmd.runcommand acCmdCompactDatabase, the problem is, that access needs to close the database, compact it and then delete the old database. Ok, the method i'll show you is not an automation or even close but from your computer, you can compact the database on the server without errors:
-on your database local machine, create a button and apply the code bellow or the other:
the problem is that i don't no how to delete the uncompacted database and rename the compacted one to the original name. In the end, the solution is not automated as i think it were, it is needed to delete the older and rename the new, so that your all database can work, because you can not reference linked tables to a milan1.mdb if they are linked to a milan.mdb.
Another Way and i think it is best, is to use the code that initialy i told you
Docmd.runcommand...., this will open a dialog box for you to chose the database you want to compact, ok, chose the one that are on the server and that's it.
Sorry, in this case i can't go any further yet, as i think i would. I just can't do as Access does, but i'll get it!!!
I am actually not sure about the server, whether that exists or not. I know I am gonna put that in network directory. So if I just use Compact Database, it will hang because of huge network transfer ?
Are you familiar with Security ? I am trying to attach my database to a workgroup so whenever users click on my database, it will ask user authorization. The only way I know is creating a shortcut to my database using this command : "msaccess.exe" "mydatabase" /wrkgrp "myworkgroup"
This works fine, but I have to hide the mydatabase in "secret" directory, so users don't click on it and bypass the security. At first, I used join workgroup, but it seems it asks for authorization to every database I open, not just a particular one. Any idea ? Will replica or the method you just told me (create copy etc) be in conflict with security ?
Thank you for answering a lot of my questions, this is my first time dealing with shared database as well as security. I used to make database only for remote one.
When i mention the server, i ment a machine that shares the database with the others. Of course it can be a computer in a network it doesn't mater, as long as it have enough memory and disk space available, chose the most recent machine to share your database.
You can try on your computer. Just create two databases and link them, in one put the tables that store the data, call it storage_database, in the other put your forms and querys. it will work. If it works on your computer, works in the network.
To secure your database, there are many ways, the most simple is to set a password for each databases, of it can be diferent. Just open your database in exclusive mode, you can do that, by opening access and then chosing open a database, in the dialog box you have a check box near the buttons, check it first and then click OK, go to the tools menu, chose security and then define password and thats it.
the other ways are more complex to explain now, is better for you to go see the help for security, is very well explicity, and easy to understand.
If you have other questions you can reply, because i'm gonna stay here a more time!
I already tried your suggestions (create new DB, link tables and import forms etc). It works. Before, when I run my query, since it is quite big, it really enlarges my mdb file. Now since I use link table etc, whenever I run query, where will the size increase, the original DB in the server or the copy one in local machine ? I observe, none of them had an increase in memory actually when I run query (which is good), but a bit confusing why.
I created a workgroup using wrkgadm.exe, remove admin user, create new administrator as well as creating users and groups with diferent privileges. The problem I am having is attaching the mdw with myDB. When I use join in wrkgadm.exe, it seems that it joins every mdb, so whenever I open mdb in my Access, it always asks for security. But I only want myDB to be attached to mdw, not all mdb. So, I created a shortcut with target "msaccess.exe myDB" /wrkgrp "myWorkGroup". The only problem is I have to hide myDB in a 'safe place' to ensure users don't click on it and bypass the security. Is this the only way to achieve it ? So, what should I do if I use multiple copies in local machines (just like the one you told me : link table etc), how does the security works then ? Where should I put the mdw ? Because each user will have different privileges.
When you say that the compacting, use the mdb in the server, that means whenever you run query etc or save data, it will only enlarge the mdb file in the server, not the mdb in local machine ? If no, do we need to compact the mdb in local machine ? I think using docmd.runcommand might not be feasible in my case because I need that to be fully automated, without user input at all. So, I should try the other method (DBEngine.CompactDatabase, kill and Name) and see how it goes.
Thanks again for the last reply, I think this discussion really increases my understanding of DB.
I have a question about myDB structure. My table has about 100 fields, so it's quite big. I create a form for data input, however, users also want to see the output results of the inputs in the form, e.g. from lookup tables or calculation etc, so I have to base my form's record source on query rather than table to ensure user's input-change will reflect in the output. Hence, I include the 100 fields in my query grid, as a result, it takes a lot of memory (2MB per run) + I have about 30 lookup tables to join. I know there is another way to do this, by using requery etc, but it really annoys me, and everytime it requeries, it jumps to the first record as well as top of the form. I ask around, they say, if I wanna show the update output result, I have to base my form on query. Any comment ? Thanks.
If you run the query on your computer, only the DataBase in your computer grows, not the one in the server, because your database is accessing data to from the server and then querys it on your computer.
I did not noticed that querying data makes the database grow, but if you're telling it must be truth.
Now, if you want to compact the database in the server, use your linked database, and in a form put the code dbengine.... in the OnClose event, comparing dates like the way i told you before and the code: "Docmd.Quit", wich makes your access window to close, then when the form closes, the code runs and the database in the server compacts and your access window close to.
Of course in the same form you can send compactation for all databases, spread over the network, or just put the same code in every database excluding the one that compacts the original.
Concerning to security, for now i do not work much with securying database, using administration and workgroups.
What i can tell you is: when making an import-linked database, think in the user that will work with it, and restrict your forms, you can do that by importing the necessary, set the options of the database for the user never sees the window that contains the forms and tables, and disable the right click, restrict the menu options to.
There is a wizard wizard add-in for access that you can download, i don't know if your acces version is 95, 97, 2000 or Xp, serach on http://www.Google.com, for Ms Access (your version) Add-ins, or Menu Builder.
This will help you make a menu for your project, and replaces the standard menu, and give a more professional look to your project.
I just tried putting the master DB in network and a copy one in my computer, and link them together. It works OK when accessing lookup tables because it only has 4 fields. However when accessing master table (100 fields), it becomes unaccceptably slow. Not sure how to get around this problem. I read in the book, replica is not effective when records modified by many users, because of resolving conflicts etc. How does synchronization works anyway, how does it resolve conflict when users change the same record, which change is taken ?
Since it seems that running query is the one that causes a lot of memory, do you think it's still necessary to compact the mdb in server since it only consists purely data ? I was thinking of only compacting mdb in local machine so I can also use 'Compact when Close' in Access option. Any comment ? The book says that when running query or something like that, Access reserves memory although it does not use it anymore. That's where Compacting comes into play. When I run docmd.runcommand... on Open event, it gives me error, that it cannot run while VB or macro runs.
Regarding security, If want to use linked databases, I guess I have to put mdw in every local machines. One of my user can read all tables, but cannot change anything, that's why I thought using workgroup is better and easier.