Solved

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

Posted on 2002-07-02
11
700 Views
Last Modified: 2007-12-19
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
Comment
Question by:wsfindlater
  • 6
  • 4
11 Comments
 

Expert Comment

by:carlosferreira
ID: 7125667
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
 
LVL 6

Expert Comment

by:pierrecampe
ID: 7125687
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
 
LVL 6

Expert Comment

by:pierrecampe
ID: 7125689
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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 

Author Comment

by:wsfindlater
ID: 7127435
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
 

Author Comment

by:wsfindlater
ID: 7127436
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
 

Author Comment

by:wsfindlater
ID: 7127448
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
 

Author Comment

by:wsfindlater
ID: 7127512
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
 
LVL 6

Accepted Solution

by:
pierrecampe earned 500 total points
ID: 7128206
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
 

Author Comment

by:wsfindlater
ID: 7128451
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
 

Author Comment

by:wsfindlater
ID: 7128461
OK, It worked.
Have a Happy 4th!
0
 
LVL 6

Expert Comment

by:pierrecampe
ID: 7128574
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question