Access Cookbook - Ken Getz [104]
Although you have no direct control over the placement of the common dialogs when they pop up, the choice of the parent window can affect the location. If you pass 0, Application.hWndAccessApp, or a normal form's hWnd property for the hWnd argument to acbCommonFileOpenSave (or just don't send a value, so it uses the default value), the dialog will appear in the upper-left corner of the Access MDI client window. If, on the other hand, you pass it the hWnd property of a pop-up form, Windows will place the dialog in the upper-left corner of that pop-up form even if the form is not visible. Therefore, for complete control over the placement of the dialog, create a form, set its PopUp property to True, and use that form to place the dialog.
Finally, remember that these dialogs don't actually do anything—they just supply you with the names of files. It's up to your application code to open or save the requested files.
See Also
For more information on working with the Windows API, see Chapter 11.
4.9. Clean Test Data out of a Database When You're Ready to Ship It
Problem
You're finished designing and building a database; it's ready to ship to your client. Before they can use it, you need to remove the artificial data you've entered, without destroying permanent lookup tables. Is there a simple way to do this without running into referential-integrity problems?
Solution
One solution is to open every data table in datasheet view, select all the records, press the Delete key, and confirm the deletion. However, there are three problems with this simple method:
You have to open tables in a particular order (i.e., tables on the many side of a many-to-one relationship before their related one-side tables).
You have to remember which tables contain test data and which ones contain production data.
The task is tedious and repetitive.
Instead of clearing out your test data by hand, you can write a general-purpose routine that uses a table of tables and a simple SQL statement to remove only the test data, in the correct order.
Open 04-09.MDB and view the tables in the database container. Open the tblFood table and try to delete some records. You'll get a referential-integrity error, because there are related records in txrefFoodRestaurant. Figure 4-17 shows the relationships set up for the sample database. Now open frmDemo and click on the Clear button to remove all the test data from the database without any manual intervention.
Figure 4-17. Relationships in the sample database
To implement this technique in your own database, follow these steps:
Import the table zstblDeleteOrder (structure only, without data) into your own database, or create a new table with the fields shown in Table 4-4.
Table 4-4. Structure of zstblDeleteOrder
Field name
Data type
Field size
Properties
Order
Number
Integer
PrimaryKey
TableName
Text
Import the module zsbasMaintain into your database, or create a new module with the single function shown here:
Public Function acbClearData( ) As Boolean
' Remove all data from tables specified in zstblDeleteOrder.
' Data is removed in the order specified to avoid
' referential-integrity violations.
On Error GoTo HandleErr
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb( )
Set rst = db.OpenRecordset("zstblDeleteOrder", dbOpenSnapshot)
Do Until rst.EOF
db.Execute "DELETE * FROM " & rst("TableName")
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
acbClearData = True
ExitHere:
Exit Function
HandleErr:
acbClearData = False
MsgBox "Error " & Err & ": " & Err.Description, , "acbClearData( )"
Resume ExitHere
End Function
Open zstblDeleteOrder in datasheet view and add one record for each table you want to clear out before shipping. These tables must be listed in the order in which you want them cleared. Assign each table a unique order number, with the lowest number belonging to the first table