How to import a fixed width text file into access using Form/VBA/Macro (1 Viewer)

I'm not sure if i'm posting this in the right thread or not. It could fall under Macros, Forms or even here for VBA

This is what I want to do. But what I'm asking is are there any online tutorials/books/videos that would teach me the following:

I want to create a macro or VBA programming in Access 2016 where the user clicks on a button on a form that will walk them through selecting a fixed width text file for the purpose of importing the data in the text file to an existing table or creating a new table. The actions will also include checking for matching records so it will just update the existing records but will also add new records.

Again, I'm asking if there are resources anyone can point me to so I can learn how to do this.

Also, resources that will include using the 'Create, Macro' menu options to create the macro and using the action catalog, ImportExportText action

Thanks

Use the import wizard and import 1 file.
Set fixed width, the path, etc...
BEFORE the final step, click the ADVANCED button.
setup, and save schema
Finish.

Now that you have the schema, include it in the import command:
(Either in macro, or vba)

Docmd.transferText ...schema...file path...

how can you check for existing record for text?

Ranman256

I'm using Access 2016

I went through the text import wizard and there was never an option to 'save schema'

I started the process by selecting External Data Tab, then selecting Text File to import a text file.

I've attached 2 images showing what I'm seeing on my end

The advance tab only shows a 'save as' button to save the specification. I don't know how to find this file. Is this the same as a schema? I searched my folders and I can't find this file.

Also the final screen gave me the option of saving the import. Is this the schema?

If so I went to the saved imports button under the external data tab and I don't see an option to open any file to see the actual steps.

Sorry, schema, aka Spec name.
Same thing.

I don't know how to access this specification file that was saved. I tried searching for it on my pc and I can't locate it.

What file extension does it Have because I've tried multiple ones and I still can't find it.

Do I open this file within Access? If so, what menu option do i select to locate it.

you dont have to access the spec name, you only need to list it in the command line
Docmd.TransferText file, spec, etc...

you CAN edit it by going thru the manual import again, click Advanced again
load your Spec, then alter it, save it.

OK, Thanks this worked. I have to test it more with fixed width and delimited text files.

I still have a question for updated the existing records.

As you can see from the image attached when you do the import wizard it give you multiple options. The first and second option tells you what it will do if the table exists or doesn't exist.

When I run this myself through the click event of the command button if the table exists it appends to the table. In other words it adds a second copied of the data. In my case my file has 98 records. The second time I run this it has 196 records

Now the table that it created it didn't create a primary key. Is this why it appended to the table?

What if I put a primary key on the table and run it again. What will happen to the data? will it update the existing records and add new records for the new data? Will an error get thrown for duplicates?