Import File into table (again)

[IceGirlAnne]

I sort of asked this question already today, but I didn't solve my problem after all...

Here's my problem---

I want users to choose a fixed-length text file in order for it to be imported into a table that has been created. I thought that I needed to create an import specification, so I followed the steps by going to File->Get External Data->Import->(find my file)->Import->Advanced->(Fix the field names just like it is in my table)->Save As, etc...

So, when a user clicks on a command button to find a file to load into the table, it works perfectly for that one file that I did for the whole process, but it doesn't work for other files that the user might choose. I will never know what file the user will want to load. I just know that it will have the same fixed-length fields in that file.

Is there a way for a user to import any text file into the tables and not just the one that I created the import specification for as described above? I'm clueless and lost at this point. Maybe I need to start from scratch....if you have any instructions or code that I need, please let me know!! Thanks for your time and help!

 

[RichMorrison]

You wrote
<<
I will never know what file the user will want to load. I just know that it will have the same fixed-length fields in that file.
>>

Not sure I understand what you are saying.

Are you saying that the files the users want to import all have the same internal structure - field lengths and start/end positions -
but you don't know the name of the file the user might want ?

If that is the case, then you can reuse an import specification.

Does that help ?

RichM

 

[IceGirlAnne]

Yes, that's exactly what I mean. How do I reuse an import specification? Or, what steps should I take to do that? I'm sorry...I'm very new at this!!

And, is what I did to create an import spec in the first place correct?

Thanks for your help!

 

[IceGirlAnne]

In other words....I thought I was reusing it, but when I go to check, only the first column of that file is showing the file, and the rest of the column fields are blank for that different file that I am importing.

 

[RichMorrison]

Anne,

You need to write event code to import a file. The general form of the statement is:
DoCmd.TransferText acImportFixed, "APS Import", "tblAPS_Import", strFileName

where
"APS Import" is the name of an import specification,
"tblAPS_Import" is the name of the table into which the data is imported,
"strFileName" is the name of a file to be imported

I don't remember all the details of saving an import specification. In Acc97 I think you go through the steps from File/Get External Data/Import on the menu. Somewhere in the dialog there is an option to save your import specification with a name. Access Help should give more detail.

Getting the name of the file to be imported requires a user-entered full path from a textbox or requires additional coding to use a common dialog for file Open/Save.

If you can get a copy of the "Access Developers Handbook" for your version of Access, you can copy most of the common dialog code from example files.

RichM

 

[IceGirlAnne]

thanks

Great! Thanks so much for all of your help!! I really appreciate it!!