doCmd.TransferText variables issue

JayceW
JayceW used Ask the Experts™
on
I am trying to create some VBA code that will import delimited data into an Access 2007 database but am having some trouble.

I am trying to use:

DoCmd.TransferText acImportDelim, "Import-Car_Data", Returnvalue, blnHasFieldNames

"Import_Car_data" is a  saved import in the database. ( I have used a saved import so that I can specify that the import file is " delimited.)

Returnvalue is a variable that has been populated with the location of the .csv import file by using a combination of the Application.FileDialog(3)  and Returnvalue = .selecteditems.Item(1) code.

I continually get an error stating that I "cannot import this file" Run-Time error 31519.

I added some code to change the file extension of the import file from .csv to a .txt but this modification hasn't helped matters.

As my import works fine when called from the GUI rather than through VBA I am very confused.

Does anyone have any ideas on how I can solve tis issue?

Thanks

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2014

Commented:
I would assume that your saved Import steps are invalid in some way. It's impossible for us to know how, since we can't actually see those steps, but I'd suggest you thoroughly check those import steps (or recreate and save them), and then also validate your .txt/.csv files against those steps.

Author

Commented:
Hello LSMConsulting,

To be honest I have tried that already and the import works fine when executed through the GUI.

Do you have any other ideas?

Thanks
Most Valuable Expert 2012
Top Expert 2014

Commented:
Have you recreated the import steps? There might be some issues with the saved steps.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Yep, sadly still no joy.

I have also now tried Droping the existing target database table and adding further code to turn warnings off, but no success.

I appreciate it is difficult for you to trouble shoot when you can't see the import method. Is there any other way that I can import a " delimited .csv file without first saving an import to the Access DB?
Top Expert 2016
Commented:
DoCmd.TransferText acImportDelim...

needs the import specification NOT the "saved import steps"

You need to create an Import Specification to do this automatically

To create the Import Specification
1) Click on external data> text file which then opens another window called "Get external data - Text file"
2) Use radio button to select "Import the source data into a new table in the current database"
3) specify the source of the file using the browse procedure then click OK
4) Choose radio button to select delimited format and then click next
5) this window allows you to choose delimiter and text qualifier and if first row contains names - click next
6) This window allows you to type the name of the field in the Field Name column, choose data type, and if you want field indexed - also can choose to skip field - then click next
7) This window allows you to add primary key or designate field as such - click next

8) Click on ADVANCED button

9. in the import specification window
type the name of the field in the Field Name column
(here you can use the field names of the destination table, specify data type,
check the box Skip if you do not want to import the column)


10 click save as, give the specification a name <-- this is the specification name that you will use in the command line below


DoCmd.TransferText acImportDelim, "ImportSpecificationName", "TableName", Returnvalue, blnHasFieldNames

Top Expert 2016
Commented:
you can also use the savedimportexport step with

docmd.RunSavedImportExport(SavedImportExportName)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial