Link to home
Start Free TrialLog in
Avatar of RogerH1
RogerH1

asked on

Access DB Structure Problem

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
 
rsStore.MoveFirst
 
Do Until rsStore.Fields("Street Address") = frmEnter.txtAddress.Text
    rsStore.MoveNext
Loop
 
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
    rsStore.Close
 
==============================================================
 
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,
Roger.
Avatar of mdougan
mdougan
Flag of United States of America image

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:

Contacts:
CONTACT_ID    CONTACT_NAME   PHONE    ADDRESS
1             Bob Smith      555-1212 123 Main St.
2             Sally Jones    555-1212 345 Elm St.


Departments:
DEPT_ID       DEPT_NAME      COST_CENTER
66            Payroll        1256
39            Finance        8887

Department_Contacts
DEPT_ID       CONTACT_ID
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
   RS.MoveNext
Wend

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"

Avatar of Richie_Simonetti
Good explanation mdougan!
Avatar of Mike McCracken
Mike McCracken

Excellent explanation

mlmcc
Avatar of RogerH1

ASKER

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.

Thanks,
Roger.
ASKER CERTIFIED SOLUTION
Avatar of mdougan
mdougan
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Avatar of RogerH1

ASKER

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,
Roger.
Cheers!