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.

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.

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.

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.

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 |