DTS Import/Export

[polisci grad]

Not sure if this is the place to ask this question but,

I'm trying to transform tables as I import them from excel to access using
DTS Import/Export Wizard. I've written this code to transform them once they
are already in, but it doesn't work when I use it in DTS. I would like DTS
to transform each table and append them all unto a large single file in
access. Any suggestions???

Thanks,

Dim newtbl As Recordset
Dim rs As Recordset
Dim db As Database
Dim tblnme As String
Dim fldnme As String
Dim rwsrce As String
Dim dt As Variant
Dim vl As Variant
Dim intloop As Integer
Dim tbl As TableDef



Set db = CurrentDb

For Each tbl In db.TableDefs
tblnme = tbl.Name

Set rs = db.OpenRecordset(tblnme)
Set newtbl = db.OpenRecordset("Trial")

If tbl.Attributes = 0 And tbl.Name <> "Trial" Then

For intloop = 1 To rs.Fields.Count - 1

Do Until rs.EOF = True

With newtbl
.AddNew
!Key = tblnme & "_" & rs.Fields(intloop).Name
!Date = rs!Date
!Value = rs.Fields(intloop).Value
.Update
End With

rs.MoveNext
Loop

rwsrce = rwsrce & fldnme
rs.MoveFirst

Next intloop

End If

Next tbl

Set db = Nothing
Set rs = Nothing
Set newtbl = Nothing