Tutorial: Setting up automatic Access data conversions with HanDBase Plus and

HanDBase Data Exchange

With the variety of add-on tools we offer for the HanDBase program, we have offered a number of ways to

interface handheld data with desktop database managers like Microsoft Access. Because of the interoperability

among our tools, there are many ways you can piece the tools together to form solutions to your needs. One of

the most common of such is a way to collect data from handhelds directly into Microsoft Access. While our

HanDBase ODBC Driver solution already provides a way that you can access and

synchronize handheld databases directly from within Access, users are often

looking for a way to get data from a handheld database into an Access table.

The HanDBase Data Exchange for Access add-on was created for just that

purpose. Using a wizard-type interface, you can set up the parameters for

conversion between HanDBase and the Access table. This can be used to

move data into HanDBase from Access, or vice versa. However, users may find

this process tedious to do before or after each hotsync, and would to automate

this process on every hotsync. In fact, we've built the ability for

this right into the HanDBase Conduit (included with the HanDBase Plus package,

or available separately)! This tutorial outlines the process to call the

HanDBase Data Exchange directly from the HanDBase conduit on every database

sync.

Example:

This example, creates a replica of each of your handheld databases

in a database called "My HanDBase Data.MDB". The data will automatically be

converted to Access on every sync.

Assumptions:

- User has installed the HanDBase Conduit (or HanDBase Plus) and the HanDBase Data Exchange for Access add-on.

- The HanDBase Data Exchange for Access add-on is installed in the c:\Program Files\HanDBase\Data Exchange

folder on the local PC.

Steps For Palm Version

1. Right click on the Hotsync Icon in the lower corner of your screen, and select 'Custom'.

2. Select the HanDBase Conduit, and choose the option 'Change'. You are now shown the HanDBase Conduit settings for the current selected user.

3. The bottom portion of the screen shows the automatic options available during the conduit sync. On the left side are options that can be run before

the sync, and the right side has options after the sync. In our example, we want to convert each handheld database to Access format, and so we will want

to perform this operation after it's synchronized with the handheld, to assure we have the latest data. Since we want each and every HanDBase database on

the handheld to convert to Access, we will select the option to 'Perform command after each db sync'.

4. Now we need to specify the command to run HanDBase Data Exchange, and have it convert a HanDBase PDB file to Access. From reading the HanDBase

Data Exchange documentation, the following commands seem applicable:

pdbtoaccess - specifies we're going from a pdb file to Access.

pdbfile:{<pdb_file_name>} - where we specify the pdb file.

mdbname:{<mdb_file_name>} - where we specify the name and path of the Access

.MDB file

table:{<table_name>} - where we specify the name of the table in Access.

deletetable:{<delete_table_action>} - Here's where we choose whether to:

deletetable: replace the entire table with a new one.

deleterows: replace any existing data

appendrows: append this data to the table (if it already exists)

autorun:{true or false} - specifies whether to launch the HanDBase Data Exchange GUI with the options you specified for confirmation, or not. Specify true if you do not want to launch the GUI for confirmation. Specify false if you would like to launch the GUI for confirmation.

autoexit:{true or false} - specifies whether the program will stay open after the conversion, or close automatically.

So we need to set up the command appropriately, based on our goal of creating this from every database. But how do we specify what to call the

database, the table, or the pdbfile, when these variables are different for each HanDBase database that we sync? This is where we use MACROS. A set

of MACROS have been defined in the HanDBase conduit to substitute certain items into the command lines. From the HanDBase Conduit Documentation, if

you place any of the all-caps words below in your command line:

CURRENTFILENAME is replaced with the complete PC file name (drive, path, name, extension). You can put this string anywhere, and more than once.

DATABASENAMEONLY is replaced with the complete handheld database name. You can put this string anywhere, and more than once.

PALMUSERNAME is replaced with the user name of the Palm which that Database is synchronized with. You can put this string anywhere, and more than once.

So now we can set up our command options. Once again:

pdbtoaccess - specifies we're going from a pdb file to Access. We'll need to specify this setting, so our command line will start as:

"c:\program files\handbase\data exchange\hdeaccess" pdbtoaccess

(This is the path to our hdeaccess.exe file (the HanDBase Data Exchange executable filename and the option pdbtoaccess, separated by a space).

It is in quotes so that the Windows operating system understands that this is all one command, and the spaces don't make it think there are

two commands.

5. Now the next parameter:

pdbfile:{<pdb_file_name>} - here we specify the pdbfile using the MACRO 'CURRENTFILENAME'. So our command line is now:

"c:\program files\handbase\data exchange\hdeaccess" pdbtoaccess pdbfile:{CURRENTFILENAME}

6. And the next:

mdbname:{<mdb_file_name>} - here we can specify the name of the Access database where we store this. As we mentioned, we were going to put

this in a database called My HanDBase Data.MDB. Let's put this in the root directoy of drive C, like this:

"c:\program files\handbase\data exchange\hdeaccess" pdbtoaccess pdbfile:{CURRENTFILENAME} mdbname:{c:\My HanDBase Databases}

7. The name of the table:

table:{<table_name>} - where we specify the name of the table in Access. In this case, we want to specify the name of the HanDBase database/table,

using the MACRO 'DATABASENAMEONLY':

"c:\program files\handbase\data exchange\hdeaccess" pdbtoaccess pdbfile:{CURRENTFILENAME} mdbname:{c:\My HanDBase Databases} table:{DATABASENAMEONLY}

8. The deletetable option:

deletetable:{<delete_table_action>} - Here's where we choose whether to replace the entire table with a new one, replace any existing data, or

append this data to the table (if it already exists). Since this example is about trying to create a replica of what's in HanDBase, we'll

specify - deletetable, and let it create a new table based on what it finds in HanDBase. So our command line now reads:

"c:\program files\handbase\data exchange\hdeaccess" pdbtoaccess pdbfile:{CURRENTFILENAME} mdbname:{c:\My HanDBase Databases.MDB} table:{DATABASENAMEONLY} deletetable:{deletetable}

9. The autorun option:

autorun:{true or false} - specifies whether to launch the HanDBase Data Exchange GUI with the options you specified for confirmation,

or not. Specify true if you do not want to launch the GUI for confirmation. Specify false if you would like to launch the GUI for confirmation.

we want this to happen automatically, and only tell us if there is an

error, so we'll select true:

"c:\program files\handbase\data exchange\hdeaccess" pdbtoaccess pdbfile:{CURRENTFILENAME} mdbname:{c:\My HanDBase Databases.MDB} table:{DATABASENAMEONLY} deletetable:{deletetable} autorun:{true}

10. Lastly, the autoexit option:

autoexit:{true or false} - specifies whether the program will stay open after the conversion, or close automatically. Again, we want this to

run without our intervention unless there is a problem, so we'll select 'true' for this option as well. This yields our final command line as:

"c:\program files\handbase\data exchange\hdeaccess" pdbtoaccess pdbfile:{CURRENTFILENAME} mdbname:{c:\My HanDBase Databases.MDB} table:{DATABASENAMEONLY} deletetable:{deletetable} autorun:{true} autoexit:{true}

So let's enter this into the space below the 'Command:' label in the 'After Sync' box.

11. Now, let's select OK to save these settings, and then select 'Done' to close the Custom Conduit settings.

12. Now we should be ready to sync. Put the Palm handheld into the cradle and press the Hotsync button. If all is correct, we should now be creating

the Access database, and all of the tables therein.

One important note: The HanDBase conduit does not run on databases that have not changed since the last sync, for speed optimization purposes. Because of

this, you will only find databases in your Access database that have been changed since setting this up. Simply opening each database on the handheld

after configuring the above, will set the modification date forward so that the conduit will sync them, and thus they will be converted into Access.

Thereafter, every time they are edited, the conduit will sync them, and they will be replicated in Access by this command line.

That's it- I hope you enjoy your newfound capabilites, and the advantages of being able to have access to all of your handheld data in a powerful program

like Microsoft Access!

Troubleshooting:

If the c:\ folder does not contain an Access file called My HanDBase Databases.MDB, perhaps one of the following has occurred:

- You're not using the latest version of the HanDBase Conduit (this example requires 2.77c or higher to run). To check the version of the conduit,

right click the Hotsync icon at the bottom right of your screen, and select the 'View Log' option. The HanDBase loggings should mention the version

number.

- You're not using the latest version of the HanDBase Data Exchange for Access (this example requires V1.12 or higher to run). To check the

version of HanDBase Data Exchange for Access, run the HanDBase Data Exchange for Access program and select 'Help', and then 'About'. You

should see the version there.

- There is a typo in your command line. Please double check that it matches above.

- You've installed the HanDBase Data Exchange for Access to a different folder than the default. You will have to modify the command line to point

correctly to whereever you have installed it.

- You have not made a change to any of your databases since setting this up. Open one or more databases on your handheld and try again.

Steps For PPC Version

Note: We've taken great pains to make the Pocket PC version of HanDBase and it's conduit act as similarly to the Palm version without compromising the

ease-of-use, speed, or making the program feel clumsy and out-of-place on the different operating system. As a result, the steps for doing this on the

Pocket PC are nearly the same.

1. Select the Start Menu, and then Programs, and then HanDBase. In that section should be an option for 'ActiveSync Conduit Options'. Click on

that to get to the settings for HanDBase's conduit.

2. Select the user in the top left that you want to adjust this for. You are now shown the HanDBase Conduit settings for the current selected user.

3. The bottom portion of the screen shows the automatic options available during the conduit sync. On the left side are options that can be run before

the sync, and the right side has options after the sync. In our example, we want to convert each handheld database to Access format, and so we will want

to perform this operation after it's synchronized with the handheld, to assure we have the latest data. Since we want each and every HanDBase database on

the handheld to convert to Access, we will select the option to 'Perform command after each db sync'.

4. Now we need to specify the command to run HanDBase Data Exchange, and have it convert a HanDBase PDB file to Access. From reading the HanDBase

Data Exchange documentation, the following commands seem applicable:

pdbtoaccess - specifies we're going from a pdb file to Access.

pdbfile:{<pdb_file_name>} - where we specify the pdb file.

mdbname:{<mdb_file_name>} - where we specify the name and path of the Access

.MDB file

table:{<table_name>} - where we specify the name of the table in Access.

deletetable:{<delete_table_action>} - Here's where we choose whether to:

deletetable: replace the entire table with a new one.

deleterows: replace any existing data

appendrows: append this data to the table (if it already exists)

autorun:{true or false} - specifies whether to launch the HanDBase Data Exchange GUI with the options you specified for confirmation, or not. Specify true if you do not want to launch the GUI for confirmation. Specify false if you would like to launch the GUI for confirmation.

autoexit:{true or false} - specifies whether the program will stay open

after the conversion, or close automatically.

So we need to set up the command appropriately, based on our goal of creating this from every database. But how do we specify what to call the

database, the table, or the pdbfile, when these variables are different for each HanDBase database that we sync? This is where we use MACROS. A set

of MACROS have been defined in the HanDBase conduit to substitute certain items into the command lines. From the HanDBase Conduit Documentation, if

you place any of the all-caps words below in your command line:

CURRENTFILENAME is replaced with the complete PC file name (drive, path, name, extension). You can put this string anywhere, and more than once.

DATABASENAMEONLY is replaced with the complete handheld database name. You can put this string anywhere, and more than once.

PALMUSERNAME is replaced with the user name of the Palm which that Database is synchronized with. You can put this string anywhere, and more than once.

So now we can set up our command options. Once again:

pdbtoaccess - specifies we're going from a pdb file to Access. We'll need to specify this setting, so our command line will start as:

"c:\program files\handbase\data exchange\hdeaccess" pdbtoaccess

(This is the path to our hdeaccess.exe file (the HanDBase Data Exchange executable filename and the option pdbtoaccess, separated by a space).

It is in quotes so that the Windows operating system understands that this is all one command, and the spaces don't make it think there are

two commands.

5. Now the next parameter:

pdbfile:{<pdb_file_name>} - here we specify the pdbfile using the MACRO 'CURRENTFILENAME'. So our command line is now:

"c:\program files\handbase\data exchange\hdeaccess" pdbtoaccess pdbfile:{CURRENTFILENAME}

6. And the next:

mdbname:{<mdb_file_name>} - here we can specify the name of the Access database where we store this. As we mentioned, we were going to put

this in a database called My HanDBase Data.MDB. Let's put this in the root directoy of drive C, like this:

"c:\program files\handbase\data exchange\hdeaccess" pdbtoaccess pdbfile:{CURRENTFILENAME} mdbname:{c:\My HanDBase Databases.MDB}

7. The name of the table:

table:{<table_name>} - where we specify the name of the table in Access. In this case, we want to specify the name of the HanDBase database/table,

using the MACRO 'DATABASENAMEONLY':

"c:\program files\handbase\data exchange\hdeaccess" pdbtoaccess pdbfile:{CURRENTFILENAME} mdbname:{c:\My HanDBase Databases.MDB} table:{DATABASENAMEONLY}

8. The deletetable option:

deletetable:{<delete_table_action>} - Here's where we choose whether to replace the entire table with a new one, replace any existing data, or

append this data to the table (if it already exists). Since this example is about trying to create a replica of what's in HanDBase, we'll

specify - deletetable, and let it create a new table based on what it finds in HanDBase. So our command line now reads:

"c:\program files\handbase\data exchange\hdeaccess" pdbtoaccess pdbfile:{CURRENTFILENAME} mdbname:{c:\My HanDBase Databases.MDB} table:{DATABASENAMEONLY} deletetable:{deletetable}

9. The autorun option:

autorun:{true or false} - specifies whether to launch the HanDBase Data Exchange GUI with the options you specified for confirmation,

or not. Specify true if you do not want to launch the GUI for confir