Access DB Structure Problem

Posted on 2002-04-10
Last Modified: 2010-05-02
I have a VB6 front end reading from an Access 97 DB. The problem that I am running into may be multiple, then again it may not be. I am still learning how to use ADO, and this was my first task using it.
I was given an existing DB that had an Access front end, and I was told to convert it to a VB front end. The DB is one large table (approx 43 fields and growing). The first field is set to auto number, the next few fields are the typical contact information (Comp., Name, Address...). Then the last many (this keeps growing is why I say many) fields are all of the associated departments for each contact. I have wanted to break the DB up many times, but due to my lack of knowledge on how to do that I am uncertain as to where to begin. A contact can have many departments, and a department can have many contacts. I have even tried making the department fields actual records instead, but I am uncertain as how to associate them with each record.
The program actually works fine. I know before long it will start having problems if I do not fix it now. The only one that I have noticed so far is when there are multiple records of the same company with the same address, but a different contact person, when I view to see what departments are associated with that record it shows all of them for that company, and not just for that particular contact. If I change the DB structure will that fix this problem? Is it the way I am reading in the data?
Here is how load in all of the records initially:
Set connFile = New ADODB.Connection
Set rsDepartment = New ADODB.Recordset
connFile.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source= " & _
   App.Path & "\Christmas Card List.mdb; Mode = readwrite"
SQL1 = "SELECT * FROM [Departments]"
With rsDepartment
    .CursorLocation = adUseClient
    .CursorType = adOpenKeyset
    .LockType = adLockBatchOptimistic
    .Open SQL1, connFile, , , adCmdText
End With
'Populates the Listbox with the department names starting at the ninth column
For intCounter = 9 To rsDepartment.Fields.Count - 1
   lstNames.AddItem rsDepartment.Fields(intCounter).Name
Next intCounter
Like I said before this works great for all of the other records. Now here is how I read in to view what records are associated to the departments. Please note that all of the department fields are boolean.
Set rsStore = New ADODB.Recordset
rsStore.Open "SELECT * FROM Departments WHERE " & _
     "[Street Address] = True", connFile, , , adCmdText
Do Until rsStore.Fields("Street Address") = frmEnter.txtAddress.Text
For intCounter = 10 To rsStore.Fields.Count - 1
    If rsStore.Fields(intCounter).Value = True Then
        lstDepartments.AddItem rsStore.Fields(intCounter).Name
   End If
Next intCounter
In the above, the Street Address field is used because it is not a blank field, and I use it as a form of checking. I know that if the DB were broken up then I could match it other ways, since it is not, this is all I can do for now.
Since I work by myself, and have no one to bounce ideas off of except in places like this,  I need is a couple of things. First, how would I break a DB up like this one, and second, as far along as I am, how much recoding will it take to fix this? This is a pretty big program, and I know that eventually it will break if it is not fixed now.
Thanks in advance,
Question by:RogerH1
LVL 18

Expert Comment

ID: 6931710
You are correct in thinking that this is a bad structure and it's only going to get worse, and exponentially harder to work with.

You simply need to apply basic "relational" modeling to the structure.

You've already done this verbally by identifying the various "entities" and how they "relate" to each other.  So, given what you've said so far:

1             Bob Smith      555-1212 123 Main St.
2             Sally Jones    555-1212 345 Elm St.

66            Payroll        1256
39            Finance        8887

66            1            
39            2
66            2

Now, if the contacts are working for a specific company, then you probably need a company table, and then if contacts can only work for one company, you can put the COMPANY_ID column in the Contacts table.

To write the SQL for the code above you'd have queries like:

SELECT Departments.*
FROM Departments, Department_Contacts
WHERE Departments.DEPT_ID = Department_Contacts.DEPT_ID
AND Department_Contacts.CONTACT_ID = 2
order by DEPT_NAME

This would be to get all of the departments for a particular Contact.  Now, since you want to use the primary key (CONTACT_ID) as the field that you use to "join" the tables together, then it's helpfull when providing the user with a list of contact names, that you also store the CONTACT_ID.  The trick I use for this is to store the CONTACT_ID which is probably an autonumber field with a datatype of Long Integer in the combo or listbox's ItemData property.  Here is sample code for filling a combo:

While Not RS.EOF
   cboContacts.AddItem RS("CONTACT_NAME").Value
   cboContacts.ItemData(cboContacts.NewIndex) = RS("CONTACT_ID").Value

Then, in the combo's Click event, you can format the SQL using:

Dim sSQL as String

sSQL = "SELECT Departments.*"
sSQL = sSQL & " FROM Departments, Department_Contacts"
sSQL = sSQL & " WHERE Departments.DEPT_ID = Department_Contacts.DEPT_ID"
sSQL = sSQL & " AND Department_Contacts.CONTACT_ID = " & cboContacts.ItemData(cboContacts.ListIndex)
sSQL = sSQL & " order by DEPT_NAME"

LVL 16

Expert Comment

ID: 6931834
Good explanation mdougan!
LVL 100

Expert Comment

ID: 6931999
Excellent explanation

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.


Author Comment

ID: 6932179
Your description is great. In fact I started playing with it after I posted this message, and came up with a very similar layout as yours. The only difference is, the Department_Contacts also has a boolean field in it. I did this because I need to know what department is True or False for each contact. In your SQL statement I would make the WHERE statement at the end read "Department_Contacts.Boolean = True instead.

Now comes the big question:

How do I take an existing DB and make it into multiple tables? The problem is what is now fields will have to become records, and the boolean options below each of those fields will need to correlate to each of those records. I hope this all makes sense. The point is, will I have to basically recreate 680 records?  I hope that there is an easier way around this.

LVL 18

Accepted Solution

mdougan earned 100 total points
ID: 6932331

First off, there is no need to have records in the department_contacts table for contacts that are not in a department.

If you want to list contacts who are NOT in a department, your sql would be:

Select C.*
From Contacts C
Where not Exists
(Select D.Contact_ID
 From Department_Contacts D
 Where D.Contact_ID = C.Contact_ID

The result given the data above:
1             Bob Smith      555-1212 123 Main St.

If you want to list all departments a contact is NOT in:

Select D.*
From Departments D
Where not Exists
(Select DC.Department_ID
 From Department_Contacts DC
 Where DC.Department_ID = D.Department_ID

The result given the data above:
39            Finance        8887

If you want to select all departments and indicate with a "Y" or "N" if the contact is in that department:

Select D.*, "Y" as InDepartment
From Departments D
Where Exists
(Select DC.Department_ID
 From Department_Contacts DC
 Where DC.Department_ID = D.Department_ID


Select D.*, "N" as InDepartment
From Departments D
Where not Exists
(Select DC.Department_ID
 From Department_Contacts DC
 Where DC.Department_ID = D.Department_ID


So, you can see that you can display the Falses without having to store them in the database.  Only if you were going to be running queries like this over and over again would it be worthwhile to consider wasting the extra storage space in your database.

As far as populating the database, it's not too hard, except for the autonumber fields.  What you would do is first create your new tables as empty tables, and then in Access you'd create queries that are "append" queries.  You'd use your original table as the Source table, and then bring down to the query grid just the columns that correspond to the Contact (and for a second query the Department info) and Then under the Query menu, select the Append option.  Then, choose your new table name.  If your column names match in the old and new tables, then Access will automatically indicate which field in the new table will be populated by one of the fields in the old table that you brough down to the query grid.  But, you should double-check that the source and destination columns are correct.  Just make sure to leave out the column for the AutoNumber column

Once you run the query, your contacts table will be filled and all will have new keys.  Do the same for your departments table.

The only tricky part comes when you want to populate the department_contacts table.  If, say, the contact_name is unique and the dept_name is unique, you could have a query like this:

(this can be another append query)

If the name fields are not unique, then there is a way to use the primary key from the old table, but it's a little more work.......
LVL 18

Expert Comment

ID: 6932340
Oh, and if in your old table, you had 5 rows that had the same contact -- because that contact was in 5 departments, then in the Query, you are going to have to click on the "SUM" button, which will add a "Group by" clause to the query.  Just group by every contact field (for the contact query).  This will ensure that you only get unique contact records inserted into the table.

Author Comment

ID: 6934366
This definitely confirms what I was thinking. Plus it will help me with some of the SQL statements when dealing with multiple tables, which I have not dealt with before.

Thanks so much for your help,
LVL 18

Expert Comment

ID: 6935439

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question