Access 2007-2019
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  

News      User Comments     History     Notify Me

7/3/2014 11:27:11 AM
Microsoft Access Expert 23
By Richard Rost   Richard Rost on Facebook Richard Rost on Twitter Richard Rost on Google Plus Richard Rost on LinkedIn Email Richard Rost

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.

Permanent Link
Course Link: Microsoft Access Expert 23
Keywords: access expert 23 remote data entry composite keys import bank transactions xml
Page Tag: whatsnew
Post Reply

Import csv Comment from Mark B @ 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
Show Just This Thread        Post Reply
Import csv Comment from Mark Bee @ 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?
Show Just This Thread        Post Reply
sync with server Comment from Kelvin Yew Kuan T @ 2/29/2016
I would be interested in learning how to edit records and then sync with the server.
Show Just This Thread        Post Reply
Append Query Comment from Brian Jensen @ 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
Show Just This Thread        Post Reply
Error with my Sync with Server event Comment from vicki Hudson @ 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.
Show Just This Thread        Post Reply
PayPal Comment from Jeffrey Ervin @ 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?
Show Just This Thread        Post Reply
Comment from Kenny Nelson @ 4/15/2015
NOTE:  This works great with queries also!
Show Just This Thread        Post Reply
Converting European Dates Currency Comment from michele finizio @ 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.

Show Just This Thread        Post Reply
synchronize data between the databases Comment from Lucas @ 9/12/2014
Hi Richard,
I would be interested in learning about how to synchronize data between the databases as discussed at 15:40.
Show Just This Thread        Post Reply
Comment from Warren G @ 8/15/2014
Hi Richard
I would be interested in learning how to edit records and then sync with the server.
Show Just This Thread        Post Reply
Hi Richard the video quality is very low Comment from Willem Els @ 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.
Show Just This Thread        Post Reply
schedule to Compact and Repair Comment from Alex Hedley @ 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.

Show Just This Thread        Post Reply
Access with iPad iPhone Comment from Alex Hedley @ 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:
Show Just This Thread        Post Reply
Access with iPad iPhone Comment from Larisa Kiseleva @ 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?
Show Just This Thread        Post Reply
Remote Data Entry Comment from B @ 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 :)
Show Just This Thread        Post Reply
Remote Data Entry Comment from B @ 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.
Show Just This Thread        Post Reply

Add Your Comments or Subscribe
If you just want to subscribe to get email updates when the News is updated, then enter your name and email address and check the Notify Me box below. If you would optionally like to add your comments below to be posted, they are welcome.
  Your Name:  Required
  Your Email:  NOT Public


Sorry about this step. It's just to keep the spam bots away:
  Verify: What is 9+3:
  Notify me when the News is updated.
  Remember Me for my next comments
Please do not use this form for Customer Service inquiries! If you have questions about your account, shipping info, courses you've ordered, need passwords, etc. please use the Customer Service Center instead.

I value your comments. They will be displayed on this page (above). Your name will be displayed, but your email address will not be.
As always, I promise to never give away your personal information to anyone else, ever.

NOTE: If you don't leave your name and email address, DON'T expect a reply. I can't promise a personal reply to everyone who posts here. I TRY my best, but I cannot guarantee it. If you don't leave your real name and email address, I won't even bother. I usually just hit DELETE. -Richard



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

8/15/2019Last Chance for Access CDs
8/1/2019Customer List Form Template
8/1/2019New Template Access Customer Database
7/30/2019Microsoft Access Developer 13
6/20/2019TIP: Validation Rules in Access
11/20/2018Microsoft Access Developer 12
8/25/2018NEW: Access Dev 9, 10, 11
8/25/2018Microsoft Access Developer 11
8/25/2018Microsoft Access Developer 10
8/25/2018Microsoft Access Developer 9

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