P
Petr Danes
Fairly often, I get handed a dataset in a flat file format. It may be from a
spreadsheet, or from an amateur-built database or even just a text table.
Sometimes it goes on to become a respectable database, sometimes it's just a
bit of clean up, but always, one of the first steps is normalizing the
dataset.
One of the routine chores associated with the task is breaking out fields of
repeating data into separate tables. After many repetitions of this, I
finally realized it's a pretty mechanical operation, which doesn't really
need all that much thought or creativity, so I decided to automate the
process a little.
Maybe the Access analyzer can do something similar, but I've never been able
to figure out how to make it do what I want, so I wrote this to fit my exact
needs and nothing else.
The tasks accomplished by my routine are described here and the numbering
corresponds to the numbered comments in the code. Given an existing table
and field, called TT and FF:
1. Create a new table, called "TableOf" & FF & "s", with an Autonumber
primary key called FF & "AutoID", and a second column, also called FF, same
type, same length if text, with nulls disallowed, empty fields disallowed
and an index disallowing duplicates.
2. Check to see if there are any blanks or nulls in FF, and create a new
field in TT, called FF & "AutoID". If there are no nulls or blanks in FF,
this new field also disallows nulls.
3. Execute a grouping query to load FF in the new table with one sample of
each piece of data from the original FF in TT, excluding nulls and blanks.
4. Using a Join on the two FF fields, load the newly generated autonumber
primary keys from the new table back into the new foreign key field in TT.
5. Remove the original FF from TT.
6. Create a relationship between the new foreign key in TT and the primary
key of the new table, with updates and deletes cascaded.
The included code is driven from a simple form which I import into whatever
database I'm fixing. The form has two comboboxes and a command button. The
first combo shows all the tables in the database; the second all fields for
the table selected in the first. The command button simply calls the
BreakOutField routine with the selected table/field. BreakOutField is
self-contained, so the form is only a graphical assist, not a necessary
component. The BreakOutField routine may be called from any other code, all
it needs is the table name and field name as string parameters.
I tried several variations of the code, because I wanted to make it all work
from one 'library', but I was never able to get everything functional. My
first choice was SQL, to make it as universal as possible, including the
transfer of data, but that broke down when I discovered that adding a
relationship to a table didn't support cascaded updates and deletes.
(Cascaded updates aren't really necessary, since I use Autonumber codes for
the link fields, but cascaded deletes are, and SQL refuses to do that for an
already existing table.)
I then tried to do it all with ADOX, but quickly discovered on my own how
buggy that library is, later confirmed by some grubbing around in the
archives.
I then tried DAO, but couldn't figure out how to set up all the properties
of a relationship or Autonumber field properly, so I gave up on the single
provider notion. The result is a Heinz-57 mix of SQL, DAO and ADOX, but it
does what I want. All table and field names used in SQL are surrounded by
square brackets, so it should be able to handle names with spaces. I don't
use such names myself and change them when I run across them in other
people's work, but they are legitimate, so the code is set up to handle
them.
The code below is all the VBA associated with the form. I've not included
the form, since NG etiquette for these forums prohibits attachments, but it
should be easy enough to figure out how to build it from the code. If you
can't figure it out, you probably shouldn't be using it. And if any Access
hotrods with their own websites think it's worth posting for people to
download, feel free.
WARNING: This is a programmer's tool, not intended for any sort of
production environment. It works where I have tested it, but I have not
tested every possible field type, there is no error checking and it makes
permanent changes to table structures - make sure you know what you're doing
before you light it off, and don't come crying to me if you smoke your only
copy of an important database.
-------------------------------------------------------------
A reference must be set to the Microsoft ADO Ext. n.m for DDL and Security
library in order for the ADOX portion of the code to work, and Microsoft DAO
n.m Object Library for the DAO code to work.
Watch for line wrap. I've tried to keep the lines short, but your NG reader
may munch them anyway.
If you have the relationships window open wile this code executes, it will
NOT be updated automatically to show the new relationships. Sometimes you
can click "Show All Relationships" to make them appear, sometimes I've had
to close and re-open the window.
RowSource SQL for Tables combobox:
SELECT MSysObjects.Name
FROM MSysObjects
WHERE ((MSysObjects.Type=1)
AND (Left([name],4)<>"MSys")
AND (MSysObjects.Flags=0))
ORDER BY MSysObjects.Name;
RowSource of the Fields combobox gets set by the AfterUpdate event of the
Tables combo, and its RowSourceType is Field List
-------------------------------------------------------------
Option Compare Database
Option Explicit
Private Sub Form_Open(Cancel As Integer)
' Fields combo is empty initially
cboFields.RowSource = ""
cboFields.Value = ""
End Sub
Private Sub cboTables_AfterUpdate()
' Whenever the table selection is changed,
' reload the Fields combo
cboFields.RowSource = cboTables.Value
cboFields.Value = cboFields.Column(0, 0)
End Sub
Private Sub cmdBreakOut_Click()
' Call the work routine, then reload
' the Fields combo, since the field list
' is now changed
BreakOutField cboTables.Value, cboFields.Value
cboFields.RowSource = cboTables.Value
cboFields.Value = cboFields.Column(0, 0)
End Sub
Sub BreakOutField(crnttblnm$, crntfldnm$)
Dim cdb As dao.Database
Dim crnttbl As dao.TableDef
Dim crntfld As dao.Field
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim fk As ADOX.Key
Dim newtblnm$, newkeyfldnm$, newrelnm$
Dim x$, xt$, NullsInColumn As Boolean
' Set up names of things
newtblnm = "TableOf" & crntfldnm & "s"
newkeyfldnm = crntfldnm & "AutoID"
newrelnm = newtblnm & "_" & crnttblnm
' Set references to current database, table and column
Set cdb = CurrentDb
Set crnttbl = cdb.TableDefs(crnttblnm)
Set crntfld = crnttbl.Fields(crntfldnm)
' 1.
' Create the new table and load in the two columns:
' an AutoIncrement primary key
' a copy of the given column from the source table
x = "CREATE TABLE [" & newtblnm & "] ([" & newkeyfldnm & _
"] AUTOINCREMENT CONSTRAINT PrimaryKey PRIMARY KEY , [" _
& crntfldnm & "] "
xt = Choose(cdb.TableDefs(crnttblnm).Fields(crntfldnm).Type, _
"", "Byte", "Integer", "Long", "Currency", _
"Single", "Double", "DateTime", "", "Text", _
"", "Memo", "", "", "Guid")
x = x & xt
If xt = "Text" Then x = x & " (" & crntfld.Size & ")"
x = x & " CONSTRAINT " & crntfldnm & " UNIQUE Not Null);"
cdb.Execute x
cdb.TableDefs.Refresh
cdb.Relations.Refresh
' Don't want empty fields in the new table,
' but SQL can't specify this
cdb.TableDefs(newtblnm).Fields(crntfldnm). _
AllowZeroLength = False
' 2.
' See if there are any empty/null entries in original table
' If yes, nulls are permitted in the new column
' If not, the field is required
x = "SELECT TOP 1 [" & crntfldnm & "] FROM [" & crnttblnm & _
"] WHERE [" & crntfldnm & "] = """" OR [" & crntfldnm & _
"] Is Null;"
NullsInColumn = (cdb.OpenRecordset(x).RecordCount = 1)
x = "ALTER TABLE [" & crnttblnm & "] ADD COLUMN [" _
& newkeyfldnm & "] LONG"
If Not NullsInColumn Then
x = x & " CONSTRAINT [" & newkeyfldnm & _
"] CHECK ([" & newkeyfldnm & "] Is Not Null)"
End If
x = x & ";"
cdb.Execute x
' 3.
' SQL grouping query to load one each
' from original table to new table
x = "INSERT INTO [" & newtblnm & "] SELECT [" & crnttblnm & _
"].[" & crntfldnm & "] FROM [" & crnttblnm & "] WHERE [" & _
crntfldnm & "] Is Not Null Group By [" & crntfldnm & "];"
cdb.Execute x
' 4.
' SQL update query joing new table and old table
' on original field value
' Query loads new FK column in original table
' with freshly generated AutoID numbers
' from PK column of new table
x = "UPDATE [" & newtblnm & "] INNER JOIN [" & crnttblnm & _
"] ON [" & newtblnm & "].[" & crntfldnm & "] = [" _
& crnttblnm & "].[" & crntfldnm & "] SET [" & crnttblnm & _
"].[" & newkeyfldnm & "] = [" & newtblnm _
& "].[" & newkeyfldnm & "];"
cdb.Execute x
' 5.
' Finally, remove the original field from the original table
x = "ALTER TABLE [" & crnttblnm & "] DROP [" _
& crntfldnm & "];"
cdb.Execute x
cdb.TableDefs.Refresh
cdb.Relations.Refresh
' 6.
' SQL doesn't support cascades for existing tables,
' so I had to do this with ADOX
Set cat = New ADOX.Catalog
Set cat.ActiveConnection = CurrentProject.Connection
Set tbl = cat.Tables(crnttblnm)
Set fk = New ADOX.Key
With fk
.Name = newrelnm
.Type = adKeyForeign
.RelatedTable = newtblnm
.Columns.Append newkeyfldnm
.Columns(newkeyfldnm).RelatedColumn = newkeyfldnm
.UpdateRule = adRICascade
.DeleteRule = adRICascade
End With
tbl.Keys.Append fk
cat.Tables.Refresh
Set fk = Nothing
Set tbl = Nothing
Set cat = Nothing
Set crntfld = Nothing
Set crnttbl = Nothing
Set cdb = Nothing
End Sub
spreadsheet, or from an amateur-built database or even just a text table.
Sometimes it goes on to become a respectable database, sometimes it's just a
bit of clean up, but always, one of the first steps is normalizing the
dataset.
One of the routine chores associated with the task is breaking out fields of
repeating data into separate tables. After many repetitions of this, I
finally realized it's a pretty mechanical operation, which doesn't really
need all that much thought or creativity, so I decided to automate the
process a little.
Maybe the Access analyzer can do something similar, but I've never been able
to figure out how to make it do what I want, so I wrote this to fit my exact
needs and nothing else.
The tasks accomplished by my routine are described here and the numbering
corresponds to the numbered comments in the code. Given an existing table
and field, called TT and FF:
1. Create a new table, called "TableOf" & FF & "s", with an Autonumber
primary key called FF & "AutoID", and a second column, also called FF, same
type, same length if text, with nulls disallowed, empty fields disallowed
and an index disallowing duplicates.
2. Check to see if there are any blanks or nulls in FF, and create a new
field in TT, called FF & "AutoID". If there are no nulls or blanks in FF,
this new field also disallows nulls.
3. Execute a grouping query to load FF in the new table with one sample of
each piece of data from the original FF in TT, excluding nulls and blanks.
4. Using a Join on the two FF fields, load the newly generated autonumber
primary keys from the new table back into the new foreign key field in TT.
5. Remove the original FF from TT.
6. Create a relationship between the new foreign key in TT and the primary
key of the new table, with updates and deletes cascaded.
The included code is driven from a simple form which I import into whatever
database I'm fixing. The form has two comboboxes and a command button. The
first combo shows all the tables in the database; the second all fields for
the table selected in the first. The command button simply calls the
BreakOutField routine with the selected table/field. BreakOutField is
self-contained, so the form is only a graphical assist, not a necessary
component. The BreakOutField routine may be called from any other code, all
it needs is the table name and field name as string parameters.
I tried several variations of the code, because I wanted to make it all work
from one 'library', but I was never able to get everything functional. My
first choice was SQL, to make it as universal as possible, including the
transfer of data, but that broke down when I discovered that adding a
relationship to a table didn't support cascaded updates and deletes.
(Cascaded updates aren't really necessary, since I use Autonumber codes for
the link fields, but cascaded deletes are, and SQL refuses to do that for an
already existing table.)
I then tried to do it all with ADOX, but quickly discovered on my own how
buggy that library is, later confirmed by some grubbing around in the
archives.
I then tried DAO, but couldn't figure out how to set up all the properties
of a relationship or Autonumber field properly, so I gave up on the single
provider notion. The result is a Heinz-57 mix of SQL, DAO and ADOX, but it
does what I want. All table and field names used in SQL are surrounded by
square brackets, so it should be able to handle names with spaces. I don't
use such names myself and change them when I run across them in other
people's work, but they are legitimate, so the code is set up to handle
them.
The code below is all the VBA associated with the form. I've not included
the form, since NG etiquette for these forums prohibits attachments, but it
should be easy enough to figure out how to build it from the code. If you
can't figure it out, you probably shouldn't be using it. And if any Access
hotrods with their own websites think it's worth posting for people to
download, feel free.
WARNING: This is a programmer's tool, not intended for any sort of
production environment. It works where I have tested it, but I have not
tested every possible field type, there is no error checking and it makes
permanent changes to table structures - make sure you know what you're doing
before you light it off, and don't come crying to me if you smoke your only
copy of an important database.
-------------------------------------------------------------
A reference must be set to the Microsoft ADO Ext. n.m for DDL and Security
library in order for the ADOX portion of the code to work, and Microsoft DAO
n.m Object Library for the DAO code to work.
Watch for line wrap. I've tried to keep the lines short, but your NG reader
may munch them anyway.
If you have the relationships window open wile this code executes, it will
NOT be updated automatically to show the new relationships. Sometimes you
can click "Show All Relationships" to make them appear, sometimes I've had
to close and re-open the window.
RowSource SQL for Tables combobox:
SELECT MSysObjects.Name
FROM MSysObjects
WHERE ((MSysObjects.Type=1)
AND (Left([name],4)<>"MSys")
AND (MSysObjects.Flags=0))
ORDER BY MSysObjects.Name;
RowSource of the Fields combobox gets set by the AfterUpdate event of the
Tables combo, and its RowSourceType is Field List
-------------------------------------------------------------
Option Compare Database
Option Explicit
Private Sub Form_Open(Cancel As Integer)
' Fields combo is empty initially
cboFields.RowSource = ""
cboFields.Value = ""
End Sub
Private Sub cboTables_AfterUpdate()
' Whenever the table selection is changed,
' reload the Fields combo
cboFields.RowSource = cboTables.Value
cboFields.Value = cboFields.Column(0, 0)
End Sub
Private Sub cmdBreakOut_Click()
' Call the work routine, then reload
' the Fields combo, since the field list
' is now changed
BreakOutField cboTables.Value, cboFields.Value
cboFields.RowSource = cboTables.Value
cboFields.Value = cboFields.Column(0, 0)
End Sub
Sub BreakOutField(crnttblnm$, crntfldnm$)
Dim cdb As dao.Database
Dim crnttbl As dao.TableDef
Dim crntfld As dao.Field
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim fk As ADOX.Key
Dim newtblnm$, newkeyfldnm$, newrelnm$
Dim x$, xt$, NullsInColumn As Boolean
' Set up names of things
newtblnm = "TableOf" & crntfldnm & "s"
newkeyfldnm = crntfldnm & "AutoID"
newrelnm = newtblnm & "_" & crnttblnm
' Set references to current database, table and column
Set cdb = CurrentDb
Set crnttbl = cdb.TableDefs(crnttblnm)
Set crntfld = crnttbl.Fields(crntfldnm)
' 1.
' Create the new table and load in the two columns:
' an AutoIncrement primary key
' a copy of the given column from the source table
x = "CREATE TABLE [" & newtblnm & "] ([" & newkeyfldnm & _
"] AUTOINCREMENT CONSTRAINT PrimaryKey PRIMARY KEY , [" _
& crntfldnm & "] "
xt = Choose(cdb.TableDefs(crnttblnm).Fields(crntfldnm).Type, _
"", "Byte", "Integer", "Long", "Currency", _
"Single", "Double", "DateTime", "", "Text", _
"", "Memo", "", "", "Guid")
x = x & xt
If xt = "Text" Then x = x & " (" & crntfld.Size & ")"
x = x & " CONSTRAINT " & crntfldnm & " UNIQUE Not Null);"
cdb.Execute x
cdb.TableDefs.Refresh
cdb.Relations.Refresh
' Don't want empty fields in the new table,
' but SQL can't specify this
cdb.TableDefs(newtblnm).Fields(crntfldnm). _
AllowZeroLength = False
' 2.
' See if there are any empty/null entries in original table
' If yes, nulls are permitted in the new column
' If not, the field is required
x = "SELECT TOP 1 [" & crntfldnm & "] FROM [" & crnttblnm & _
"] WHERE [" & crntfldnm & "] = """" OR [" & crntfldnm & _
"] Is Null;"
NullsInColumn = (cdb.OpenRecordset(x).RecordCount = 1)
x = "ALTER TABLE [" & crnttblnm & "] ADD COLUMN [" _
& newkeyfldnm & "] LONG"
If Not NullsInColumn Then
x = x & " CONSTRAINT [" & newkeyfldnm & _
"] CHECK ([" & newkeyfldnm & "] Is Not Null)"
End If
x = x & ";"
cdb.Execute x
' 3.
' SQL grouping query to load one each
' from original table to new table
x = "INSERT INTO [" & newtblnm & "] SELECT [" & crnttblnm & _
"].[" & crntfldnm & "] FROM [" & crnttblnm & "] WHERE [" & _
crntfldnm & "] Is Not Null Group By [" & crntfldnm & "];"
cdb.Execute x
' 4.
' SQL update query joing new table and old table
' on original field value
' Query loads new FK column in original table
' with freshly generated AutoID numbers
' from PK column of new table
x = "UPDATE [" & newtblnm & "] INNER JOIN [" & crnttblnm & _
"] ON [" & newtblnm & "].[" & crntfldnm & "] = [" _
& crnttblnm & "].[" & crntfldnm & "] SET [" & crnttblnm & _
"].[" & newkeyfldnm & "] = [" & newtblnm _
& "].[" & newkeyfldnm & "];"
cdb.Execute x
' 5.
' Finally, remove the original field from the original table
x = "ALTER TABLE [" & crnttblnm & "] DROP [" _
& crntfldnm & "];"
cdb.Execute x
cdb.TableDefs.Refresh
cdb.Relations.Refresh
' 6.
' SQL doesn't support cascades for existing tables,
' so I had to do this with ADOX
Set cat = New ADOX.Catalog
Set cat.ActiveConnection = CurrentProject.Connection
Set tbl = cat.Tables(crnttblnm)
Set fk = New ADOX.Key
With fk
.Name = newrelnm
.Type = adKeyForeign
.RelatedTable = newtblnm
.Columns.Append newkeyfldnm
.Columns(newkeyfldnm).RelatedColumn = newkeyfldnm
.UpdateRule = adRICascade
.DeleteRule = adRICascade
End With
tbl.Keys.Append fk
cat.Tables.Refresh
Set fk = Nothing
Set tbl = Nothing
Set cat = Nothing
Set crntfld = Nothing
Set crnttbl = Nothing
Set cdb = Nothing
End Sub