Using Satellite Forms 4.0 with Microsoft Access 97
Introduction
This paper describes how to integrate your Satellite Forms 4.0 EE application with corporate data stored in Microsoft Access 97. Although the steps described here take advantage of some specific features of Access, many of the concepts are general and apply to other database management systems as well.
Satellite Forms has the ability to save your linked files in DBF or MDB files. If you wish to use Access 97, you must use the DBF format. Access 97 has the ability to link to dBase 5 DBF files regardless of the version of the Microsoft Data Access Components (MDAC) installed on the computer. However, installing MDAC 2.1 or greater can break some of the connections of DBF files to other database management systems, including Access 2000.
Access 2000 installs MDAC 2.5 on the host computer, and Windows 2000 comes installed with MDAC 2.5. Access 2000 cannot link and read/write to DBF files, unless you upgrade the host computer to MDAC 2.6 and also install the Jet ServicePack 4. If you wish to use Access 2000, you should save your files in MDB format.
If you use Access 97, and then later change to Access 2000, you must rewrite your program to accommodate the file format, or upgrade your host computer, as noted above. For this reason, it is often better to use the Access 2000 format unless you have a compelling reason to do otherwise.
If you find any errors in this tutorial, please contact me at jon@pocketinformatics.com .
Jon Blackman, M.D., M.S.
Updated 9/9/01
Overview of Data Storage and Transfer
Please refer to the Satellite Forms Developers Guide, Using Satellite Forms with Microsoft Access, for a description of this process.
Step 1. Decide How Data Should Move Between Handheld Devices and Access 97
Please refer to the Satellite Forms Developers Guide, Using Satellite Forms with Microsoft Access, for a description of this process.
Step 2. Preparing Access 97 to Integrate with Satellite Forms
Prepare Work Order Folder for Access 97
- Create a new folder named Work Order 97 in the Sample Projects directory, which is typically located in c:\Program Files\ Satellite Forms EE \Projects\Sample Projects.
- Open the Work Order folder, copy the Work.sfa file, and paste it into the Work Order 97 folder.
- Double-click the Work.sfa file in the Work Order 97 folder to open it with Satellite Forms.
- Go to EditฎProject Properties..., make sure that the Desktop DB format is Dbase V (*.dbf) and the Creator ID is SMSF, change the Version to Major 1 and Minor 0, and press OK.
- There was an error in the creation of the Work Order database. To correct this, do the following:
- Open the wrkWorkItems table.
- In the Layout tab, press New. Enter the following and press OK.
1. Name: WRKITEMID
2. Data Type: Numeric
3. Width 4
- Highlight the WRKITEMID column and press Up until it is in the first position.
- Press the Editor tab and enter values 1 through 9 in the new column.
- Close the table.
- Go to FileฎSave As..., and press OK to save Work.sfa in the Access 97 folder. Continue to save files until all files are saved. If you are asked to overwrite the files, click Yes.
- Close Satellite Forms.
- Open the Work Order 97 folder. You should see the following files:
- the Work.sfa file (Satellite Forms Application)
- the Work.ICM file
- the Work pda file (Palm Data Application)
- three dbf files and two dbt files (Dbase 5 files)
- four pdb files (Palm Database files)
Create an Access 97 Database
- Create a new folder named Access in the Work Order 97 folder.
- Create a new blank Access 97 database and save it in this new folder as Work Order. The path to the application will then be \Satellite Forms EE \Projects\Sample Projects\Work Order 97\Access\Work Order.mdb.
Linking the Satellite Forms dBase 5 Intermediate Tables to the Access 97 Database
Access 97 can read linked dBase 5 intermediate tables as if they were native Access tables. Linking the Satellite Forms intermediate tables to Access makes it easier to view the data in these tables directly from within Access.
- Open Work Order.mdb in table view.
- Go to FileฎGet External DataฎLink Tables.
- Make sure you are in the Work Order 97 folder. Click the down arrow to the right of Files of Type: and select dBase 5.
- Select WRKLOOKU.dbf , click Link, and then click Cancel (this is not intuitive), and then click OK.
- Do the same for the other two dbf files and click Close.
Import the Satellite Forms API into the Access 97 Database.
Importing the Satellite Forms API ensures that Access will successfully execute the code that you write to mange the HotSync operations. The API sets up several constraints and parameters that will be used by your code. For Access users, an Access Add-in named SATFORMS.MDA is in the \Include directory of the Satellite Forms installation.
- Click Modules and go to FileฎGet External DataฎImport.
- Navigate to \Satellite Forms EE\Include, select SatForms.mda (it should be already selected), and press Import.
- Select Satellite Forms API (this time you really do need to select it), and press ok.
Step 3. Create Access 97 Tables to Serve as the Corporate Database.
You now need to create three additional tables in Access to represent the corporate database. These tables should have the same structure as the dBase 5 tables created by Satellite Forms for shared data elements. However, the overall structure of the tables might differ, depending upon your needs. Some data, for example, might be specific to workers at corporate headquarters, and passing this data to workers in the field would be unnecessary. In our example, we will create identical corporate tables.
- Press the Tables tab, and go to FileฎGet External DataฎImport...
- Navigate to the Access 97 folder, select files of type dBase 5 , select the dbf files one at a time and press Import and OK for each. Press Close.
- Rename the imported files WRKLOOKU_CORPORATE, WRKSITES_CORPORATE, and WRKWORKI_CORPORATE.
- Double-click each table to view the data in the tables.
Step 4 Build an Access 97 Form Including the ActiveX Control
For the HotSync operation to correctly transfer data between Access and Satellite forms, your Access server application must be running when the HotSync occurs and the Active X control must be active (key point). The easiest way to do this is to build an Access form with the Active X control on it as part of the application, and have this form open (running) on the server PC (the PC to which the users HotSync).
- Click the Forms tab and click New.
- You can create a blank form or a form attached to a table or query. For our example, create a form using Form Wizard and the WORKSITES_CORPORATE table, select all fields, select Columnar, select Standard, name the form frmWrkSites, and press Finish.
- Close the form and open it in Design mode. You will now add the Satellite Forms Active X control.
- Go to InsertฎActiveX Control..., find the Satellite Forms EE 4.0 Control, select it and press OK.
- Move the control somewhere out of the way, but on your form.
- Select Properties for the control and change the name to SatForms. This will be used by our code in the next sections.
Step 5. Write General Declarations and a Load_Form subroutine in Access 97
The following steps describe the code that you will write to manage the HotSync operation. Before we start moving the data, we must do some setup by declaring and setting a number of variables and creating a Form_Load subroutine.
Performing the General Declarations
In Access, click the Forms tab, and on the View menu select Code. Add the following Constants and Dim statements under General Declarations (Option Compare Database will already be there). These new variables will be used to store the file locations of the intermediate tables and track the progress of the HotSync operation.
Option
Compare Database
Option
Explicit
Const
Status_HotSyncStart = 1
Const
Status_HotSyncCommandComplete = 3
Const
Status_HotSyncEnd = 2
Dim
strWRKLOOKU As String
Dim
strWRKSITES As String
Dim strWRKWORKI
As String
Dim
HotSync_Progress As String
Dim CmdCount
As Integer
Dim strPATH
As String
Dim strSDDI
As String
Dim
strCreatorID As String
Creating the Form_Load Subroutine
Add a new procedure called Form_Load.
Go to InsertฎProcedure..... Enter the following and press OK.
- Name: Form_Load
- Type: Sub
- Scope: Public
Add the following code to your procedure. It will disable notifications from Access about changing data, set the path to your project directory, set string names for the variables, and enable the Satellite Forms ActiveX control.
Private Sub
Form_Load()
'Disable warnings
DoCmd.SetWarnings 0
'Path to project directory
strPATH = "c:\Program Files\Satellite
Forms EE\Projects
\Sample Projects\Work Order 97\"
'String name of SDDI DLL
strSDDI = "Sddi_PalmDB.dll"
'Creator ID in the App Designer
'You will change this when you distribute
your application
strCreatorID = "SMSF"
strWRKLOOKU = strPATH &
"WRKLOOKU.dbf"
strWRKSITES = strPATH &
"WRKSITES.dbf"
strWRKWORKI = strPATH &
"WRKWORKI.dbf"
HotSync_Progress = "Begin"
SatForms.Enabled = True
End Sub
Step 6. Write the HotSync Event Handler in Access 97 to Manage HotSync
You will now write the code that will move the data from handheld devices to the corporate database and from the corporate database back to handheld devices. As described earlier, there are four data transfers that we need to perform in our sample application. Your code will vary to fit your needs.
A to B: Updated records move from handheld devices to dBase 5 intermediate tables.
B to C: Updated records move from dBase 5 intermediate tables to Access database.
C to B: New records move from Access database to dBase 5 intermediate tables.
B to A: New records move from dBase 5 intermediate tables to handheld devices.
We will look at each of these steps in detail, working with the wrkSites table.
Step 6a. Moving Data A to B
You will create the subroutine SatForms_HotSyncStatus, which will serve as your HotSync event handler. To move data from the handheld device to the dBase 5 intermediate tables, you will use the GetTableFromPalmPilot method of the Satellite Forms ActiveX control. To transfer multiple tables, you can call this method more than once using different filenames. The variable CmdCount is used to keep track of how many tables are transferred (or commands have been given).
Important: In order to execute your new code, you must open the frmWrkSites form.
Verify that data is being transferred by making changes to the data on your handheld then synchronizing it back to Access. The GetTableFromPalmPilot method will overwrite all existing data in the tables with data from the handheld.
The Form_Load Subroutine is listed below. In the following sections, we will explain the logic and fill in the code.
Private Sub
SatForms_HotSyncStatus(ByVal StatusCode As Long, ByVal Param As Long)
'Check to
make sure events are from this application
If
SatForms.CreatorString <> strCreatorID Then Exit Sub
If StatusCode
= Status_HotSyncEnd Then
'We're done
HotSync_Progress = "End"
Exit Sub
End If
If StatusCode
= Status_HotSyncStart Then
'Move data from A --> B
Call
SatForms.GetTableFromPalmPilot(strWRKWORKI, strCreatorID, strSDDI, 0, 1, 0)
Call
SatForms.GetTableFromPalmPilot(strWRKSITES, strCreatorID, strSDDI, 0, 1, 0)
'Count 2 commands to transfer files
CmdCount = 2
HotSync_Progress = "A>B"
End If
If StatusCode
= Status_HotSyncCommandComplete Then
CmdCount = CmdCount - 1
If CmdCount <> 0 Then GoTo
CmdCompleteExit
'File set not yet finished
If HotSync_Progress = "A>B"
Then
'Move data from B --> C
HotSync_Progress = "B>C"
End If
If HotSync_Progress = "B>C"
Then
'Move Data from C --> B
HotSync_Progress = "C>B"
End If
If HotSync_Progress = "C>B"
Then
'Move data from B --> A
HotSync_Progress = "B>A"
Exit Sub
End If
CmdCompleteExit:
End If
End Sub
Step 6b. Moving Data B to C
Important: Be sure that your application completes the movement of data from A to B before it tries to move this data from B to C.
Use the Access Update Query command to move data from the dBase 5 intermediate tables (linked to Access) to the corporate database.
Please refer to the Satellite Forms Developers Guide, Using Satellite Forms with Microsoft Access, for a description of this process. The example updates the WRKSITES_CORPORATE table. If you modified the wrkWorkItems table in App Designer earlier in the tutorial, you may use the same process to update the WRKWORKI_CORPORATE table.
Now you will need to add additional code to your HotSync handler to run this query. The code should execute after the methods GetTableFromPalmPilot have completed. Use the status code HotSyncCommandComplete and CmdCount to determine when this occurs.
The HotSyncCommandComplete event occurs once for every command given, or file transferred. In this case, there were two commands to upload files, so this event will occur twice before both tables are uploaded. Decrement CmdCount each time the Status_HotSyncCommandComplete fires. When CmdCount has decremented to zero, all the commands have completed (or files uploaded) and it is time to process the data.
Add the following code to your event handler and test your program. Make changes on the handheld and perform a HotSync. Changes on the Palm should be reflected on your *_CORPORATE table.
If HotSync_Progress = "A>B" Then
'Move data from B --> C
DoCmd.OpenQuery ("UPDATE_WRKSITES_CORPORATE")
DoCmd.OpenQuery ("UPDATE_WRKWORKI_CORPORATE")
HotSync_Progress = "B>C"
End If
Step 6c. Moving Data C to B
Use the Access Append Query command to add new records from the corporate database to the dBase 5 intermediate tables linked to Access.
Please refer to the Satellite Forms Developers Guide, Using Satellite Forms with Microsoft Access, for a description of this process. The example appends records to the WRKSITES table. If you modified the wrkWorkItems table in App Designer earlier in the tutorial, you may use the same process to append records to the WRKWORKI table.
Test your queries by adding some records to the corporate tables, double-clicking the queries, and checking the linked tables. Be sure to add new and unique ID numbers to the new records. You may want to automate this process in your application.
As before, add the following code and test your data transfer.
If HotSync_Progress = "B>C" Then
'Move
Data from C --> B
DoCmd.OpenQuery ("INSERT_WRKSITES")
DoCmd.OpenQuery ("INSERT_WRKWORKI")
HotSync_Progress = "C>B"
End If
Step 6d. moving Data B to A
The last step is moving the dBase 5 data back to handheld devices. Add the following code to your HotSync event handler to run the CopyTableToPalmPilot methods. This will take the data from the dBase 5 intermediate tables and move it to the handheld.
If HotSync_Progress = "C>B" Then
'Move
data from B --> A
Call
SatForms.CopyTableToPalmPilot(strWRKWORKI, strCreatorID, strSDDI, 0, 1, 0)
Call SatForms.CopyTableToPalmPilot(strWRKSITES, strCreatorID, strSDDI, 0, 1, 0)
Call
SatForms.CopyTableToPalmPilot(strWRKLOOKU, strCreatorID, strSDDI, 0, 1, 0)
'Count 3 commands to transfer files
CmdCount = 3
HotSync_Progress = "B>A"
Exit
Sub
End If
Final Testing
Test your procedures to see that records are moved appropriately.
The final code is as follows: (Note that longer lines wrap to the next line.)
Option Compare Database
Option Explicit
Const Status_HotSyncStart = 1
Const Status_HotSyncCommandComplete = 3
Const Status_HotSyncEnd = 2
Dim strWRKLOOKU As String
Dim strWRKSITES As String
Dim strWRKWORKI As String
Dim HotSync_Progress As String
Dim CmdCount As Integer
Dim strPATH As String
Dim strSDDI As String
Dim strCreatorID As String
Private Sub Form_Load()
'Disable
warnings
DoCmd.SetWarnings 0
'Path to
project directory
strPATH =
"c:\Program Files\Satellite Forms EE\Projects\Sample Projects\Work Order
97\"
'String
name of SDDI DLL
strSDDI =
"Sddi_PalmDB.dll"
'Creator
ID in the App Designer
'You will
change this when you distribute your application
strCreatorID = "SMSF"
strWRKLOOKU = strPATH & "WRKLOOKU.dbf"
strWRKSITES = strPATH & "WRKSITES.dbf"
strWRKWORKI = strPATH & "WRKWORKI.dbf"
HotSync_Progress = "Begin"
SatForms.Enabled = True
End Sub
Private Sub SatForms_HotSyncStatus(ByVal StatusCode
As Long, ByVal Param As Long)
'Check to make sure events are from this application
If SatForms.CreatorString <> strCreatorID Then
Exit Sub
If StatusCode = Status_HotSyncEnd Then
'We're
done
HotSync_Progress = "End"
Exit Sub
End If
If StatusCode = Status_HotSyncStart Then
'Move
data from A --> B
Call
SatForms.GetTableFromPalmPilot(strWRKWORKI, strCreatorID, strSDDI, 0, 1, 0)
Call
SatForms.GetTableFromPalmPilot(strWRKSITES, strCreatorID, strSDDI, 0, 1, 0)
'Count 2
commands to transfer files
CmdCount
= 2
HotSync_Progress = "A>B"
End If
If StatusCode = Status_HotSyncCommandComplete Then
CmdCount
= CmdCount - 1
If
CmdCount <> 0 Then GoTo CmdCompleteExit
'File set
not yet finished
If
HotSync_Progress = "A>B" Then
'Move
data from B --> C
DoCmd.OpenQuery ("UPDATE_WRKSITES_CORPORATE")
DoCmd.OpenQuery ("UPDATE_WRKWORKI_CORPORATE")
HotSync_Progress = "B>C"
End If
If
HotSync_Progress = "B>C" Then
'Move
Data from C --> B
DoCmd.OpenQuery
("INSERT_WRKSITES")
DoCmd.OpenQuery ("INSERT_WRKWORKI")
HotSync_Progress = "C>B"
End If
If
HotSync_Progress = "C>B" Then
'Move
data from B --> A
Call
SatForms.CopyTableToPalmPilot(strWRKWORKI, strCreatorID, strSDDI, 0, 1, 0)
Call
SatForms.CopyTableToPalmPilot(strWRKSITES, strCreatorID, strSDDI, 0, 1, 0)
Call
SatForms.CopyTableToPalmPilot(strWRKLOOKU, strCreatorID, strSDDI, 0, 1, 0)
'Count 3 commands to transfer files
CmdCount = 3
HotSync_Progress = "B>A"
Exit
Sub
End If
CmdCompleteExit:
End If
End Sub