Sync With Access

Discuss the HanDBase for Windows Desktop program, conduits, and add-ons.

Sync With Access

Postby Small Shiny Objects » Sat Nov 19, 2011 10:22 pm

I have an Access database (.accdb version) that I have a lot of contacts in. This database is for the project that I will be working on for the next few months.

I have the contacts in one table and businesses in another table. Each contact is linked to a business, and most of businesses have more than one contact associated with them. Right now there are probably around 100 businesses and 300 contacts in these tables. By the end of the project there will probably be at least double that.

I like storing the businesses separate from the contacts so I am not storing redundant information and also so I can easily see all contacts associated with each business. I do not store these contacts in Outlook for those reasons as well as the fact that these are only people I need to keep track of temporarily, so I don't need them cluttering up my Outlook.

I am travelling a lot and often go long periods of time without powering up my laptop and it would be very helpful to have access to these contacts and businesses on my Android phone. If I purchase the Enterprise Windows desktop software how easy will it be for me to get these contact's and businesses onto my Android phone?

Will I have to create the database structures on my phone first, or can I just point the desktop software to those two tables in my .accdb database and it will do the rest?
Small Shiny Objects
 
Posts: 41
Joined: Sun Oct 16, 2011 2:48 pm

Re: Sync With Access

Postby curtterp » Sun Nov 20, 2011 3:47 pm

The difference between the Professional and Enterprise desktops are this. Both can do Access syncing, but the Enterprise can do both Access and ODBC syncing.

You can get by with Desktop professional. With both Professional and Enterprise editions, you can actually import an Access table into a new HanDBase table. I have done it a few times, but it has been a while. If I remember right, you can import the tables one at a time, but you would need to setup any relationships between the tables in the HanDBase databases. The import function is an easy way to setup the HanDBase tables so you have the same field types so syncing will be a breeze. It is a little bit more setup to sync with Access, but not that much more. Once it is setup, it works pretty well.

Hope this information helps... if you have more questions, feel free to ask
Have a good day

Curt
I am not a DDHSoftware employee, just a long time HanDBase user.... from Palm to Windows Mobile to Android, to iOS. Thanks to DDH, the database files transferred to each platform without a problem.
curtterp
 
Posts: 679
Joined: Tue May 26, 2009 6:41 pm
Location: Northern IL

Re: Sync With Access

Postby Small Shiny Objects » Thu Dec 01, 2011 11:04 pm

How safe would it be to sync these two tables with Access? I do not want to corrupt my Access database.
Small Shiny Objects
 
Posts: 41
Joined: Sun Oct 16, 2011 2:48 pm

Re: Sync With Access

Postby Brian_Houghton » Thu Dec 01, 2011 11:07 pm

It's safe, however if you are working with any type of essential data, I always recommend making a backup.

Here's how to do the conversion and set up your custom Sync Exchange entries:

HOW TO SET UP SYNCING WITH ACCESS:

1. Open Data Exchange, found at Start Menu -> All Programs ->
HanDBase 4 -> Tools
2. Choose to convert from Access to HanDBase
3. Select your Access database, select the table to convert and enter
the full path of the HanDBase database to be created. For example,
use C:\test.pdb. Then click Export.
4. Open the newly created HanDBase database in HanDBase Desktop.
5. Delete all of the records, save your changes and install the
database to the Android device.

Then you will want to set up the Sync Exchange entry:

To configure a custom Sync Exchange entry for your database, please
use the following steps to set it up:

1. In HanDBase Desktop, select the Configure -> Android Users menu option.
2. Select the Edit Sync Settings option.
3. Select the database in question on the left side of the dialog.
4. Uncheck the option to use default sync settings in the upper right corner.
5. Check the box to sync with Access / ODBC at the lower right corner
of the dialog.
6. Click the Settings button.
7. Configure the database to sync with the Access database and table.
You can also set up any field mapping that is needed.

Hope this helps. Give it a try and let me know if you run into any problems.
Kind Regards,
Brian Houghton, DDH Software
Brian_Houghton
 
Posts: 2246
Joined: Wed May 20, 2009 8:30 am

Re: Sync With Access

Postby Small Shiny Objects » Thu Dec 08, 2011 11:15 pm

Brian, I purchased Windows Enterprise Desktop, installed, and set up a partnership with my Droid Bionic.

I've sent you a PM.
Small Shiny Objects
 
Posts: 41
Joined: Sun Oct 16, 2011 2:48 pm

Re: Sync With Access

Postby Small Shiny Objects » Fri Dec 09, 2011 10:59 pm

Brian, I followed your instructions as closely as I could. Everything was pretty similar to how you described, but the databases on my phone kept showing up blank no matter what I tried.

I finally saw the button for the sync log, and it is telling me that "ODBC Error was Microsoft Access 'autonumber' fields cannot be synchronized. Remove these coumns from the column list.

I can't remove the autonumber fields, those are my primary keys, something that every single Access database that follows data normalization standards has. I sincerely hope I'm missing something here, because without my primary keys, I have no way of linking my two tables together.

I guess in theory I could remove the primary key from my "Contacts" table, but the primary key in my "Offices" table is a foreign key in my "Contacts" table and without that link, my database will be fairly useless.

Please tell me I am just misunderstanding your directions and did something wrong and that I don't have to delete my primary key.
Small Shiny Objects
 
Posts: 41
Joined: Sun Oct 16, 2011 2:48 pm

Re: Sync With Access

Postby Brian_Houghton » Sun Dec 11, 2011 11:00 pm

Hi,

Indeed, autonumbering fields are not supported by Sync Exchange. The Microsoft Access AutoNumber field type serves as a convenience for generating unique identifiers for records in a table. However, Microsoft Access does not allow external sources to modify the values in AutoNumber fields in existing records or create values in an AutoNumber field for new records.

Because of this, if you have create a configuration in Sync Exchange between a HanDBase database and an MS Access database that has an AutoNumber field in it, you should omit the AutoNumber field from the field mapping for that synchronization. If you include an AutoNumber field in the field mapping, then the first time you create a new record in HanDBase, Microsoft Access will reject the new record, because Access will not allow external data sources to create a value in an AutoNumber field.

When creating a configuration in Sync Exchange, you can specify to Merge All Columns, Merge Selected Columns or Merge mapped columns. If you choose Merge Selected Columns or Merge mapped columns, you can specify which fields are included in the synchronization. Use either of these two options, and make sure that you omit the AutoNumber field in Access -- just do not match up the AutoNumber field in Access with any field in the HanDBase database.
Kind Regards,
Brian Houghton, DDH Software
Brian_Houghton
 
Posts: 2246
Joined: Wed May 20, 2009 8:30 am

Re: Sync With Access

Postby Small Shiny Objects » Tue Dec 13, 2011 10:09 pm

Brian, I am totally OK with not being able to create new records on my phone. For this particular application, I would actually prefer the records on my phone be read only, to minimize the chances of corrupting my Access DB.

I hope you understand it was reasonable for me to link my contacts table to my offices table by having the autonumber primary key in my offices table as the foreign key in my contacts table, as that is relational database 101.

So, is there any way I can link my contacts table my offices table that will not change the structure of my Access DB and which will not require extensive manual work?
Small Shiny Objects
 
Posts: 41
Joined: Sun Oct 16, 2011 2:48 pm

Re: Sync With Access

Postby Brian_Houghton » Wed Dec 14, 2011 11:07 am

Hi,

You could set up your sync mapping so that the autonumbering field was excluded, and then use features in HanDBase to recreate the functionality on your handheld. You could add a calculated field that adds 1 to its previous value as a way of autonumbering your fields. Then define a relationship between the parent and child databases based on the calculated field.

By omitting all of these fields from your Sync Exchange mapping, you would run no risk of damaging your Access database.
Kind Regards,
Brian Houghton, DDH Software
Brian_Houghton
 
Posts: 2246
Joined: Wed May 20, 2009 8:30 am

Re: Sync With Access

Postby Small Shiny Objects » Thu Dec 15, 2011 11:42 am

Brian_Houghton wrote:It's safe, however if you are working with any type of essential data, I always recommend making a backup.

Here's how to do the conversion and set up your custom Sync Exchange entries:

HOW TO SET UP SYNCING WITH ACCESS:

1. Open Data Exchange, found at Start Menu -> All Programs ->
HanDBase 4 -> Tools
2. Choose to convert from Access to HanDBase
3. Select your Access database, select the table to convert and enter
the full path of the HanDBase database to be created. For example,
use C:\test.pdb. Then click Export.
4. Open the newly created HanDBase database in HanDBase Desktop.
5. Delete all of the records, save your changes and install the
database to the Android device.
Does this test.pdb ever get used again (for syncing or otherwise), or is this just the necessary process to get the database structure onto the Android phone?
Then you will want to set up the Sync Exchange entry:

To configure a custom Sync Exchange entry for your database, please
use the following steps to set it up:

1. In HanDBase Desktop, select the Configure -> Android Users menu option.
2. Select the Edit Sync Settings option.
3. Select the database in question on the left side of the dialog.
4. Uncheck the option to use default sync settings in the upper right corner.
5. Check the box to sync with Access / ODBC at the lower right corner
of the dialog.
6. Click the Settings button.
7. Configure the database to sync with the Access database and table.
I set this up to sync to the original Microsoft Access Database, correct?
You can also set up any field mapping that is needed.

Hope this helps. Give it a try and let me know if you run into any problems.
Small Shiny Objects
 
Posts: 41
Joined: Sun Oct 16, 2011 2:48 pm

Next

Return to HanDBase for Windows

Who is online

Users browsing this forum: No registered users and 1 guest

cron