Hi, Michael.
You may have run into a problem with your import specification by trying to
apply the "Advanced" options too soon. If you click on the "Advanced"
button immediately after the Import Text Wizard dialog window opens, then
you'll see the default import specifications, along with the specifications
that Access has guessed at. If you make any changes at this point, those
changes will apply to the defaults and the guesses, and you may not end up
with the layout that you want.
Before you click on the "Advanced" button on the Import Text Wizard, you
need to make some choices, such as whether to use delimited or fixed width
fields, which field delimiter to use, whether the first row in the text file
contains names, what constitutes a "text qualifier," and whether to save the
text file in a new table or append to an existing table.
If you are creating a new table as part of this import and you are using
Access 2003, then when you get to the "Field Options" page, skip it. Other
versions of Access allow you to make changes to the fields other than the
first field from this page, so go ahead and make changes on this page if you
have another version. Click the "Next" button to go to the "Primary Key"
selection page and select your primary key option, then click the "Next"
button to go to the last page of the wizard, where you will select the
"Advanced" import options.
If you are appending to an existing table, then when you click the "Next"
button on the "store data" page, you'll arrive on the last page of the
wizard, where you will select the "Advanced" import options. (You won't be
prompted to change field options or choose a primary key if you are
appending records.)
When you click on the "Advanced" button, you'll see the dialog window that
allows you to specify the file format, fields and delimiters. Your earlier
choices are already selected on this page. Go ahead and make any "advanced"
choices and modifications, such as whether to use four-digit years. If you
weren't able to change the field information on the wizard page in Access
2003, do so in this dialog window. Access guesses the field data types, so
you may need to make some modifications, and even change some field names.
Add indexes to the fields where needed and mark the check box under the
"Skip" column for any field you don't want to import.
Now click on the "Save As . . ." button and name your new import
specification. Click the "OK" button to save. Click the "OK" button on the
"Advanced" import options dialog window to return to the last page of the
wizard. Here, you can name your new table to anything you'd like before
clicking on the "Finish" button. (You won't be able to change the name of
the table to import to from this page if this import is going to be appended
to an existing table.)
Your text file will now be imported according to the Import Specification
that you've saved.
Later, when you need to amend this Import Specification, just import a text
file (it can be the same one or one that's slightly different) with the
Import Text Wizard again. As soon as the wizard's dialog window pops up,
click on the "Advanced" button to open the "Advanced" import options dialog
window. Click on the "Specs. . ." button to select and open your previously
saved Import Specification. Make your changes to the advanced options.
If this is all you need to amend, then select the "Save As. . ." button to
save your changes under a newly named specification (or overwrite the
existing one if you don't want to keep the original specification). Click
the "OK" button to save the specification and click the "OK" button to
return to the first page of the wizard and keep clicking "Next" until you
reach the last page of the wizard. Here, you'll be able to change the name
of the table that Access will import the text file into, provided this is a
new table. Click the "Finish" button to import the text file.
If you need to amend other options in the wizard besides the advanced
options, then after making the changes in the "Advanced" import options
dialog window, click "OK" to return to the first page of the wizard. Make
whatever changes you need to make, and when you get to the last page, click
on the "Advanced" button once again. Click on the "Save As . . ." button to
save the new specification (or overwrite the existing one if you don't want
to keep the original specification.) Click the "OK" button to save and
close that dialog window. Click the "OK" button again to return to the last
page of the wizard. If you need to change the name of the table to import
to, do so before you click the "Finish" button.
Your text file will now be imported according to the amended Import
Specification that you've just saved.
HTH.
Gunny
For your Microsoft Access, database development and maintenance needs, see:
http://www.softomagixly.com