Using Satellite Forms 4.0 with Microsoft Access 2000

Introduction

This paper describes how to integrate your Satellite Forms 4.0 EE application with corporate data stored in Microsoft Access 2000. 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. This process is discussed in the companion tutorial “Using Satellite Forms 4.0 with Microsoft Access 97.” 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 Developer’s 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 2000

Please refer to the Satellite Forms Developer’s Guide, “Using Satellite Forms with Microsoft Access,” for a description of this process.

Step 2. Preparing Access 2000 to Integrate with Satellite Forms

Prepare Work Order Folder for Access 2000

1. Create a new folder named Work Order 2000 in the Sample Projects directory, which is typically located in c:\Program Files\ Satellite Forms EE \Projects\Sample Projects.
2. Open the Work Order folder, copy the Work.sfa file, and paste it into the Work Order 2000 folder.
3. Double-click the Work.sfa file in the Work Order 2000 folder to open it with Satellite Forms.
4. Go to Edit®Project Properties..., make sure that the Desktop DB format is “Access (*.mdb)” and the Creator ID is “SMSF”, change the Version to Major “1” and Minor “0”, and press “OK”.
5. There was an error in the creation of the Work Order database. To correct this, do the following:
1. Open the wrkWorkItems table.
2. In the Layout tab, press New. Enter the following and press OK.

1. Name: WRKITEMID

2. Data Type: Numeric

3. Width 4

3. Highlight the WRKITEMID column and press Up until it is in the first position.
4. Press the Editor tab and enter values 1 through 9 in the new column.
5. Close the table.
5. Go to File®Save As..., and press OK to save Work.sfa in the Access 2000 folder. Continue to save files until all files are saved. If you are asked to overwrite the files, click Yes.
6. Close Satellite Forms.
7. Open the Work Order 2000 folder. You should see the following files:
1. the Work.sfa file (Satellite Forms Application)
2. the Work.ICM file
3. the Work pda file (Palm Data Application)
4. three mdb files
5. four pdb files (Palm Database files)

Create an Access 2000 Database

1. Create a new folder named Access in the Work Order 2000 folder.
2. Create a new blank Access 2000 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 2000\Access\Work Order.mdb.

Linking the Satellite Forms MDB Intermediate Tables to the Access 2000 Database

Access 2000 can read the intermediate MDB 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.

1. Open Work Order.mdb in table view.
2. Go to File®Get External Data®Link Tables.
3. Make sure you are in the Work Order 2000 folder.
4. Select WRKLOOKU and click Link.
5. In the Link Tables box, select SFTable and click OK.
6. In the Table view of Access, rename the linked table WRKLOOKU.
7. Do the same for the other two mdb files and click Close.

Import the Satellite Forms API into the Access 2000 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.

1. Select the Modules view and go to File®Get External Data®Import.
2. Navigate to \Satellite Forms EE\Include, select SatForms.mda (it should be already selected), and press Import.
3. Select Satellite Forms API (this time you really do need to select it), and press ok.

Step 3. Create Access 2000 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 MDB 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.

1. Select the Tables view and go to File®Get External Data®Import...
2. Navigate to the Access 2000 folder, select the MDB files one at a time, press Import, remember to select SFTable, press OK, and rename the file. You will rename the imported files WRKLOOKU_CORPORATE, WRKSITES_CORPORATE, and WRKWORKI_CORPORATE
3. Double-click each table to view the data in the tables.

Step 4 Build an Access 2000 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).

1. Select the Forms view and click New.
2. 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.
3. Close the form and open it in Design mode. You will now add the Satellite Forms Active X control.
4. Go to Insert®ActiveX Control..., find the Satellite Forms EE 4.0 Control, select it and press OK.
5. Move the control somewhere out of the way, but on your form.
6. 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 2000

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, select the Forms view, and go to View®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.

1. Name: Form_Load
2. Type: Sub
3. 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 2000\"

'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.mdb"

strWRKSITES = strPATH & "WRKSITES.mdb"

strWRKWORKI = strPATH & "WRKWORKI.mdb"

HotSync_Progress = "Begin"

SatForms.Enabled = True

End Sub

’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’

Step 6. Write the HotSync Event Handler in Access 2000 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 MDB intermediate tables.

B to C: Updated records move from MDB intermediate tables to Access database.

C to B: New records move from Access database to MDB intermediate tables.

B to A: New records move from MDB 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 MDB 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 MDB intermediate tables (linked to Access) to the corporate database.

Please refer to the Satellite Forms Developer’s 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 MDB intermediate tables linked to Access.

Please refer to the Satellite Forms Developer’s 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 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 MDB 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 2000\"

'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.mdb"

strWRKSITES = strPATH & "WRKSITES.mdb"

strWRKWORKI = strPATH & "WRKWORKI.mdb"

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

’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’