Access 2007: File Size Stays The Same After Deleting Records

Problem:  An Access database file has gotten large, so you decide to delete many records to reduce the file size.  After doing so, however, the file size stays the same.

Solution:  Compact and repair the database.

1)  Go to the Office button in the upper-left.
2)  Go to Manage and select "Compact and Repair Database".

Your database will now be a smaller size, sometimes even if you didn't delete any records. 

To make this automatic:

1)  Go to the Office button in the upper-left and click the "Access Options" button.
2)  Select "Current Database" from the left-hand menu.
3)  Check the box "Compact On Close" and click OK.

Access: Auto-Populate Fields in a Form Based on ComboBox Selection

This post outlines how to automatically fill other fields in a form based on a selection made from a ComboBox.

1)  While in Design View, select the ComboBox from which you will use to fill in the rest of the fields.  All this data must exist in the same table for this method to work.

2)  On the Property Sheet, select the Data tab and click the "..." button next to Row Source.  The "Show Table" dialog should appear asking you which table to select from.  Choose the apporiate table.

3)  Drag and drop the column headers that will be used for filling in the data and save the query.

4)  Go to the Format tab in the Property Sheet.  Change the Column Count to the number of columns you selected in step 3.  Change the Column Widths to be 1 for the column you want to be selectable from the drop down and 0 for each column you want hidden.  For example, I want my first row to be the list used in the Combobox, but I listed two additional columns, so my Column Widths will look like this:  1";0";0".

The 0 values are just making the data available to use when we auto-fill later on.

5)  Go to the Event tab and click on the "..." button for "After Update" and select Code Builder.  This will bring up the Microsoft Visual Basic interface.

6)  You shold see a section beginning with "Private Sub COMBOBOX1_AfterUpdate()". Below that and before "End Sub", type the following:


Me.[TEXT BOX] = Me.[COMBOBOX1].Column(1)
 
"TEXT BOX" is the label for the box you wish to fill automatically fill.
"COMBOX1" is the name of the combobox where the selection was made to fill the rest of the boxes.
"Column(1)" indicates which column from the hidden areas of COMBOBOX to fill this box with.  Note that the column counts begins at 0, so above the 1 indicates the second column.
 
To continue, you could add another text box that automatically gets filled by typing the following:
 
Me.[TEXT BOX2] = Me.[COMBOBOX1].Column(2)
 
7)  Go back to Form View and try it out.  I may add some screen shots at a later time, but in the meantime, please post comment with questions for clarity.
 
An advantage of using this method is that it frees up the Control Source field in the Property Sheet so that any data entered and automatically filled can still be stored to a table for other reports.

Access 2007: The Database Has Been Placed in a State by User on Machine That Prevents It From Being Opened or Locked

Problem:  You get the message "The database has been placed in a state by user on machine that prevents it from being opened or locked" when you try to open a shared database over a network.  The database can only be opened by one person while the others receive the above message.

Solution:  The database should automatically open in shared mode, but network security settings may interfere with this.  Try one the following:

1)  Does the first person opening the database see a notification at the top that warns "Security Warning: Certain content in the database has been disabled".  If so, then click on the "Options..." button, select "Enable this content" and click OK.  The notifications should disappear and others should now be able to open the database, but should also Enable the content in the same manner.

To fix this in a more-permanent manner, from the "Options..." button given in the warning, click on the "Open the Trust Center" link at the bottom of the window the appears.  From within the Trust Center window, go to Macro Settings (on the left) and select "Enable all macros".  From now on the security warning should not appear.

2) If you do not see a security warning as describe above, the security settings could still be a problem and Access just isn't notifying you.  In Access, click the Office button in the upper-left corner, select "Access Options", select "Trust Center" from the left menu, and then click the "Trust Center Settings..." button.  From the Trust Center, select "Macro Settings" from the left menu and choose "Enable all macros" from the options.
3)  Create a shortcut to the database by right-clicking the Access file, selecting "Send to >" and choose your Desktop.  From now on, open the database from the shortcut on your desktop.  Solutions #1 and #2 may still apply to this setup.