doCmd.TransferText variables issue

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

JayceWAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.

0
JayceWAuthor 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
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Have you recreated the import steps? There might be some issues with the saved steps.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

JayceWAuthor 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?
0
Rey Obrero (Capricorn1)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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)Commented:
you can also use the savedimportexport step with

docmd.RunSavedImportExport(SavedImportExportName)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.