DoCmd.TransferText Method (Access)

[BittenApple]

Hello team,

I am using DoCmd.TransferText Method (Access) to transfer data into Microsoft Access:

Docmd.TransferText acImportDelim, "specification Name", _
"MyTable", "C:\mypath\filename", True, "MySheet!A1toAB10000"

Gives me an error, I don't know what to use for specification Name. Is this code correct?



Thanks for your help,
BittenApple

[HansV]

Do the following:
- Switch to Access.
- On the External Data tab of the ribbon, click Text File in the Import & Link group.
- Click Browse...
- Select the text file that you want to import and click Open.
- Click OK. This starts the Import Text Wizard.
- Select Delimited.
- Click Next >.
- Select the delimiter used in the text file (comma, tab, ...) and whether the first line of the text file contains field names.
- Click Next >.
- Click in each field and if necessary, adjust the field name and the field type.
- When you have done this for all fields, click Next >.
- Specify whether Access should create a primary key field, or that one of the fields in the text file can act as primary key.
- Click Next >.
- Instead of clicking Finish, click Advanced...
- You will see an overview of the settings that you specified. This is the import specification.
- Click Save As... to save the import specification in the database.
- You can accept the name that Access proposes, or give the import specification another name.
- Whichever you choose, take good note of the name; copy it to the clipboard if necessary.
- Click OK to save the import specification.
- Click OK to return to the Import Text Wizard.
- You don't have to finish the import, you can click Cancel now to return to Access.
- Switch to the Visual Basic Editor.
- Replace specification Name in your code with the name that you gave the import specification.

For example, if you named it MySpec, the code would become

Docmd.TransferText acImportDelim, "MySpec", _
"MyTable", "C:\mypath\filename", True, "MySheet!A1toAB10000"

[Mark L]

Just to be clear, though, you don't absolutely need to have an import specification; you can just skip that parameter. You will then get whatever Access sees. The import spec is useful if you need to rename fields, specify a data type etc.

[BittenApple]

Hello team,
I made the codes as this:
Docmd.TransferText acImportDelim, "MySpec", _
"MyTable", "C:\mypath\filename.csv", True, "MySheet!A1:AB10000"

It didn't work, it gave me errors as 2521 or when added extension csv, the message was object can't be found.
any help?
BittenApple

[Rudi]

I don't think the HTML Tablename argument is valid for a csv file...

Try:
Docmd.TransferText acImportDelim, "MySpec", "MyTable", "C:\mypath\filename.csv", True

or even without the Spec name as Mark mentions:
Docmd.TransferText acImportDelim, , "MyTable", "C:\mypath\filename.csv", True

Obviously make sure that your path and filename (as specified) are correct too.

[BittenApple]

Hello Rudi,
What do you mean by saying:
I don't think the HTML Tablename argument is valid for a csv file...
?
Which table are you referring to?
Regards,
BittenApple

[HansV]

You specify "MySheet!A1:AB10000" as the 5th argument of DoCmd.TransferText. The 5th argument, named HTMLTableName, is only valid if you import of link a HTML file, not if you import a CSV file.
Moreover, "MySheet!A1:AB10000" is an Excel reference that makes no sense in a text file.

[BittenApple]

Hello team,
I was able to fix the code with your guidance, it did work.
I didn't use specification and just path and the name of the csv file with a . and then csv.
Thanks a lot for it.
Regards,
BittenApple