Basic facts about the raw data in MS Access format

This article provides some basic facts about the raw data in MS Access format. It's also helpful to those who acquire the database in other standard format such as MS SQL Sever, mySQL, Oracle. 

NOTE: Data in MS Access format are provided without support and the user are assumed to be of necessary skills on MS Access or similar database environment. This article is only to provide some basic ideas and you may need consult your computer assistant for further help.

1) System requirement

2) Contents and relationships

3) Basic operations & samples

System requirement

Currently we provide raw data in MS Access 2000 format and your system must match following requirements:

a) Windows 95/98/ME/2000/XP/2003 Server or later operation system
b) 128MB RAM or more
c) Sufficient hard disk space, depends on the size of the database, some database may require 5GB or more hard disk space
d) Microsoft Access 2000 or later version installed.

(MS Access support 2GB maximum only and you need more advanced database system, such as mySQL, MS SQL Server (Standard edition or up), Oracle, to handle some databases in a whole. In this case, the system requirement may vary dramatically depends on the database system you have)

Since most MS Access file (MDB) provided are compressed in ZIP format, you may also need a unzip tool such as WinZip, PKZip to open the achieve. 

Contents and relationships

Usually the MDB file contains of following files (tables):

a) comps (dbo_comps): list of company entries
b) paths (dbo_paths): list of categories 
c) dirs (dbo_dirs): hierarchical list of the categories
d) links (dbo_links): list of category_company relationships
e) fielddefs (dbo_fielddefs): a list of fields in the comps table 

The "fielddefs" table may be omitted in some simple databases.

The relationship between those tables can be described by following diagram:

* The keys in each table are shown in Bold, all keys are of integer type
** dbo_paths, dbo_paths_1 are the same table

Basic operations & samples

Here are some basic operations and sample code

1) list specified companies 

You can just query against the comps (dbo_comps) table to get such a list:

select * from dbo_comps where title like 'ABC*'  
-- starting with "ABC"

select * from dbo_comps where state='TX' and fax>''
--companies from Taxes with fax numbers 

2) list companies in a certain category
You need join the "path" table , the "link" table and "comps" table to generate such a result.

select * from dbo_paths where title='Restaurant'
-- list category of "Restaurant"

select c.* from dbo_comps  c, dbo_links l, dbo_paths p
where c.id=l.id and l.parent=p.id and p.title='Restaurant'
--list all companies under the category "Restaurant"

3)list all companies in a certain category and its sub categories (may take long time to proceed)

select c.* from dbo_comps  c, dbo_links l, dbo_paths p
where c.id=l.id and l.parent=p.id and p.path like '/Restaurant/*'
-- list all companies under the category "Restaurant" and its sub categories such as "Restaurant / Chinese", "Restaurant / French", ...

Submit a problem

Please use the contact form to submit us your questions, problems.