Microsoft Access Tips
- My Access 2000 database won't recognize "Dim dbs As Database"
- Access 2000 defaults to using the Activex Data Objects (ADO) library for data access. Access 97 defaulted to the Data Access Objects (DAO) library. You can still use either in A2K, but you must set a reference:
- Open any Module
- Click on Tools - References
- Scroll down the list and find "Microsoft 3.xx Object Library" where "xx" is a number
- Check the box next to this listing, then exit the Reference dialog.
You're now ready to use BOTH data access methods - DAO and ADO - in your code. However, you MUST "tell" Access which one you'll use:
Dim dbs As DAO.Database
Dim rst As ADODB.Recordset
This is known as "disambigulating" (yeah, it's a real word!!) your references. You are basically telling Access "I want to open a DAO database object named dbs". Also, in the references checklist you opened earlier, make sure you set your preferred method of data access HIGHER on the list than the one you'll use less. This will speed things up slightly, and it will inevitably result in less headaches for you.
- Simple functions like Left and Right have stopped working
- You're probably missing a reference:
- Open any Module
- Click on Tools - References
- Are any marked "MISSING"? If so, uncheck them, navigate OUT of the dialog box, then reopen the dialog box and re-check the references you previously unchecked. Note: you may wish to write down the EXACT names of the references you uncheck BEFORE unchecking them.
- I've set my listbox for MultiSelect, now how do I use the values?
- You iterate through the collection using the ItemsSelected property:
Dim var As Variant
Dim str As String
For Each var In Me.lstTables.ItemsSelected
str = str & Me.lstTables.Column(0, var) & vbCrLf
MsgBox "You selected the following:" & vbCrLf & vbCrLf & str
The variable str now contains the value in the FIRST column of your listbox for each item selected. How you use these values is up to you. If your listbox is multi-column, then substitute the appropriate column number for 0 in the code above.
- I've got two comboboxes and I want to synchronize them.
- There are two methods for doing this. The first involves setting the .RowSource of your second combo to filter the content based on the value selected in the first combo:
In the Properties dialog for Combo2, set the .RowSource to something like this: "SELECT lngVehicleMakeID, strVehicleMake FROM tblMakes WHERE lngVechicleMakeID=" & Me.Combo1.Column(0)
I prefer, however, to set the .Rowsource in the AfteUpdate event of my first combo. This avoids potentially out of sync comboboxes, and avoids the problems associated with enabling/disabling the combos based on user input:
Me.Combo2.Rowsource="SELECT lngVehicleMakeID, strVehicleMake FROM tblMakes WHERE lngVechicleMakeID=" & Me.Combo1.Column(0)
- I set up security on my database, but it doesn't seem to be working
- You probably didn't set up your security properly. Access security can be a tricky road to navigate; even experienced users occasionally get tripped up when dealing with some of the intricacies of Access security. The single best thing you can do is familiarize yourself with the security model. Download and read-read-read the Access Security FAQs. Follow the instructions to the letter, and you'll have a properly secured Access application.
The most common issues that crop up in the newsgroups are these:
- Forgetting to remove permissions from the Users Group. You cannot remove the Users group, but you should remove all permissions from this group
- Not joining the proper workgroup. User passwords and permissions are stored in the Workgroup file (the .mdw file) you created when you implemented security. To use your database, you must join that workgroup. This is normally done via a desktop shortcut:
"Path to MSAccess.exe" "Path to your database" /wrkgrp "Path to your workgroup file"
- Setting permissions on the folder where the database resides. While this may be a necessity for your particular work environment, setting folder permissions will not secure you database.
One other tidbit: Posters often complain of not being able to gain "exclusive" access to their database (usually when trying to make design changes). This is often caused by a stray ".lbd" file. An .ldb file is a "locking" file that Access creates and destroys when users log in and out. Occasionally, a network glitch (or a user not exiting Access properly) will cause you database to close without deleting this file. When this happens, you can be locked out of your database (and other users can experience problems as well). To rememdy this, find and delete the .ldb file (it's usually on the workstation) and figure out what caused the problem and correct it!!
- My AutoNumber fields keep skipping numbers!
- This is by design. The Autonumber field is a guaranteed unique Long Integer value; it's purpose is to simply make sure that all records in your table are unique. Autonumber fields should rarely be exposed to users. They can get out of sequence (at least according to humans) and most users don't like that. When they see a gap, they believe they've done something wrong. A better solution is to provide your own Numbering scheme that mimics some real-world value. For example, a new Customer Number may be a combination of the first 3 letters of the customer's last name (for example, "Smith" would become "SMI"), the type of customer (for example, "Residential" would become "RES"), and a sequence number (a number generated by the database to guarantee uniqueness ... in othe words, the Autonumber!!!). This way, in a table where the last Autonumber is 986, a Residential customer with the last name of Smith would be assigned this value: SMI-RES-987. This is often more "readable" than a standard autonumber (it has some "value" to the user, as it tells them what type of customer, and gives them a clue about their last name), and the number at the end has much less "value", since users read left-to-right. There are many other numbering schemes out there; Google on "ms access autonumber" for more info than you care to read!!
- Dealing with subforms
- Subforms are the best and worst part of Access. With subforms, you can easily and quickly make detailed master/child records available to the user. Interacting with them, however, can be a pain.
First, you must understand that a subform is (a) a Control on the main (parent) form and (b) a true Access Form. After adding a subform control to your main form, you'll notice only two events exposed: Enter and Exit. However, once you are on the subform, all the events associated with a form are avaiable for your use. Referring to a subform is somewhat tricky. Since a subform is a Control, you must access the parent form's control collection BEFORE referring to ANY portion of the subform:
The syntax is convoluted, and could be shortened somewhat ... however, I've found this syntax to be bulletproof and so have never had need to change it.