flexible Import Specs

  • Thread starter Thread starter Rob Tofield
  • Start date Start date
R

Rob Tofield

Hi there,

Does anyone know if there is a way to adjust Access Import
Specs "on the fly" via code? I have an Access 97 system
that imports many CSV files from a variety of sources and
the people who send the files have a habit of adjusting
the layout of the data in the files. I want to create some
sort of interface where the Users can designate the
columns with certain data points without having to
actually adjust the import spec themselves.

Any help would be appreciated.

Regards
Rob Tofield
 
Hi Rob,

This is one of the places where Access & VBA give you the flexibility to
code just about import capability and any user interface you want. From
what you say I'd probably do it roughly along these lines:

1) Temporarily attach the CSV file as a linked table. If these are
well-formed CSV files you don't need an import spec.

2) Display a form with lists of the fields in the linked table and the
table you're importing the data to, and a user interface that lets the
users decide which CSV field goes to which destination field.

3) Build and execute a SQL append query that incorporates the user
input.
 
Rob

In addition to John's suggestion (link to/import CSV as temp tables; create
append queries to permanent), you description reminds me of what XML is
supposed to handle.

Just a thought...

Jeff Boyce
<Access MVP>
 
Within MS Access 97:
Tools | Options | System Objects (select)
View tables and you should see MSysIMEXSpecs
This table contains the Ms Access import specs
Presumably it can be modified using your code at run time, like nay other table.
Haven't tried it yet but I'm looking to do the same sort of thing so will in due course.
 
Back
Top