Transfertext, import problem

[kuipers78]

Hello,

I've converted an Access 2.0 Database to 97 and now the import function doesn't work properly anymore... (under 2.0 it works fine)
The current vba-code I use for importing a (no-extension) file into a table named "data":

--------------------------------------------------------------------
Dim strfile as String

On Error GoTo Err_cmdimport_Click
Msg = "Please specify the file to import"
Title = "Import file"
strfile = Imputbox (Msg, Title, "C:\")

If Strfile = "" then Exit Sub

Open strFile for Input As #1 'check if path exists
Close #1

docmd.transfertext , "data-import", "data", "C:\filename"

Err_cmdimport_Click:
Msgbox Err.Description
Close

---------------------------------------------------------------------

After the transfertext command the code goes to the 'Err_cmdimport_Click:' and I get a messagebox (no error-code) which says "Cannot update data. The Database or object is read-only". However, the database or the textfile aren't both read-only... (neither is the table "data") What could possibly go wrong while it worked fine under 2.0 ? By the way, I've tried several variations on the Tranfertext command without any result.

Any help will be greatly appreciated!

 

[namliam]

I dont think in newer versions (yes I know, backward compatibilty and such ) you cannot import a file without extension.

You cannot even import a .DAT extension. It needs to be .TXT, so I have gotten into the habbit of renaming my files... Try adding the .TXT extension...

 

[kuipers78]

Thanx NamLiam, it looks like you're right about that.
It isn't a problem to change the file extension to .txt, but apparently Access 97 deals different with such a filetype, because I get an other error message now
The message says "Field F1 doesn't exist in target table "Data". The field cannot be added."

I am not very familiar with the rules for importing .txt files en target tables, but in the old situation everything worked fine...
I can tell the target table consists of 24 fields (named 1 to 24). Five fields are numeric (long integer) and all the other fields are of the type text, with a certain field length.
The import file (now .txt) consists of 2 'introduction' lines (they weren't imported under 2.0) and 53 'import' lines. All data under the lines is being separated by the '|' symbol. The length and number of '|' symbols varies per line.

What is going wrong?

Again, any help will be greatly appreciated!

 

[namliam]

Check your import specification that your using and make sure it contains propper names. and/or Does the file have Column headers on the first line?

 

[kuipers78]

No, when I delete the 2 'introduction' lines I still get the same error message. The text 'F1' isn't even present in the .txt file... I really have no clue what is going wrong...

 

[namliam]

F1 must be part of your import specification, by default access will call it Field1 not F1...

 

[kuipers78]

All specifications are written down in the code I posted.

 

[kuipers78]

I've figured out that Access wants you to define all fieldnames in the first .txt row, seperated by ';'. This results in 1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24

Furthermore, Access wants you to use the ';' symbol for all separations of data in the .txt file, so the '|' symbol doesn't work anymore... When the file matches all these restrictions, importing is no problem. Is Access 97 really that strict? Or can I use other statements to import the file succesfully?

I now use this code for importing:

docmd.TransferText acImportDelim, , "Data", "C:\Filename", True


Thanx again for all help.

 

[namliam]

No, not all the specifications are in the code.... If you dont know what something is dont assume you dont need it

the TransferText command works like so:
docmd.TransferText acImportDelim,"specName","TableName","FileName"

You are not specifying a "specName" or Import specification. In this you can define however you want your file to be interperted. The seperator can be any character and your layout can be anything.

Try doing right click on your database window, Import.
Now select text file and OK, you will be redirected to the import wizard. When you choose Advanced you can save you specification, which you can then use to Import your file without problem.

 

[kuipers78]

Maybe I wasn't clear enough, Namliam. I tried to say that all import specifications I have written are in the code I posted. I wouldn't dare to assume that this code is 'complete' or that there aren't other ways of specifying an import. Otherwise I wouldn't ask for it, would I?

You are right about not specifying a "specName" in my import line; I already noticed that, but couldn't figure out how to fill this in correctly. As I read in your post, it enables you to specify the lay-out of the import file (headers, seperators), right?
I thought you would give an example or something, but further you describe an import wizard for importing files. It works great and you are right about being able to make all kind of specifications, but I do not see the connection with the code we were talking about...

I'll try to be very specific now: is it possible to assign a separator in vba-code? And is it possible to define your import lay-out there too? If so, can you give an example (preferably comparable with my code)?

Thanks for your support!

 

[john471]

Pmfji....

...you will be redirected to the import wizard. When you choose Advanced you can save you[sic] specification, which you can then use to Import your file without problem.

What Namliam said, without labouring the point, is that you use this wizard to define your Import Specification, and save it (with a name of your choosing). The buttons for this are there (under "Advanced...") in that wizard, if you look hard enough for them. You then refer to that saved import specification by that name in your code.

docmd.TransferText acImportDelim,"YourSavedImportSpecName","TableName","FileName"


HTH

Regards

John

 

[kuipers78]

Thanx John! And thanx of course Namliam!
Sorry, I should have read more carefully, but Access has hidden these features quite good... Now it works fine and I am very happy! Thanks again!

 

[namliam]

The great thing of this feature is that one can only get there using this wizard, as far as I know there is no other way to specify a specification...

[qoute]save you[sic] specification[/quote] You are kidding, no your not..

Its because we have this stupid commercial here in the NL...

 

[john471]

Namliam,

Sorry, I wasn't kidding, but it was an error of judgement on my part. It is one of my pet peeves, and it got under my skin at just the wrong moment. For me, reading text with incorrect usage of (you)/your/you're and even there/their/they're causes difficulties, as although they sound the same, they do have different meanings - so I find myself having to re-read, thinking "oh, they meant 'their'" (or whatever).

Apologies.

John.

 

[namliam]

Apologies not needed John, I am not a native English speaker but take pride in the fact that my English is quite good.

We have this stupid commercial in the Netherlands, which uses you instead of your because of their name. I simply cannot believe I got contaminated by that stupid commercial.

In future please do correct any typo I may make...

Kind Regards

 

[kuipers78]

Yeah, I'm a dutchman too, so why do we make things so difficult by discussing in English? Just kidding of course, it ain't a dutch forum and otherwise John couldn't have joined our discussion
One more question (a little bit offtopic): what does 'sic' means??? Spelling InCorrect???

 

[john471]

It is Latin for "so" or "thus". There are numerous postulations as to its use, or the intentions of any and all who use it. The one most appropriate to this discussion is

(www.guardian.co.uk/notesandqueries/query/0,5753,-23558,00.html)
Sic is Latin for So or Thus. It is used to denote that a grammatical error, mistake or specific formating[sic] in a quoted section is in the original quote and the quoted section is AS IT APPEARS in the original document. The quoting party is pointing out that they are not making an error or changing the format of the quote but are indicating that the original contianed[sic] the error or formating[sic].

 

[namliam]

I knew it was something like that, but just couldnt put it into words...Thanks John for explaining and thanks again for pointing out...

 

[Brad_512]

difference between SpecName and FileName?

What Namliam said, without labouring the point, is that you use this wizard to define your Import Specification, and save it (with a name of your choosing). The buttons for this are there (under "Advanced...") in that wizard, if you look hard enough for them. You then refer to that saved import specification by that name in your code.

docmd.TransferText acImportDelim,"YourSavedImportSpecName","TableName","FileName"


HTH

Regards

John

So if you don't use the import wizard, where does the spec name come into play? Where else should the spec name be applied in the form? Also, where would the filename come from? Is it the file you just browsed for and grabbed? If so, how would that be named in the coding?

 

[john471]

Brad,

The specname comes into play after you have created an import specification, which as namliam points out, can only be done through the wizard - so you first go through the import steps manually to allow you to create (and save) a specification, and then you can later use that specification in your code. If you've never used the wizard I don't think the specname can come into play, because you won't have any saved specifications.

The filename is the name (and path) of the file that you wish to import. I don't really think I understand the intention of your question on this point. If you want to provide functionality for the user to browse for a file, then you would need to write additional code for that. Searching this forum should yield some good examples on how to do that. GHudson posted a sample in This Thread that might be a reasonable place to start.

HTH

Regards

John