Access DB Structure Problem
Posted on 2002-04-10
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]"
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockBatchOptimistic
.Open SQL1, connFile, , , adCmdText
'Populates the Listbox with the department names starting at the ninth column
For intCounter = 9 To rsDepartment.Fields.Count - 1
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
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,