Archive for August, 2010

August 24th, 2010

MS Access – Splitting and Deploying Your Database

Splitting and Deploying Your Database

So you’ve developed a database and now you are finally ready to deploy it to your users! There are a couple things to consider to simplify your life as much as possible.

 

Split Your Database

Split? Yes, if you have not done so, and you are setting up a network or multi-user database, it is time to split your database. This is a simple process by which the tables are placed into one database (called the Back-End) and the remaining database objects (queries, form, report, code) are placed into another database (called the Front-End). The Back-End is then placed on the office server and a copy of the Front-End is given to each user and setup on their PC. DO NOT allow your users to all connect using the same Front-End file, they should each have their own copy on their machine.

 

Why Split your Database

There are numerous reasons/advantages to splitting your database, such as:

  1. Performance – Since the Front-End is located on each user PC, it can have significant benefits in overall performance.
  2. Reduced Network Traffic – Since the Front-End is located on each user PC there is that much less data to transfer back and forth over the network.
  3. Maintenance/Updating – Splitting the database simplifies the process of performing updates. By creating update scripts, you can in effect simply replace the front-end and automatically upload it to your users while never having to take the database offline.
  4. Reduces Corruption – It has been stated that splitting your database can reduce data corruption.
  5. Multi-User – Splitting your database permits multiple-user simultaneous usage of your database.

 

How-to Split Your Db (Access 2000-2003)

Tools->database utilities->Database Splitter  

 

How -to Split Your Db (Access 2007)

Database Tools -> Move Data -> Access Database  

 

Database Splitting References

Split your Access database into data and application
Splitting a access database, or how to run ms-access in a multi-user mode
Splitting Microsoft Access Databases to Improve Performance and Simplify Maintainability

 

Creating a Persistent Connection

Once you split your database, it is very imporant to create a persistent connection between your Front-End (FE) and your Back-End (BE) to ensure optimal performance of your database.  To do so:

In your back-end (BE)

  • Create a bogus table in your back-end with 1 field, then add 2-3 entries into the table (keep it simple, but the table name and actual data have no importance).

In your front-end (FE)

  • Create a new linked table based off of the newly created bogus table (step above)
  • Create a form that use's that table as it's recordsource
  • In your database startup routine, most probably AutoExec Macro, open the form in a hidden mode as soon as your db launches

Share and Enjoy

  • Google Plus
  • Facebook
  • LinkedIn
  • Twitter
  • Email
  • Print

CategoryMS Access, MS Access General Information | Comment1 Comment »

Tags:
August 24th, 2010

MS Access – Multiple Criteria DLookup

A common question in many a newsgroup/forum is how can I perform a DLookup with more than one criteria? Well, the fact of the matter is that the Criteria permits you to specify as many criterion as you please.

For instance, lets say we have a contact table named ‘tbl_contacts’ and we want to retrieve the Telephone Number (field TelNo) for Daniel (Field FirstName) Pineault (field LastName), then the code would look something like:

DLookup("[TelNo]", "tbl_contacts", "[FirstName]='Daniel' AND [LastName]='Pineault'")

Now let’s push this example a little further. Let’s assume instead of wanting to hard code the individual’s name, we want to pull the value from form controls. So let assume we have a form name ‘frm_contacts’ with controls named ‘txt_FirstName’ and ‘txt_LastName’, in that case the code would look something like:

DLookup("[TelNo]", "tbl_contacts", "[FirstName]='" & Forms![frm_contacts].Form.[txt_FirstName] & "' AND [LastName]='" & Forms![frm_contacts].Form.[txt_LastName] & "'")

Special Note

One important thing to properly grasp when building a criteria is how and if they need to be surrounded by any special characters.

  • Date values need to be surrounded by #Your Date Value#
    • “[DateField]=#10/17/2009#”
  • Text values need to be surrounded by ‘Your Text Value’
    • “[TextField]=’Daniel’”
  • Numeric values do not need to be surrounded by anything
    • “[NumericField]=200″

Share and Enjoy

  • Google Plus
  • Facebook
  • LinkedIn
  • Twitter
  • Email
  • Print

CategoryMS Access General Information, MS Access VBA Programming | CommentNo Comments »