Generating a field from file name

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

Guest

I have several hundred HTML files that I have imported to Access. The
conversion process went fine. The creator of the HTML tables (not me) forgot
to add a field with some pertinent data. Fortunately, the name of the table
is this pertinent data I need added to each record. What this guy did was
name each file(table) by date, followed by some other abbreviations that are
relevant to each record. If I change the table name in Access by mistake, I'm
screwed.

Ultimately, I need to merge everything together into one big set of data. I
can't do this until I get the problem from above taken care of. Is there a
way to automatically have a field generated for each table, and in that
field, have the file name automatically populated for each record?

I realize there are some referential integrity issues because of the
replication for each record. I can deal with this later. I just need to have
complete records first.
 
Cheese said:
...name each file(table) by date, followed by some other abbreviations that are
relevant to each record.
Click to expand...
Typical beginner design error.

Ultimately, I need to merge everything together into one big set of data.
Click to expand...
Definitely the right thing to do.

Is there a way to automatically have a field generated for each table,
and in that field, have the file name automatically populated for
each record?
Click to expand...

Start by making the target table to store all records into; an easy way
is to just copy one of the existing tables, delete all records, and add
an extra field at the end (date/type). Assumed names in my example:
tblMaster, F_Date respectively.

Also, I'm assuming the "date" part of the table names to be six digits
in the form yymmdd; the code can easily be modified in the case of a
different format.

The sub below should populate the master table reading each partial
table in turn:

Sub Populate_tblMaster()
Dim db As DAO.Database
Dim rst As DAO.Recordset
strTableNames = "SELECT Name FROM MSysObjects WHERE " _
& "Type = 1 AND IsNumeric(Left(Name,6))"
Set db = CurrentDb
Set rst = db.OpenRecordset(strTableNames)
rst.MoveFirst
Do Until rst.EOF
vYear = Left(rst.Fields(0), 2)
vMonth = Mid(rst.Fields(0), 3, 2)
vDay = Mid(rst.Fields(0), 5, 2)
vDate = DateSerial(vYear, vMonth, vDay)
strSQL = "INSERT INTO [tblMaster] ( F_Date ) SELECT " _
& rst.Fields(0) & ".*, " & vDate & " AS F_Date " _
& "FROM " & rst.Fields(0)
db.Execute strSQL
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

Note: To run this code, an appropriate DAO Object Library reference is
required. While in the VB editor window, go to menu item Tools >
References; check if a Microsoft DAO reference is present among the ones
checked at the top of the list. If not, scroll down to find the
appropriate Microsoft DAO X.X Object Library reference and check it. The
appropriate reference is DAO 3.51 for A97, DAO 3.6 for A2K or later.

HTH,
Nikos
 
Back
Top