Access 2007-2016
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  

< Previous: Access Expert 22

Next: Access Expert 24 >

Access Expert Level 23

Expert Microsoft Access Tutorial - 1 Hour, 40 Minutes
In Access Expert 23 we will build a database for remote data entry. This will allow our sales reps or service technicians to add contact records on the road. We will learn how to import bank transaction data. We will learn about multi-field composite keys, and how to transfer related tables using XML data. Topics include:
  - Remote Data Entry Database
  - View Info, Add Contacts Remotely
Synchronize With Main Server

  - Import Bank Transactions
  - Prevent or View Duplicates
  - Multi Field Composite Keys
  - Prevent Duplicate Sales Reports
Transfer Relational XML Data

  - Exporting Customers With Orders
  - Maintain Relationships via XML Files

Order Now

If you would like a preview of what's covered in this class,
click here to watch the first and last lessons of this course (free of charge), or scroll down for more information.


Access Expert Level 23
Description: Access Expert Level 23
Versions: Recorded with Access 2013. Also use with 2007 and 2010. These lessons may or may not work with Access 2003.
Pre-Requisites: Access Expert Level 22 strongly recommended. This class builds on the import/export topics covered in the last couple of classes.
Running Time: 1 Hour, 40 Minutes
Cost: $26.99

Today we will begin by building a project that takes advantage of the data import/export lessons we've been studying over the last few classes. We will build a remote data entry database for our sales reps or service technicians to take on the road with them. They may or may not have Internet access while on calls, so we want them to be able to view customer information and previous contacts, and to be able to add new contacts while they're on the call. Then, when they return to the office, they can click one button to synchronize their records with the main office database (upload all new contacts, and download any new or changed customer information). It's a great solution for anyone in this situation. I'll use contacts in class, but the same technique can be used for orders, inventory, or whatever else you want to make available remotely.

remote data entry database


Next, we'll learn how to import bank transaction data into our database. This will allow you to maintain your own transaction listing, or perhaps build a check register database to track expenses. We will use data from Paypal in class, because it's a fairly popular, world-wide system. Every bank is different in the format of the data that they provide for download, but using the techniques in this lesson you should be able to manipulate things for your bank.

import bank data


In lesson three, we will learn about multi-field composite keys. These allow us to index values in multiple table fields. This means that you can prevent duplicate values across these fields. For example, you may have a bunch of stores that have to report daily sales figures. You wouldn't want the same store reporting sales for the same day twice. A composite key will prevent this.

composite keys


Finally, we're going to learn how to use XML files to export information from our database to be shared with other databases while maintaining the integrity of our relationships. For example, if you want to give someone else a list of all of your customers and their orders, we can use an XML export to give them our customer, order, and order details tables all in one file with the IDs and relationships preserved. This is very powerful for sharing data.

xml multi table export


This is the 23rd class in the Access Expert series. There are a lot of good, practical examples of how to use the import techniques of the previous few classes in this one - plus a lot more. Of course, if you have any questions about whether or not this class is for you, please contact me.


Complete Outline - Access Expert Level 23

00. Introduction (7:58)

01. Remote Copy For Data Entry (36:44)
Copy PC Resale Main Database
Customize for Remote Use
Lock Customer Form
Don't Allow Edits, Deletions, Additions
Only Allow Additions on Contact Form
IsRemote Yes/No Field
Link to Main Database Tables
Not Connected to Network Error Message
Append New Records to Server
Delete Data in Remote Database
Copy All Records from Server to Remote
Macro to Perform Synchronization

02. Import Paypal Transactions (25:37)
What is Paypal
Download Paypal History File
Import Paypal Data Into Access
Set up my Transaction Table
Prevent Duplicate Records
03. Composite Keys (15:44)
Prevent Duplicate Values Multiple Fields
Create Store Locations Table
Do Not Want Two Stores in Same City
Indexed, No Duplicates
Indexes Dialog Box
Create the Composite Index
Also Called a Compound Index
Make Sure Store Doesn't Report Sales Twice
Create StoreSalesT
Prevent Duplicate Store Sales Figures
Allowing Duplicate Values
Find Duplicates Query Wizard
SQL Statement Explained Briefly

04. Transfer Multiple Tables XML (7:23)
Send Multiple Tables with Related Data
Exporting Customers with Orders
Export XML Menu Select Tables
Import Tables Into a New Database

05. Review (7:29)


Keywords: Remote Data Entry, Import Paypal Transactions, Composite Keys, Transfer Relational XML, microsoft access tutorial, microsoft access tutorial, microsoft access training, access 2007, access 2010, access 2013, synchronize with main server, prevent or view duplicates, multi field keys, export customers with orders

Student Interaction: Microsoft Access Expert 23

Richard on 7/3/2014:  Microsoft Access Expert Level 23 is 1 hour, 40 minutes long. In this class we will build a database for remote data entry. This will allow our sales reps or service technicians to add contact records on the road. We will learn how to import bank transaction data. We will learn about multi-field composite keys, and how to transfer related tables using XML data. Topics include: - Remote Data Entry Database - View Info, Add Contacts Remotely - Synchronize With Main Server - Import Bank Transactions - Prevent or View Duplicates - Multi Field Composite Keys - Prevent Duplicate Sales Reports - Transfer Relational XML Data - Exporting Customers With Orders - Maintain Relationships via XML Files Click here for more information on Access Expert Level 23, including a course outline, sample videos, and more. This course was recorded using Access 2013, but is also valid for Access 2007 and 2010 users. This class follows Expert Level 22. The next class in the series is Access Expert 24.
B on 7/3/2014: Great class Rick!
To build off of your remote sales associate example adding contacts, what would you do in this situation (trying to fit it into the class example):
I want my sales associates to be able to enter contacts while on the road so they can be re-imported into my database. However, for compliance purposes the sales associates can't ever access my server for upload/refresh (even when not on the road), and again for compliance reasons a second server can't be created. However, I still need their inputted information, and still need them to be able to view certain client information in the database as well.
Any thoughts on this? In the past I've used simple excel imports, but the "sales associates" tend to go a bit wild due to the lack of input controls leading to data issues/cleanup (by me!), and also currently can't benefit from having certain information at their fingertips via the database. I realize this "compliance" issue ties our hands quite a bit-hoping you have some insight.
This import/data exchange portion of the series has been hugely applicable to a project I'm working on. Thank you!

Reply from Richard Rost:

Not giving them access to view existing data is easy - just don't let them download it. Give them a "data entry only" form where they can type in new contacts. If there is information that they CAN see, then just make a query to export just that data for them. If you're not allowed to create a "second server" then just export the info that they ARE allowed to view in an Excel file (or XML file if you need to keep relationships) like I showed in class. They can then import that.

B on 7/5/2014: Thanks Rick! That should work great for my purposes.
Hopefully the other students are getting as much out of this import/export segment of the series as I am-it has been a huge help for me! For those of us who don't have the luxury of having our developed system serve as "THE" system for our companies (due to compliance, other departments using other systems, etc.) getting data in and out cleanly-and the tips/best practices from your experience, has really been a true value. Please continue to give insight on "less than perfect database circumstances" in the classes!

Reply from Richard Rost:

Thanks :)

Larisa Kiseleva on 7/8/2014: Is it possible to set remote data in iPhone or ipad using Access 2013 Runtime Deployment?

Reply from Richard Rost:

Not that I know. Alex?

Alex Hedley on 7/9/2014: There are no Official MS Access apps for iOS.
When you say runtime, where is this installed?

- - -
One option would be to create a website that links to your db and the iPad can connect to it that way.
- - -
There a few posts about this:

Alex Hedley on 7/26/2014: If you are going to be adding and removing data often it might be worth creating a schedule to Compact and Repair your Database.

Check out this Glossary item explaining how and why.


Willem Els on 7/31/2014: Hi Richard, the video quality is very low.
look at the video 05:00

Reply from Richard Rost:

Willem, you're right. That video is a bit grainy. When I bounce around a lot (moving the screen from place to place, scrolling, etc.) that tends to happen - and I'm moving around A LOT there. I've actually been playing around with my video compression settings and the next couple of lessons (starting with X25) will be in a higher resolution.

Warren G on 8/15/2014: Hi Richard
I would be interested in learning how to edit records and then sync with the server.

Lucas on 9/12/2014: Hi Richard,
I would be interested in learning about how to synchronize data between the databases as discussed at 15:40.

michele finizio on 11/10/2014: Hi Richard, for your "European customer" could you teach the following issue while import data:
date : EU format for date is dd/mm/yyyy instead of mm/dd/yyyy. When we import date (like we have in that sample) we have problem to convert

Number with decimal: Some EU country use comma (",") instead of dot (".") to separate the decimals. When we import this kind of data we have problem


Reply from Richard Rost:

I'll be completely honest, I don't have a lot of experience working with Access in foreign country configurations. However, I believe that if someone enters a date on a computer in England, for example, where they have dd/mm/yyyy set up as their format, Access stores that value as a PROPER date/time. So if you send that Access database file to someone in the US, the dates should STILL appear correct because his computer's format is set to mm/dd/yyyy and these are DATES. Remember, the format of the date is set in WINDOWS in the Regional Settings.

The problem occurs when you import data that's stored in a TEXT FILE or some other non-Access (or non-Excel) file. When you pull that in to Access as text, now you have to do some massaging to get the digits in the right place. I've already showed you how to manipulate data like this using the STRING functions (left, right, mid) in Access Expert 25. If you've got:


And you need to convert that over to the US format, you'd say:

USDate: Mid(D,4,2) & "/" & Left(D,2) & "/" & Right(D,4)

This will give you:


As long as all of your values are in exactly the same format, this will work. Now you can wrap that in a CDate() function (which I just covered in Access Expert 26) to convert that to a real Date value.

Now create an UPDATE QUERY to copy all of these over into a new field (D2 for example) and then you can delete the original field once you've made sure it all copied over correctly.

Replacing commas with decimal places is just a simple matter of using the REPLACE function.

S = Replace(S,",",".")

This will replace all of the commas with periods in the string S.

Hope this helps.

Kenny Nelson on 4/15/2015: NOTE: This works great with queries also!
Jeffrey Ervin on 5/1/2015: To Many Problems with importing from paypal they have changed there interface or updated there website, not sure if thats an issue but maybe you may have a look and see if things have changed? The main error I get when i try to import and click finish is "The search Key was not found in any record"

Reply from Alex Hedley:

Are you downloading a cvs and importing it into Access?

vicki Hudson on 8/16/2015: I get an error with my Sync with Server event. everything is the same as in the video. the error is: "SELECT* cannot be used in an INSERT INTO query when the source or destination table contains a multi-valued field." It does complete the sync, appends to Server tables and deletes all info in ContactT and CustomerT.

Reply from Alex Hedley:

Can you try adding the Field names instead of * and see if that works.

Brian Jensen on 1/15/2016: I have a question as to why use an append query on the AppendCustomerServerQ and not a make table query? Wouldnt using an append only add new records to the remote DB and not update other customer's info? I would think we basically just want a copy of the backend customer list need to append.

Reply from Alex Hedley:

You are copying remote data back to your main db so you want to append the data from remote to your main db then delete it.
If you had a Make Table you would have two Tables your original and then a new, that would mean a UNION or other would need for showing all your data

Kelvin Yew Kuan T on 2/29/2016: I would be interested in learning how to edit records and then sync with the server.

Mark Bee on 3/17/2016: I've imported a cvs file from my HMRC payroll software and the data looks like below:

Employer's Name,,,Tax Year,,,Accounts Office Ref,,,PAYE Ref
Mark Bee,,,2015,,,238746guu3242,,,777/jjhs7733

Employee's Full Name,,,Employee's Address,,,National Insurance Number,,Payroll ID,,
Jo Bloggs,,,65 Some Road, Redlands Lane Fareham ,,,HG664545,,,,

Payment Date,,Payment Tax Period,,Payment NI Period,,Payment Taxcode Full,,Payment NI Letter,,Payment Pay Frequency

Payment Pay For Tax,,Payment Pay For NICs,,Payment SSP,,Payment SMP,,Payment SAP,,Payment ShPP,,Payment SPP

How do I get the data to come out like yours in your vid? Data changed to protect employee etc...


Reply from Alex Hedley:

Did you run it through the wizard?

Mark B on 3/22/2016: Hi, thanks for your reply. Yes I did use the wizard, and it wasn't able to seperate the field names and the data.

When I open the csv file on my desktop, it loads up in excell there are like 5 field names, then the data below, then spaces below that, then another row of field names, then under that more data.

Is there a way to be able to get all the field names in one row then the data underneath.

Any ideas?
many thanks in advance, Mark

Reply from Alex Hedley:

You'd need the data to be in a tabular format so you might need to change the method you export it to, it sounds like it's more of a page by page report instead of a table.
If you don't have any other options you will have to clean it up first, some macros/vba might be possible to automate removing extra lines etc


You may want to read these articles from the 599CD News:


Visual Basicindex
Account Login
Online Theater
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Live Chat
Customer Support
WalkThru Tutorials
Consulting Services
About Us
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Gift CDs
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP