Need to dynamically update columns

[raynman1972]

When uploading one of my .txt files into Access, I have to change three of my datetime columns to text so that I don't get import errors. I have some VBA code that can go through and fix these columns to the correct data type, but it refers to the columns specifically by name and I have to repeat a section of code 3x and this is bugging me.

If I set up a pair of arrays, one for column names (col1, col2, col3), and another with the fld.OrdinalPosition (9, 12, 15), I think I could loop through the same code 3x and get this done. Is there some way that, if given the column name, I can have some lookup function return the fld.OrdinalPosition so that I can skip the second array? Something to make this more dynamic.

Here is the code that I have stolen and adapted to my needs:

'---Create New Field
dbsData.TableDefs.Refresh
Set tdf = dbsData.TableDefs("EMAILS_RESOLVED_DETAIL_TEMP")
Set fld = tdf.CreateField("LOCK_DATE_NEW", dbDate)
'Optional: set default value
fld.DefaultValue = "0"
'We set ordinal position, just after old field
fld.OrdinalPosition = 12
'And append
tdf.Fields.Append fld

'Copy values from old field to a new one
dbsData.Execute _
"Update EMAILS_RESOLVED_DETAIL_TEMP Set LOCK_DATE_NEW = LOCK_DATE", dbFailOnError

'Delete old field
tdf.Fields.Delete "LOCK_DATE"
tdf.Fields.Refresh

'Rename new field to old
tdf.Fields("LOCK_DATE_NEW").Name = "LOCK_DATE"
tdf.Fields.Refresh
'Done!
Set tdf = Nothing

[John_G]

Why go through all of that? Why not just import the data to temporary table, use the VBA code you have to fix the data, then append to the main table? You could do all that in one procedure, and probably by only having to go through the data only once.

(I know - that's easy for me to say - but I've been programming for years!)

John

[SteveF]

As an alternative to JohnG's suggestion -- are these .txt files always in one or more fixed layouts? If so, then you can set up and save one or more Import Specifications in which you explicitly tell Access to treat those offending columns as text. Your VBA code can point to whichever Import Specification should be used at the time of import, and no other reformatting is needed.

Steve

[raynman1972]

Maybe I didn't explain what I am doing very well, sorry. New at this.

I am importing from a text file into a temp table and using an import spec to treat these columns as text. Then, I use the code above to cycle through each column and fix it to the appropriate data type. It would just be nice to have a function that I could feed table name, column name, data type and position and then use this for a number of different uses instead of repeating the code each time. The ideal would be some way of feeding in the column name and have some code that knows that the column is #x so that I can skip this parameter as well.

And to answer the other question, I get this flat file daily and it has a fixed format- read that as no variance in order or format of data.

[John_G]

OK, you get the flat-file daily, but what happens to the data after you fix it? You said it goes to a temporary table, which you then fix - but then what happens? Do you use the data and then discard it, or do you append it to another table which keeps all the accumulated (and reformatted) information?

[SteveF]

If the file has a fixed format, then an Import Specification is definitely your friend!

You'll need to do a manual import of the file once, to set up and name the import spec.

After the import spec is set up, use DoCmd.TransferText in VBA to import the file using the named specification -- either to a new table or append to an existing one.

Steve

[raynman1972]

I append the data to another table after I have re-formatted it and it accumulates. Basically, this is a daily feed that I need to condition, add additional detail columns to and then commit to my long-term table.

[John_G]

I think you can do all this without having to add fields, delete fields. All you need is two recordsets, and one pass through the temporary table, something like this:

Dim db as database
dim rstTemp as recordset, rstPerm as recordset
set db = CurrentDB
set rstTemp = db.Openrecordset("TemporaryTable")
set rstPerm = db.OpenRecordset("PerrmanentTable")
'
' Loop through the temporary table
'
while not rstTemp.EOF
'
' Add a new Record to the Permanent Table
'
rstPerm.Addnew

In this part, between rst.Addnew and rstPerm.Update, you would transfer all the required fields from the current Temporary records to the new permanent record, doing the required refomatting using whatever VBA code or functions you need. If you use a function to reformat and/or change the type of a temporary field value, the you can do something like rstPerm!Field1 = myformatfunction(rstTemp!Field1). For fields that do not need to be reformatted, just use rstPerm!Field2 = rstTemp!Field2. By doing things this way, you don't have to worry about changing table structures.

rstPerm.Update
rstTemp.movenext
wend ' End loop through temporary table records


This is just a suggestion of course - but it is the way I would approach your issue.

HTH

John