Import from excel, etc.

[JimS]

My client imports several data streams from a corporate system that outputs
excel or tsv or csv files. I have an import proecess in pretty good shape,
except corporate is forever changing the columns. Most often, they add
columns without telling my client. I use an "INSERT * from
ImportedTempTable..." SQL command to get the data where it belongs. I don't
need all the columns, but would prefer to import most of 'em. The Insert
statement errors out when the import table has more fields in it than the
target table.

So, I thought, why not compare the two tables' field lists and import only
the matches? Two issues:

1. Can you point me to an example of looping through the fieldname
collection on a (linked) table, so I can get up to speed on that quickly....
and,
2. What if the sum of the lengths of the field names, commas, square
brackets, etc. come to more than 256 characters (the longest string
variable)? How do I deal with that as I assemble the SQL command?

Jim

 

[Brendan Reynolds]

My client imports several data streams from a corporate system that
outputs
excel or tsv or csv files. I have an import proecess in pretty good shape,
except corporate is forever changing the columns. Most often, they add
columns without telling my client. I use an "INSERT * from
ImportedTempTable..." SQL command to get the data where it belongs. I
don't
need all the columns, but would prefer to import most of 'em. The Insert
statement errors out when the import table has more fields in it than the
target table.

So, I thought, why not compare the two tables' field lists and import only
the matches? Two issues:

1. Can you point me to an example of looping through the fieldname
collection on a (linked) table, so I can get up to speed on that
quickly....
and,
2. What if the sum of the lengths of the field names, commas, square
brackets, etc. come to more than 256 characters (the longest string
variable)? How do I deal with that as I assemble the SQL command?

Jim

Doesn't matter if the table is linked or not, Jim, the code to loop thorugh
the field collection is the same for linked or local tables. What do you
mean by "256 characters (the longest string variable)"? A variable-length
String can hold up to approximately 2 billion characters. A fixed-length
String can hold 64,000. See the following URL ...

http://office.microsoft.com/client/helppreview.aspx?AssetID=HV012019151033&ns=MSACCESS.DEV&lcid=1033

Public Sub LoopThroughFields()

Dim db As DAO.Database
Dim tdfs As DAO.TableDefs
Dim tdf As DAO.TableDef
Dim flds As DAO.Fields
Dim fld As DAO.Field

Set db = CurrentDb
Set tdfs = db.TableDefs
Set tdf = tdfs("Assets")
Set flds = tdf.Fields
For Each fld In flds
Debug.Print fld.Name
Next fld

End Sub

 

[JimS]

Brendan,
Thanks for your response. I was in a hurry, and thought to myself "I can
do this...", so I first determined that I was, indeed, wrong about the string
length rule. My bad.
Then, being more familiar with ADO, I wrote a function that collected all
the fields together in a string, surrounded by square brackets and separated
by commas.

Turned out I knew more than I thought. Thanks for the DAO version.