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.OL EDB.4.0;Da ta 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(intCou nter).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.
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.OL
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(intCou
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)
lstDepartments.AddItem rsStore.Fields(intCounter)
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.
Good explanation mdougan!
Excellent explanation
mlmcc
mlmcc
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.Boole an = 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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
Thanks so much for your help,
Roger.
Cheers!
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_I
AND Department_Contacts.CONTAC
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(cboCo
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_I
sSQL = sSQL & " AND Department_Contacts.CONTAC
sSQL = sSQL & " order by DEPT_NAME"