-
Notifications
You must be signed in to change notification settings - Fork 0
Northwind Database Tutorial
This tutorial will use the standard Northwind database provided by Microsoft to show you how to convert your database to use the Update Back-End code. You could then use this database to try out any additions that you want to use on your own system.
The two files provided in the zip file are North Wind.mdb and North Wind_be.mdb. This is the same demonstration database provided by Microsoft except that it has been split into front-end and back-end files and has also been converted to Access 2000 format, (actually the front-end file has two additional tables, a query and a module added to save you a bit of time later).
To test this demo first create a folder called UBE on the C: drive and copy the two files into it, this should ensure that the front-end will link to the back-end correctly. If you really need to use a different folder location then you should re-link the front-end and back-end using the Linked Table Manager when you first open the database. When you have the two files set up and confirmed that the database is working correctly you should then [make a copy of both files]{.underline} which will be used later to test the update facility.
The procedure to add the update code to this database will basically be the same procedure for your own database and is as follows :-
-
Open the front-end file (North Wind.mdb in this demo) and import all the database objects from the ubeUtility V1-2.mdb file, that is table ubeUpdate, forms ubeForm and ubeUpdating, macro AutoKeys (if you don't want to use a short cut key to open the Update form or you already have an AutoKeys macro you don't really need this one) and module ubeUpdateCode.
-
Open any code module and run the Compile option (Debug > Compile Northwind) to ensure that there are no duplicate procedure names or other errors, etc.
-
Open the ubeUpdateCode module. At the top of the code you will see this line :- Public Const gRefTable = "tblGeneral". If you want to use a different table name for the reference table then change it here but for this demo we will just leave it as tblGeneral. Exit the module code.
-
Now you need to add one line of code to your start up form or module to call the update code in the ubeUpdateCode module each time the database is opened and it will depend on what code is run when the database is started. It needs to run [before]{.underline} any other actions in your database but [after]{.underline} any automatic re-linking code which re-links your front-end to the tables in the back-end. In this North Wind database the Startup form is opened from Tools > Startup > Display Form/Page: and the On Open event of the Startup form runs the OpenStartup() procedure in the Startup module. So for this demo, open the Startup code module and near the top of the page there is this line of code:- On Error GoTo OpenStartup_Err Insert this line of code immediately after that line :- Call UpdateBackEndFile(False) Note that the user has the option to [not]{.underline} show the Startup form each time this database is opened by ticking a box on the form, you should make sure, however, that you do not provide a similar option on your own database because if this form is [not]{.underline} displayed, then the UpdateBackEndFile function is not run. I usually provide a switchboard form on start up and add the line of code to the Open event of the form. You could also use an AutoExec module to run the code. If you are using Access 2007 in .accdb mode you should also read the Access 2007 Variations section above.
-
Now open the ubeForm form. You can just double-click the name in the database window as normal or use the short cut key combinations (Ctrl + Shift + Z) provided by the AutoKeys macro (assuming that you imported it earlier). Note: I have found that, in Access 2000 anyway, this does not work immediately but if you open the AutoKeys macro in Design mode and save it again it works OK after that.
-
When you first attempt to open the ubeForm the code checks for the reference table in the back-end and when it does not find it, an error message is displayed, as shown in Fig 4 above. If you click No then no action is taken, if you click Yes then the table (tblGeneral or whatever name you chose) complete with the ubeVersion field, is added to the back-end file and then linked to the front-end file. The main (blank) Update Back End form is then displayed. You could start adding new fields, tables, etc to the back-end immediately but it would probably be advisable to exit the database at this stage, run it again and check that everything still works correctly and then send your client a copy of this front-end so that they can update their own back-end with the new reference table. See below for details of what the end user needs to do when they first receive this version of the front-end file.
That is all you need to do to prepare the database for back-end updates. You can now start adding new tables and/or fields as and when they are required.
When you send the new version of the front-end file to your client he will run the database and will immediately be shown the same error message shown in Fig 4 above. He should click on Yes which will add the reference table to his back-end file and then run the database normally. If you have already added some tables and/or fields to this initial version, then those updates will be executed as well.
Note that the situation regarding the front-end/back-end code is slightly different for your update and your client's update. When you opened the Update Back End form for the first time there was no reference table (tblGeneral) in your back-end file or a link to it in the front-end file, this generates an error 3265. However, when you send a copy of your front-end to the client, the front-end has a link to the reference table but the client's back-end file does not have the table itself, this will generate error 3078. The code handles both situations to provide the table and/or link but you may need to be aware of this if anything other situation should arise.
This section shows a few methods of adding tables and fields and changing property settings in the North Wind database, try them out yourself to see how easy it is to change the back-end.
Exercise 1. Add two new tables and link them together.
The current database has a Customer table with fields for a Contact Name and Contact Title. Let's suppose that any company could have multiple contact names, this means that we need another table to store the contacts and link it back to the Customer table. Let's also suppose that we would want to store a set of notes for each contact name, this means we need another table to store the notes and link it back to the contacts table. So we need to add a table for contacts, call it Contacts, and also a table for diary notes, call it DiaryNotes. We need to link the Customer table to the Contacts table as a 1 to Many relationship, we can use the CustomerID field which is the Primary Key field in the Customers table and we need to link the Contacts table to the DiaryNotes table, also a 1 to Many relationship.
When we have created the Contacts table we need to copy the contact names from the Customer table to the Contacts table and then we can delete the ContactName field in the Customer table. As the ContactTitle field and Phone field also 'belong' to the contact we will copy those over as well.
As mentioned earlier, there are two methods to create tables in the back-end and for this exercise it is better to first create the blank tables and then copy them into the back-end. To save you time I have already created two tables -- Contacts and DiaryNotes. If you open the tables you will see that the table Contacts has six fields and table DiaryNotes has four fields and neither has any records. We will use field ContactRef (AutoNumber) as the primary field to link to field ContactRef in table DiaryNotes.
Open the Update Back End form and enter the following into the first seven rows :-
Ref Action Table/Query Name Field Name Field Type Property Additional Data Field Description
1 Copy Table Contacts
2 Copy Table DiaryNotes
3 Set Relationship Customers CustomerID 1-n Casc Upd/Del Contacts CustomerID
4 Set Relationship Contacts ContactRef 1-n Casc Upd/Del DiaryNotes ContactRef
5 Run Query ubeUpdateContacts
6 Delete Field Customers ContactName
7 Delete Field Customers ContactTitle
Here is a brief description of each line, see the full explanations in the Action Codes section above.
Line 1 -- Copies the Contacts table to the back-end.
Line 2 -- Copies the DiaryNotes table to the back-end.
Line 3 -- Create a 1 to Many relationship between tables Customers and Contacts using field CustomerID and with Cascade Updates and Deletes active.
Line 4 - Create a 1 to Many relationship between tables Contacts and DiaryNotes using field ContactRef and with Cascade Updates and Deletes active.
Line 5 -- Run the ubeUpdateContacts update query which copies the ContactName, ContactTitle and Phone field data from table Customers into table Contacts. I have already saved this query in the database to save time.
Lines 6 & 7 -- Delete the ContactName and ContactTitle fields from table Customers, the Phone field is left in table Customers because that really belongs to the company.
Note that in some columns you can choose the table/field from the drop down and others you will have to enter the names manually, this is because the data may or may not be available at the time you are entering the data.
When you have entered all the lines above click the Update Back End button, the updating form will show briefly and then the All Updates Completed OK message will be displayed. Close the form and open the Customers, Contacts and DiaryNotes tables in turn, check that the two fields have been removed in the Customers table and that the Contacts table is populated with the contact names. The DiaryNotes table will be empty at this time, of course. You could also open the back-end file and check that the relationships have been set up correctly. If you were doing this for real you could now add the appropriate forms to display the Contacts and DiaryNotes table data.
Exercise 2. Add new field and change field properties.
Let's assume that some company names in the Customers table are longer than 40 characters (which is what the field size property is set to). We will change it to 100 characters. Also in the Customers table we want to add a Hyperlink field to display Word documents associated with the company. The Orders table has a date field called OrderDate where the Format property is set to Medium Date, we will change that to Short Date (of course, it makes no difference to how the date is stored but it will demonstrate the facility). We will also add a new field to the Products table called Weight to store the weight of each product in Kilograms. In this case the Update code automatically sets the Default property of all Numeric fields to 0 (although you can change this if you want to), this means that when the user adds a new record to the Products table the Weight field will be set to 0 until a new value is entered by the user. The problem then is that the Weight field for all the existing products will be Null which may cause problems for various queries and reports if this field is used as part of a sum or totals calculation. We need to fill all the existing product records with 0 when the new field is added. You could do this as a separate line but most properties can be set at the same time as a new field is added to a table.
Add the following records to the Update Back End form :-
Ref Action Table/Query Name Field Name Field Type Property Additional Data Field Description 8 Set Property Customers CompanyName Text Field Size = 100 Changed size 9 New Field Customers Documents HYPERLINK New Hyperlink 10 Set Property Orders OrderDate Format = Short Date Changed format 11 New Field Products Weight SINGLE Fill With = 0 Add weight
Here is a brief description of each line, see the full explanations in the Action Codes section above.
Line 8 -- Change Text Field Size property of CompanyName to 100 characters.
Line 9 -- Add new Hyperlink field called Documents to table Customers.
Line 10 -- Change format of OrderDate field to 'Short Date' in table Orders.
Line 11 -- Add new field called Weight to table Products and set all existing fields to 0.
Click the Update Back End button to change the back-end tables. Close the Update Back End form and check the tables to see if the changes have been made.
Exercise 3. Execute VBA code on update.
Sometimes it is necessary to make changes to the back-end file using VBA code and you can do this using the Execute Code command. What you should do is write the VBA code and save it in a module and test it thoroughly, I normally create a module called ubeMiscellaneousCode and save any procedures, functions, etc in this module so that they are all kept together and this code is ONLY used by the Updater code. I have created a module with this name in the demo program for you to try out.
It is useful to change the SubdatasheetName property for all back-end tables to [None] to speed up network traffic. The ubeMiscellaneousCode module has some code to do this (provided by Tom Wickerath, I think). If you open any table that is linked to another table (table Orders for example) you will see a small + sign at the left of each record and when you click on that symbol, the associated table data is displayed. Setting the SubdatasheetName to [None] on all tables will remove that facility.
Add the following record to the Update Back End form :-
Ref Action Table/Query Name Field Name Field Type Property Additional Data Field Description 12 Execute Code TurnOffSubDataSh
Click the Update Back End button to change the back-end tables. Close the Update Back End form and check the tables to ensure that the + symbol is no longer available.
Exercise 4. Try your own updates.
Now try out your own changes. Use this database as a test bed for any changes you may need to make in your own projects.
You can test what the client will see when you send him a copy of the new front-end file. Close the database and replace your current back-end file with the copy of the back-end file that you made earlier (before adding any tables).
Run the database again which will simulate what the client will see when he does the same, as his back-end file will not have the new tables/fields etc. The message shown in Fig 4 above will be displayed first, click the Yes button to add the reference table to the back-end file.
If you have then made more changes to the back-end (as we have for this demo) then the message shown in Fig 2 above will be displayed. Click Yes again to make the changes, the normal start up form should then be displayed after a short delay. Check the back-end file to ensure that all the requested changes have been made.
Note that if you have also provided some re-linking code to link the front-end to back-end then that code will run before the updating code.