Renaming/redfining field in a linked table

  • Thread starter Thread starter Praising Jesus
  • Start date Start date
P

Praising Jesus

I have a batch of text files that are downloaded from the mainframe every
week. I am linking tables to the text files so every week I just dump the
text files in a folder, open Access and I can work with the data.

The files are fairly complex (up to 69 fields per file with thousands of
records) and I have eight different files.

I am looking for two things: 1) after I have created the linked table, is
there a way to rename the fields? Access says no, but that makes no sense to
me since the name of the table has no relation to the linked file. 2) can I
change the data type after it has been recreated? Again, Access says no, but
that makes no sense. The data is not related to the field definition until I
link them. Why would Access care if I change them?

I need some help, or I am going to have to start over from scratch
recreating them. The impetus for this is the downloaded data has changed its
formatting, so where I created them as text files, I can now identify them as
number and date fields to use them properly. Additionally, the committee
doesn't like the names I picked, so they want me to rename the fields.

---Mike
 
I think the best approach is to import the text files into tables in Access.
Assuming that you can pre-define the format of each text file (i.e. the same
type of data in the same columns each time a particular text file is
exported/imported), you can do this:

1. Import manually once for each file (File -> Get External Data -> Import).
This will define the table structure for you. You could also just manually
create the tables & start at #3 below.
2. Go to design view for each table created this way and make any necessary
changes to field formats.
3. Define an Import Specification for each file/table combination that
defines which fields in the text file are included in the import, the fields
mapping between the text file and the table, the format of each field, etc.
(File -> Get External Data -> Import, change file type to Text, select a
file, then Click Advanced -> Specs.)
4. Once all the Import Specs are configured, you can run code something like
this:

'Dimension variables, as necessary
Dim FileName as String
Dim TableName as String
Dim ImportSpecName as String

'could start a loop here to read file/table names from a table (see notes
below)
FileName = "C:\MyTextFile.txt"
TableName = "MyTable1"
DoCmd.RunSQL "Delete * from " & TableName 'flushes prior data
ImportSpecName = "ImportSpecForMyTextFileAndTable1"
DoCmd.TransferText acImportFixed, TableName, FileName, ImportSpecName , 0

This imports the text file into the table using the formats & mappings in
the named import spec. You could enter all the text file and table names into
a table, then open a recordset to loop through all of them, importing each
one in turn. When done, you would have an Access-table-based snapshot of the
data in the text files. You can now output it any way you like: in an Access
report, a simple query, to a spreadsheet, via e-mail, etc.

The SQL statement could be omitted if the data will be cumulative rather
than refreshed each time the import is done.
 
You can create an import specification for your text files. Use one of the
text files to start with by hand. when the import wizard opens, click on
advanced. In here you can give the field a name, define the data type, and
choose not to import a column. Then save the spec with a name and use the
spec name in the TransferText method to import your files.
 
Back
Top