Lessons

Resources

Questions?

Please feel free to post your questions or comments below. If you have questions about a specific lesson, please specify the lesson number and the time index in the lesson where the issue occurs. Thank you!

Subscribe for Updates

If you would like to be notified whenever changes are made to this page, new lessons are posted, or comments are made, click on the subscribe button.

Comments for Access Expert 23

Age	Subject	From
2 years	23 04	John Schreiber
2 years	Bank Transactions Import	John Schreiber
2 years	Lesson 23 02	John Schreiber
3 years	Lesson 1 AppendContactServerQ	Bryce Tervo
4 years	OrderF Wont Allow Additions	Kent Jamison
4 years	Remote Copy for Data Entry	Ronald de Boer
5 years	Error message in Remote copy	Robert Taplin
10 years	Import csv	Mark B
10 years	Import csv	Mark Bee
10 years	sync with server	Kelvin Yew Kuan T
10 years	Append Query	Brian Jensen
11 years	Error with my Sync with Server event	vicki Hudson
11 years	PayPal	Jeffrey Ervin
11 years	No Subject	Kenny Nelson
12 years	Converting European Dates Currency	michele finizio
12 years	synchronize data between the databases	Lucas
12 years	No Subject	Warren G
12 years	Hi Richard the video quality is very low	Willem Els
12 years	schedule to Compact and Repair	Alex Hedley
12 years	Access with iPad iPhone	Alex Hedley
Next >>

Subscribe to Access Expert 23
Get notifications when this page is updated

Intro

In lesson 23, you will learn how to set up remote data entry in Microsoft Access by creating a separate database for users to work with offline and then synchronize their changes when they return. I will show you how to import transactions from PayPal and prevent duplicate data during imports using transaction IDs and queries. We will also cover composite keys, which allow you to prevent duplicate records in a table based on multiple fields. Finally, you will see how to export and import multiple related tables using XML to transfer data between databases.

Transcript

Welcome to Microsoft Access Expert Level 23, brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.

In today's class, we will begin by learning about remote data entry. This is where we'll build a second database that our sales reps or our techs can take on the road with them on their laptops. Whether or not they have internet access, they can enter new contact records, sales records, service records, or whatever is needed while they're on the road. Then, when they get back to the main office, they can synchronize that information with the main server.

Next, we'll learn how to import transactions from a banking website or a credit card website. The example that we'll use in class will be PayPal because it's pretty universal, whereas most banks can be slightly different in their formats. We'll learn how to prevent importing the same data twice using the transaction IDs. We'll also see how to set up a query to catch duplicate entries.

Next, we'll learn about composite keys. We've learned about primary and single keys before, which allow you to prevent duplicates based on one field. A composite key allows you to prevent duplicate data in your table based on multiple fields. For example, if you have multiple stores and each store has to submit a daily sales report, you wouldn't want to have two reports from the same store on the same day. That's where a composite key will help.

Finally, we've seen how to import and export data based on one table. In this lesson, we're going to see how to export and import multiple tables with their related information using XML. This is great if you want to send someone information from your database who may or may not be using Access, and you have multiple related tables that you need to send them.

This class follows Access Expert 22. In that class, we covered fixing non-relational data, updating our vendor pricing based on new information, and fixing our order entry system. It's strongly recommended that you watch that class and all the ones before it before continuing on with today's class.

I will be using Access 2013 in today's class. What is covered today should work just fine with Access 2007 and 2010. Of course, if you come across something that is significantly different, please let me know.

The material in today's class is completely new. I've never covered this before in my 2003 series. If you're using Access 2003, you can try to follow along with the lessons covered in today's class, but I can't guarantee that everything will work just fine. If you're using 2003 or earlier and you don't want to upgrade to 2013, which I strongly recommend, and you're having problems, please feel free to contact me and I'll do my best to walk you through it. I don't have a copy of 2003 still installed on any of my machines, but I do kind of remember what the menus looked like. I'm pretty sure most of the material in today's class should work with some minor tweaking.

My courses are broken up into beginner, expert, advanced, and developer level classes. Beginner level classes are for novices. You should understand all the topics covered in them by the time you get to the expert level classes, which you're in now. When you finish all the expert level classes, the advanced classes will cover event programming and macros, and the developer classes will cover Visual Basic for Applications. Each group of classes is broken down into multiple levels, Level 1, 2, 3, and so on.

In addition to my normal Access classes, I also have seminars designed to teach specific topics. Some of my seminars include building web-based databases, creating forms and reports that look like calendars, securing your database, working with images and attachments, writing work orders and running a service business, tracking accounts payable, learning the SQL programming language, creating loan amortization schedules, and lots more. You can find details on all of these seminars and more on my website at accesslearningzone.com.

If you have questions about the topics covered in today's lessons, please feel free to post them in my student forums. If you're watching this course in the online theater on my website, you should see the student forum for each lesson appear in a small window next to the class video. Here you will see all of the questions that other students have asked, as well as my responses to them and comments that other students have made. I encourage you to read through these questions and answers as you start each lesson and feel free to join in the discussion. If you are not watching these lessons on my website, you can still visit the student forums later by visiting accesslearningzone.com/forums.

To get the most out of this course, I recommend you sit back, relax, and watch each lesson completely through once without trying to do anything on your computer. Then, replay the lesson from the beginning and follow along with my examples. Actually, create the same database that I make in the video, step by step. Don't try to apply what you're learning right now to other projects until you've mastered the sample database from class.

If you get stuck or don't understand something, watch the video again from the beginning, or tell me what's wrong in the student forum, and I'll do my best to help you. Most importantly, keep an open mind. Access may seem intimidating at first, but once you get the hang of it, you'll see that it's really easy to use.

I strongly encourage you to build the database that I build in today's class by following along with the videos. However, if you would like to download a sample copy of my finished database file, you can find it on my website at accesslearningzone.com/databases. Sometimes, if you get stuck, the easiest way to learn is to tear apart someone else's database. One of the ways that I taught myself Access years ago was by tearing apart the Northwind Traders database that comes with Microsoft Access. You'll find there's a sample database for each of my courses on my website.

Now, let's take a few minutes and go over exactly what we're going to cover in today's class.

In Lesson 1, we're going to create a remote copy of our database that users can use for data entry to add new contacts and such while they're not in the office and don't have an internet connection.

In Lesson 2, I'm going to show you how to import PayPal transactions into your Access database.

In Lesson 3, we're going to learn about composite keys, which is creating a key for a table that's based on multiple fields. You can prevent duplicate values across multiple fields in the same table, for example, having a store with the same city and state as another store.

So far, all of our importing and exporting has dealt with single-table data, one table at a time. In Lesson 4, I'm going to show you how to transfer multiple tables, in other words, tables with related records from other tables like customers and orders, using XML.

Quiz

Q1. What is the primary purpose of building a remote copy of a database for sales reps or techs?
A. To allow offline data entry and later synchronization with the main server
B. To reduce storage space on the server
C. To create backup copies of the database automatically
D. To test new features before deploying to the main office

Q2. When importing transactions from a banking or credit card website like PayPal, what is used to prevent importing the same data twice?
A. The amount of the transaction
B. The transaction ID
C. The customer's name
D. The date of the transaction

Q3. What function do composite keys serve in a Microsoft Access table?
A. They allow duplicate values in a table
B. They prevent duplicate data based on multiple fields
C. They sort records in a table automatically
D. They encrypt sensitive data

Q4. What is the benefit of using XML to export and import multiple related tables?
A. It reduces the file size dramatically
B. It can transfer multiple related tables with their information, even to users without Access
C. It converts all data to plain text only
D. It allows import into Excel only

Q5. Why does the instructor recommend not immediately applying concepts from the tutorial to other projects?
A. The concepts are outdated
B. The sample database must be mastered first for proper understanding
C. Other projects might contain viruses
D. Access only allows one project at a time

Q6. According to the video, what level will cover event programming and macros in more detail?
A. Beginner level
B. Expert level
C. Advanced level
D. Developer level

Q7. If using Access 2003 or earlier, what does the instructor recommend if you run into problems with the lessons?
A. Stop using Access
B. Upgrade immediately to Access 2013
C. Contact him for help and try to follow along with some adjustments
D. Only use the Northwind Traders database

Q8. What method does the instructor recommend for learning if you get stuck?
A. Continue to the next lesson anyway
B. Watch the video again or ask questions in the student forum
C. Hire an outside tutor immediately
D. Skip the difficult section

Q9. Where can you find the sample databases used in the instructor's courses?
A. At accesslearningzone.com databases
B. Included with your Access software by default
C. In the Access help menu
D. On Microsoft's main website

Q10. Which of the following is NOT a seminar topic offered by the instructor?
A. Building web-based databases
B. Securing your database
C. Learning VBA for Excel only
D. Creating loan amortization schedules

Answers: 1-A; 2-B; 3-B; 4-B; 5-B; 6-C; 7-C; 8-B; 9-A; 10-C

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Access Learning Zone covers several advanced topics in Microsoft Access that will help you manage and work with your data more effectively. I'm Richard Rost, your instructor, and in this lesson, we're going to build on concepts that you've learned in the previous courses, especially Access Expert 22. If you haven't been through those earlier classes yet, I recommend you go back and review them first. They lay the groundwork for what we'll cover today.

We'll start with remote data entry. This involves setting up a second database that your sales representatives or technicians can take with them on their laptops while they're on the road. Even if there is no internet connection available, they can continue entering new contact records, sales records, service records, or whatever information they gather while they're away from the office. When they return, they'll be able to synchronize this new data with your main Access server at the office, ensuring your records stay up to date.

Moving forward, I'll demonstrate how to import transaction data from an online banking or credit card website. For our example, we'll use PayPal, since its transaction exports are commonly used and formatted in a way that's relatively easy to work with. You'll learn how to import this file into Access and set up the system to avoid importing the same transaction more than once by relying on the unique transaction ID. I'll also show you how to create a query that identifies duplicate entries, which is very helpful in managing your data accuracy.

Next, we'll explore the concept of composite keys. By now, you're familiar with primary keys that enforce uniqueness based on a single field. A composite key extends this idea and allows you to impose uniqueness across multiple fields. For example, if you have several retail locations and each one must submit a daily sales report, you want to ensure that no two reports from the same store on the same date are entered. Composite keys enable this level of validation in your tables.

Finally, we've covered single-table import and export in earlier classes, but today, I'll show you how you can transfer multiple related tables at once using XML. This is particularly useful when sharing information with others who might not use Access, or when you need to distribute related data contained in multiple tables.

This class uses Access 2013, though the material should be compatible with Access 2007 and 2010. If you notice anything that works differently or runs into issues, I invite you to let me know. For those of you working with Access 2003 or earlier, most of these techniques should still work, though some features and menu locations might differ. If you have trouble following along and are unable to upgrade to a newer version, please reach out and I will do my best to assist you.

My courses are structured in four levels: beginner, expert, advanced, and developer. By the time you're working through expert classes, you should have a solid foundation in the basics. The advanced courses will teach event programming and macros, while developer level topics will get into Visual Basic for Applications. Each level contains several classes to help you grow your skills progressively.

Beyond the regular course sequence, I offer seminars focusing on specific Access topics like web databases, calendar forms and reports, database security, images and attachments, work orders and service business management, accounts payable, SQL programming, and more. You can find full details on these seminars on my website.

If you have questions about today's lessons, feel free to visit the student forums on my website, where each lesson has a dedicated forum area for questions, answers, and discussion. If you are watching this course at AccessLearningZone.com in the online theater, you'll see these forums right alongside each lesson. Even if you are viewing the course elsewhere, you can always access the forums on my website and join the discussion with other students and myself.

To make the most of this course, I suggest watching each lesson all the way through before trying things out on your own. Once you've done that, rewatch the lesson and follow along step by step, building your own version of the sample database. Focus on practicing these exact examples before applying the concepts to your own projects. If you confront any confusion or run into obstacles, watch the lesson again from the start or reach out for help in the forums. Keeping an open mind is important, and with a little patience, you'll see that Access becomes easier as you learn more.

I recommend that you build out the database alongside me as seen in the videos. If you would rather study a pre-built example, downloadable copies of the finished databases for each course are available at my website. Looking through and modifying a working database can be a powerful way to learn, much as I did years ago by studying the Northwind Traders sample database that comes with Access.

Let's briefly review the material that will be covered in today's class. In Lesson 1, we will create a remote copy of the database, enabling out-of-office data entry of new contacts and other information even without internet access. Lesson 2 demonstrates how to import PayPal transactions into Access. In Lesson 3, we will cover composite keys and see how to enforce uniqueness across multiple fields, such as preventing two records for the same store on the same day. Lesson 4 explores importing and exporting multiple tables with related records, using XML for data sharing beyond single tables.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Creating a remote Access database for offline data entry
Synchronizing remote data with the main server after travel
Importing PayPal transactions into Access
Preventing duplicate transaction imports with transaction IDs
Setting up a query to catch duplicate entries
Creating and using composite keys in Access tables
Preventing duplicate records using composite keys
Exporting and importing multiple related tables using XML