Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 731
  • Last Modified:

Import (not link) CSV file into MS Access without automation (schema.ini)

Have been linking csv files into Microsoft Access using a schema.ini file. Now, I have discovered that if I link a second file in using a different schema.ini file the first linked file is no longer linked correctly. For example, if the second schema has less fields some of the fields for the first link will not show up.
So now I want to import a csv file using the same information I have in the schema file. Seems like there should be a way to do this. Access has a "TransferDatabase" command using a "Specification" which seems to be an internal version of the schema file.
Have not been able to find out how to do this.
Note: I know that automation could be used, but I am also having a problem with that ("429 - ActiveX cannot create object excel"). So using automation can not be an answer to this question.
0
wsfindlater
Asked:
wsfindlater
  • 6
  • 4
1 Solution
 
carlosferreiraCommented:
Hi -

I think you want to look into using TransferText (you'll have to execute DoCmd.TransferText TransferType, Specification Name, Table Name, File Name, Has Field Names, HTML Table Name, Code Page).

From my understanding, where you are not too clear about is what is the "Specification" parameter in the call.  To build a specification use the import wizard to create one.  To walk you thru how to create one do the following:

1) In Access 2000 Select "Get External Data" from underneath the "File" Menu.

2) Select "Import"

3) Select the csv file from the import dialog box (you may need to specify "Text Files (*txt, *csv, *.tab, *.asc) from the Files of Type field)

4) Click onto import.

5) With the rest of the Import Text Wizard select the parameters that you want to use that will successfully import the file.  Before selecting the "Finish" button in the import wizard, click onto the "Advanced..." button.

6) An Import Specification dialog box will appear with lots of parameters.  Click onto the "Save As" button

7) Type a name for the the specification.  This is the name of the specification that you will use in the TransferText function.  For sake of example just type in "MySpec".

And thats it!

Now to use it you can use in the TransferText function
(eg DoCmd.TransferText Import "MySpec" ImportTableName etc...")

As a side note this specification is actually stored within the Access database in the two system tables MSysIMEXSpecs and MSysIMEXColumn.

I hope this helps!  

Carlos
0
 
pierrecampeCommented:
carlosferreira
i see you are new to EE, so you are excused for locking this question
but please be adviced that it is considered extremely impolite to do so
could you in the future please post a comment and NOT an answer
0
 
pierrecampeCommented:
to export an access table to a csv file:
db.Execute "SELECT * INTO [TEXT;DATABASE=C:\].[test.csv] FROM [Klant]"
here Klant is an access table

to import a csv into access:
db.Execute "SELECT * INTO [Bla] FROM [TEXT;DATABASE=C:\].[test.csv]"
here Bla is the access table

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
wsfindlaterAuthor Commented:
Carlos,
I think I would still need automation in order to use the 'DoCmd'. Already understood what you said about the "Specifation" parameter. Thank you anyway.

pierrecampe,
Great answer. I always forget about using SQL.
Sorry about locking. Was not aware of the difference.
Will be more careful in the future.
Thank you very much
0
 
wsfindlaterAuthor Commented:
Carlos,
I think I would still need automation in order to use the 'DoCmd'. Already understood what you said about the "Specifation" parameter. Thank you anyway.

pierrecampe,
Great answer. I always forget about using SQL.
Sorry about locking. Was not aware of the difference.
Will be more careful in the future.
Thank you very much
0
 
wsfindlaterAuthor Commented:
Got an error message when I pressed the Submit button.
Wanted to award the points to pierrecampe, not sure he got them.
Do not know how to fix, but I will try to fix it.
0
 
wsfindlaterAuthor Commented:
Submitted the question (below) to community support

Yesterday I submitted a question in the Visual Basic group.
Somehow I locked the question.
This morning I received 3 replies.
One was an answer which I did not want to accept.
The other two were 'comments', the second comment having the right answer.
I want to award the points to the 'comments'.
Do not know how to do this.
Believe the link below points to the comment

http://www.experts-exchange.com/
sp/qManageQuestion.jsp?ta=visualbasic&qid=20318901
0
 
pierrecampeCommented:
wsfindlater you did not lock the question carlosferreira locked the question by posting an answer instead of a comment
in order to accept my comment as an answer you will first have to reject carlosferreiras answer and then accept my comment as an answer
0
 
wsfindlaterAuthor Commented:
pierrecampe
Think I have already blown it. I Accepted with my first replie, but then I got an error page so maybe it is OK.
Will try it now.
0
 
wsfindlaterAuthor Commented:
OK, It worked.
Have a Happy 4th!
0
 
pierrecampeCommented:
Thank You
>>Have a Happy 4th!<<
again thank you, but i am a belgian not an american
so for me it's a workday :-(
 
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now