D
D.Stone
I'm trying to import a small text file (2 columns, 14 rows, tab-
separated) into an pre-created table in an Access 2003 project
(connected to SQL Server 2000 back-end).
This works fine interactively, but I cannot get it to go
programmatically. I've tried using TransferText in a macro, and
RunCode in a macro calling a VBA function.
If I omit column names from the 1st line of the input .txt file,
Access complains that the target table doesn't have a column called
'F1' (which is true if unhelpful). If I try to make life easy for it
by renaming my table columns to 'F1' and 'F2', however, it complains
that the table has no column 'F3'! This game could go on a long
time....
Another line of attack was to include the column names as the 1st line
of the input .txt file, and specify 'Yes' as the 5th parameter to the
TransferText action. This gives an error 'Field <column1>_<column2>
doesn't exist in destination table', which again is true, but shows
that the tab isn't being read as a field delimiter.
I thought it might help if I created an import specification, and
quoted that as the 2nd parameter to my TransferText action. However,
the Import Wizard always has the 'Save As...' button greyed out
(unlike with an Access .mdb database).
I can try a different delimiter, and not pre-creating the table, but
I'm running out of options fast. Has anyone encountered similar
problems and succeeded in circumventing them?
Cheers,
Dave
separated) into an pre-created table in an Access 2003 project
(connected to SQL Server 2000 back-end).
This works fine interactively, but I cannot get it to go
programmatically. I've tried using TransferText in a macro, and
RunCode in a macro calling a VBA function.
If I omit column names from the 1st line of the input .txt file,
Access complains that the target table doesn't have a column called
'F1' (which is true if unhelpful). If I try to make life easy for it
by renaming my table columns to 'F1' and 'F2', however, it complains
that the table has no column 'F3'! This game could go on a long
time....
Another line of attack was to include the column names as the 1st line
of the input .txt file, and specify 'Yes' as the 5th parameter to the
TransferText action. This gives an error 'Field <column1>_<column2>
doesn't exist in destination table', which again is true, but shows
that the tab isn't being read as a field delimiter.
I thought it might help if I created an import specification, and
quoted that as the 2nd parameter to my TransferText action. However,
the Import Wizard always has the 'Save As...' button greyed out
(unlike with an Access .mdb database).
I can try a different delimiter, and not pre-creating the table, but
I'm running out of options fast. Has anyone encountered similar
problems and succeeded in circumventing them?
Cheers,
Dave