Normalizing with VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Windows 2000 Pro; Access 2000

I have been tasked with revising a major Access/Excel integrated APP because
of a recent company merger and organizational changes. One of my first
priorities is an attempt at normalizing the "central" database which now has
multiple (3-5)separate data tables for each of 6 "groups" (that will now
become 8 "Groups"). Each table has from 85-109 fields.

I have identified 27 "common variables" and 18 that, while they exist in
each group table, have no data anywhere. I want to put the 27 into a
"Base_data" table, delete the 18 and then consolidate the remaining "group"
tables for the "group specific" data variables. All linked, of course, by a
unique ID number.

My REAL headache is that this same app has over 150 separate forms and close
to 300 queries spread among 4 different .mde/.mdb's Naturally, the "FROM"
clause of many of the queries and the "Row Source" on most of the forms will
change to access the "Base_table" joined on the "Group_Table". I think I've
got the Function down that will modify the queries' SQL statements, but I'm
having trouble finding a way to cycle through all the forms and modifying the
Row Source "string".

Anybody have any "sample code" on how to cycle though all the forms in a
database?

Thanx..
 
BAC said:
Windows 2000 Pro; Access 2000

I have been tasked with revising a major Access/Excel integrated APP
because of a recent company merger and organizational changes. One
of my first priorities is an attempt at normalizing the "central"
database which now has multiple (3-5)separate data tables for each of
6 "groups" (that will now become 8 "Groups"). Each table has from
85-109 fields.

I have identified 27 "common variables" and 18 that, while they exist
in each group table, have no data anywhere. I want to put the 27 into
a "Base_data" table, delete the 18 and then consolidate the remaining
"group" tables for the "group specific" data variables. All linked,
of course, by a unique ID number.

My REAL headache is that this same app has over 150 separate forms
and close to 300 queries spread among 4 different .mde/.mdb's
Naturally, the "FROM" clause of many of the queries and the "Row
Source" on most of the forms will change to access the "Base_table"
joined on the "Group_Table". I think I've got the Function down that
will modify the queries' SQL statements, but I'm having trouble
finding a way to cycle through all the forms and modifying the Row
Source "string".

Anybody have any "sample code" on how to cycle though all the forms
in a database?
Click to expand...

Here's a routine I had lying around to search all recordsources and
rowsources for a particular string. If you can't adapt it to your
purposes, you can probably get the general idea.

Please note that some lines will have been broken onto two lines by the
newsreader, and you'll have to put them back together to make it work.

'------ start of code ------
Sub SearchRecordAndRowSources(strSought As String)

' Search the recordsources of all forms,
' and the rowsources of all combo and list boxes,
' for the specified string.

On Error GoTo Err_SearchRecordAndRowSources

Dim db As DAO.Database
Dim doc As DAO.Document
Dim frm As Form
Dim ctl As Control

Dim varTest As Variant
Dim lngFormCount As Long
Dim lngControlCount As Long
Dim lngFoundCount As Long
Dim lngControlFoundCount As Long

Debug.Print "*** Beginning search ..."

Set db = CurrentDb
For Each doc In db.Containers("Forms").Documents
DoCmd.OpenForm doc.Name, acDesign, WindowMode:=acHidden
Set frm = Forms(doc.Name)
With frm
lngFormCount = lngFormCount + 1
lngControlFoundCount = 0
If InStr(.RecordSource, strSought) Then
Debug.Print "Form " & .Name & " RecordSource: " &
..RecordSource
lngFoundCount = lngFoundCount + 1
End If
For Each ctl In .Controls
If ctl.ControlType = acComboBox _
Or ctl.ControlType = acListBox _
Then
lngControlCount = lngControlCount + 1
If InStr(ctl.rowsource, strSought) Then
lngFoundCount = lngFoundCount + 1
lngControlFoundCount = lngControlFoundCount + 1
If lngControlFoundCount = 1 Then
Debug.Print "Form " & .Name & " -- string
found in control"
End If
Debug.Print , "Control " & ctl.Name & "
RowSource: " & ctl.rowsource
End If
End If
Next ctl
DoCmd.Close acForm, .Name
End With
Set frm = Nothing
Next doc

Exit_SearchRecordAndRowSources:
Set ctl = Nothing
Set doc = Nothing
Set db = Nothing
Debug.Print "*** Searched " & lngFormCount & _
" forms and " & lngControlCount & " controls, found " &
_
lngFoundCount & " occurrences."
Exit Sub

Err_SearchRecordAndRowSources:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_SearchRecordAndRowSources

End Sub
'------ end of code ------
 
And a SUPER THANX!

While waiting for a response to my post I was looking at what a "manual" fix
would take..grooaann...
Thanx again
 
Back
Top