Solved

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

Posted on 2002-07-02
11
696 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
 

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

762 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now