Client Server Microsoft Access

As you might (or might not) know, you can use Access's built-in Database Splitter to split any existing database into two separate databases (two separate MDB files). The Database Splitter takes you through the steps necessary to split the database. In the process, the wizard creates a database that contains only the tables, giving it the same filename as the original database file, appended with _be.

For example, say you split a database file named MOM.mdb using the Database Splitter. After the database is split, you end up with a file named MOM_be.mdb, which contains all of that database's tables. You also still have your original MOM.mdb database containing all the original queries, forms, reports, pages, macros, and modules.

However, MOM.mdb won't contain any tables. Instead, the database window shows links to external tables. In the database window, each linked table has an arrow to the left of its icon, as in the example shown in Figure 13-1. Pointing to a linked table's icon or name displays the table's true location in a screen tip at the mouse pointer.

Figure 13-1:

Arrows next to table names indicate linked tables.

j MOM 2003 : Database (Access 2002 2003 file format)

"-Q0S

:{J Reports

Gt^.MAS

¿¡J Create table in Design view ->3 My Business

11 Create table by using wizard >>!ZI Order Details

Create table by entering data -frilll Orders >>3 CountryLookup Products

>>3 CurrentNames «>3 Sales Tax Rates Customers

■fd Email Messages Table . u

C :\5ecureData\MOMBacl€nd .rndb]

StateLookup

Splitting the database into two lets you keep the back end separate from the front end. The back end is the database file that contains only the tables. On a network, you can place the back-end MDB file on any shared folder in a network so that all computers in the network can get to the tables.

After you place the back-end database in a shared folder, the next step is just a matter of opening the front-end database to make sure that it can find the linked tables. If you've changed the location of the back-end database since splitting the tables, you can use the Linked Table Manager to reestablish a link with those tables at any time.

Getting back to the MOM.mdb and MOM_be.mdb example, say you put MOM_ be.mdb in a shared folder named MOMFolder on a computer named Homie. Then you open the front-end database, MOM.mdb, on some computer other than Homie. If the links to the back-end database fail, just choose ToolsO Database UtilitiesOLinked Table Manager from the Access menu bar to the new location. In this example, that would be \\Homie\MOMFolder\MOM_ be.mdb.

When not to split a database

Splitting a database isn't something to be taken lightly, just for the heck of it. After you split a database file into two, changing or deleting a field in a table becomes a real headache. You have to open the back-end database table and change the field there. Furthermore, Name AutoCorrect won't be able to propagate a field name change through other objects as it normally would. So you might have to manually change the same field name in several objects in the front-end database file.

Splitting a database across multiple computers also slows things down because now there's the extra step of transferring all data to and from tables over the network. To keep life simple and not slow things down while you're trying to create a database, keep your tables, queries, forms, reports, macros, and modules all in one MDB file. Don't even think about splitting things until you've created everything you need — and are sure that everything is working.

If you want to try it, start with a copy of some database (so you still have the original un-split database available to you). Open that database with Access in the usual manner, and then choose ToolsODatabase UtilitiesODatabase Utilities. For more information on the whole shebang, search Access Help go to or http:// search.microsoft.com and search for the keywords Database Splitter, Linked Table Manager, and Name AutoCorrect.

After you reestablish the links, you can install the front-end database on any computer in the network that has Microsoft Access installed (up to a maximum of five simultaneous users). For example, Figure 13-2 shows how the back-end database is installed on one computer, which acts as the server by serving table data to all who request it. Each of the other computers has a copy of the MOM.mdb front-end database installed, so each of those computers has access to exactly the same back-end tables.

Everything I've discussed so far can be done without any VBA at all. The Database Splitter and Linked Table Manager tools are both right on the Tools menu in Microsoft Access. After you establish a valid link between the frontend and back-end databases, everything else is automatic. As far as queries, forms, reports, macros, and modules go, a linked table is no different from a local table (a table that's actually in the current database, not just linked).

Here is 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 on an as-needed basis. For example, you can attach code to a form's On Load and On Unload events to interact across the network only while that form is open or only at the moment when the data is required.

Back-end server

(tables only)

Microsoft Access and MOM be.mdb

Figure 13-2:

Front-end clients and back-end

Microsoft Access and MOM be.mdb

Figure 13-2:

Front-end clients and back-end server

Front-end f clients

Microsoft Access and MOM.mdb

Front-end f clients

Microsoft Access and MOM.mdb

To illustrate the various techniques shown in this chapter, I use an example of a single Access MDB file named MOMSecure.mdb. 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.mdb from any other Access database. (Most of the code that follows is copy-and-paste stuff. In most code, you'll need to change the path to reflect the actual location of your own external MDB file.)

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

server

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 database window and choose Cut.

2. Press Ctrl+V or right-click some empty space in the database window 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 keep things relatively simple, say that MOMSecure.mdb contains only two objects: one table and one query. The table's name is CCSecure; its structure is shown at the left side of Figure 13-3. The figure also shows some sample data in that table, in Datasheet view.

By the way, the names and credit card numbers shown are entirely fake. Don't bother trying to use them to shop online.

Figure 13-3:

CCSecure table in Design and Datasheet

_J CCSecure : Table

"EBB

Description

_J CCSecure : Table

Description

ContactID CCPreferred

Number Text

Foreign key to Customers table Amex, Master Card, Visa

CCHolder

Text

CCAcctNumTxt CCExpireMon

Text Number

Account number (stored as text) Expiration month

CCExpireYr

Number

Experiation year

CCcidCode

Number

CID code

Field

Properties

General Lookup Field Size Format Decimal Place:

Long Integer

General Number

General Lookup Field Size Format Decimal Place:

Long Integer

General Number

CCSecure Table (Design view)

CCSecure table (Datasheet view)

Input Mask ÇaÊtïijrç E-v^M Value Validation Ruit Validation Te*

Itrde^d Smart'Tags

¡CantactlD EOPreferíád

Record: M

Scolder | CBAtttNumflg 111 Magier.Card Tori Pinea I969T836B902743-

Marilou Midcalf 84961554525379 Wilma Wannabe 27494592338566.. FraMy Unciljoua 7183570604345 NancyO'Hara ¡773T976263732: HankAMatilda Slat 17436024217957 Scott and Matalife Sc 41570844360464. Linda PfeiWsdn 78591423318948 Ino Yasha 57744520389922

Rodney Robarts: 20261749426737 Dominic Kryzwickl 6811997990232

SSfiipireMDn I ftp.gkpiréYt Efeidg.adë;!

13 Annex 16 Ame» myisa

■sàVisa 26 Mastef.tCard MasterCard

28 Visa

29 MaafaSrd 3QI Visa

31 Mastei'Oard

2007

2008 200$ 20BB| 2007 2006 2007 2006 2009 2009

1 EM

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.

^ CCPreferred: Contains the name of a preferred credit card, such as Amex, Master Card, or Visa.

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

^ CCAcctNumTxt: Stores the card account number as text: for example,

1234567898765432.

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

^ CCExpireYr: The year when the card expires, as an integer: for example, 2005, 2006, and so forth.

^ 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, I 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([CCAcctNumTxt],4)

Figure 13-4 shows the CCSecureQry query both in Design and Datasheet views. I 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 the 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.

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

CCHint:"xxxx-xxxx-xxxx-"+Right([CCAcctNumTxt],4) CCSecureQry (Design view)

Figure 13-4:

CCSecure Qry in C:\Secure Data\MOM Secure, mdb.

CCHint:"xxxx-xxxx-xxxx-"+Right([CCAcctNumTxt],4) CCSecureQry (Design view)

Figure 13-4:

CCSecure Qry in C:\Secure Data\MOM Secure, mdb.

CCSecureQry Datasheet view)

In a sense, you've turned the MOMSecure.mdb 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. I suppose you could call it your customer credit card information server. I 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 nor VBA.

So how can any Access database on the network reach into MOMSecure.mdb 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:

1 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 is a local table and won't reflect any changes made to the source table since the snapshot was taken.

1 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 will be reflected in the linked table.

1 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, like when using that data for a single VBA procedure only.

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.

0 0

Post a comment