Backend server

(tables only)

Microsoft Access and MOM.accdb

Here's a downside to the whole business of splitting the tables from the other objects: network traffic. It takes time to get things across a network. The heavier the traffic on the network, the longer it takes.

You might have situations where a certain external table needs to be accessed only occasionally. Perhaps only a snapshot of some data is all that's required. In such cases, you can use VBA to open and close external links as needed. For example, you can attach code to a form's On Load and On Unload event procedures to interact across the network only while that form is open or only at the moment when the data is required.

Changing linked tables back to local tables

To convert a linked table back to a local table, do the following:

1. Right-click the linked table's icon in the Navigation pane and choose Cut.

2. Press Ctrl+V, or right-click some empty space in the Navigation pane and choose Paste.

3. In the Paste Table As dialog box that opens, type the original table name (same as the linked table's name), choose Structure and Data (Local Table), and then click OK.

To illustrate the various techniques shown in this chapter, we use an example of a single Access ACCDB file named MOMSecure.accdb. To keep names short, this table is stored in a folder named SecureData on drive C:. Thus, the path to the database file is C:\SecureData\MOMSecure.accdb from any other Access database. (Most of the code that follows is copy-and-paste stuff. In most code, you need to change the path to reflect the actual location of your own external ACCDB file.)

If MOMSecure.accdb were in a shared folder named SecureData on a computer named Max in a local network, the path would be \\Max\Secure Data\MOMSecure.accdb from any Access database on the LAN.

To keep things relatively simple, say that MOMSecure.accdb contains only two objects: one table and one query. The table's name is CCSecure; its structure is shown in Design view on the top-left side of Figure 13-3. The figure also shows some sample data in the CCSecure table, in Datasheet view.

Disclaimer: All the names and credit card numbers shown in these figures are fictional, and any resemblance to real people or credit card numbers is purely coincidental. (So don't bother trying to use them to shop online.)

As you might have guessed already, the CC in the field names is short for credit card. Here's a quick overview of the purpose of each field in the CCSecure table:

^ ContactID: A Long Integer that relates each record to a specific customer in a separate Customers table. ContactID is the foreign key here in the CCSecure table and the primary key in the Customers table.

Figure 13-3:

CCSecure table in Design and Datasheet views.







Number Number Text

Description Foreign K,eyto ÇLf^smers table Master card, '.'ifj, etc.. ^arne'fotvcredlt'&ardj Amount ^fiibertgtor'ed asTe'xt] Exflnration Month ExpirationYegf


. = X

ContactID ' CCPreferred -



~ CCExpireMo »

CCExpireYei *•

CCcidCode -

11 MasterCard

Tori Pines





13 Amex

Marilou Midcalf





16 Amex

Wilma Wannabe





17 Visa

Frankly Unctuous





22 Visa

Nancy O'Hara






1 Record: H lof5 ► M h |

tei 1 Search

ii CCPreferred: Contains the name of a preferred credit card, such as AMEX, Master Card, or VISA.

i CCHolder: Contains the cardholder name as it appears on the card.

i CCAcctNumText: Stores the card account number as text: for example,


i CCExpireMon: The month when the card expires, as an integer (1-12).

i CCExpireYr: The year when the card expires, as an integer: for example, 2007 or 2008.

i CCcidCode: The three-digit CID code that appears on the back of the credit card.

The second object of MOMSecure is a query named CCSecureQry. For this example, we created a query named CCSecureQry. This query displays all records from the CCSecure table except the account number and CID fields. In place of the account number is a calculated field named CCHint, which is a calculated control based on the expression shown here:

CCHint: "xxxx-xxxx-xxxx-" & Right([CCAcctNumText],4)

Figure 13-4 shows the CCSecureQry query in both Design and Datasheet views. We omitted the CCcidCode field from the query for no particular reason other than to have an example of leaving fields out of a query. As you can see in Datasheet view, the CCHint field displays xxx-xxx-xxx-1234, where 1234 is the last four digits of the account number. Thus, the query is hiding some information from the CCSecure table.

Figure 13-4:

CCSecure Qry in C: \Secure Data\MOM Secure. accdb.

CcritactlD CC Preferred

Figure 13-4:

CCSecure Qry in C: \Secure Data\MOM Secure. accdb.

CcritactlD CC Preferred

soc-" & R.i g ht([CC AcctN umText], 4)


. E. X

Contact ID - CCP referred -



- : CCExpireMo

- I CCExpireYea -

11 MasterCard

Tori Pines



3 2008

13 Armex

Marilou Midcalf



6 2007


1 2009

17 Visa

Frankly Unctuous



.11 2007

22 Visa

Nancy O'Hara



121 2007



cord: H 4 lof5 ► M K- | .


CCAcctNumText is a text field, so you use the expression Right([CCAcctNumText],4) to refer to the last (rightmost) four characters of that field's contents.

In a sense, you've turned the MOMSecure.accdb database into a little black box from which you can zap some credit card info out of a query (or the table, if need be) from any other Access database in the network. We suppose you could call it your customer credit card information server. We also suppose you can't call it your secure server right now because making it secure would require some close encounters of the network administration kind, which has nothing to do with Access or VBA.

How can any Access database on the network reach into MOMSecure.accdb and grab data, even when that database is closed? Here are the three answers to this question, and you can use whichever method seems most appropriate to the occasion:

I Import (a snapshot): You can import a snapshot of a table or query, storing it as a table in the current database. The imported table becomes a local table and doesn't reflect any changes made to the source table since the snapshot was taken.

I Link: You can create a link to any table in any external database. This type of link is identical to that created by the Database Splitter. Changes made to the source table are reflected in the linked table.

I Recordset: You can create an ActiveX Data Objects Database (ADODB) recordset of any table or query from an external database. Recordsets are useful when you need only a brief snapshot of external data, such as when you're using that data for only a single VBA procedure.

Each of the methods has its pros and cons. Which method is most appropriate at any given time depends on the situation. If the situation calls for a quick snapshot of current data, you can import data. If the situation calls for an open link to the table, like when both tables need to be up-to-date with each other, you need either a link or a recordset. Start with the easiest scenario first — the quick-zap grab of a snapshot of current data.

Was this article helpful?

0 0

Post a comment