Access 2003 VBA Docmd.TransferText SpecificationName doesn't work

[Guest]

I am trying to automate the Importing of a CSV file and I am having trouble
with the Docmd.TransferText SpecificationName. I have created one with the
Import/Export wizard but can't find it to edit it (2 col importing as numeric
insted of text and generating import errors) - does this command actually
work? ... I have no problems if I import manually ... is anyone able to help?

 

[Douglas J. Steele]

When you have the Import Text wizard running, look for a button labelled
Advanced in the bottom left hand corner of the dialog.

 

[Guest]

I am trying to automate the Importing of a CSV file and I am having trouble
with the Docmd.TransferText SpecificationName. I have created one with the
Import/Export wizard but can't find it to edit it (2 col importing as numeric
insted of text and generating import errors) - does this command actually
work? ... I have no problems if I import manually ... is anyone able to help?

Yes I have already done that and saved the specification ... I can't find
where it saves the file to edit it so that I can give the col a type 'text'
with a 'width 255'
and even though I use the specification in the code it uses the default ...
That is why I am wondering wether this funtion even works properly ...

 

[Guest]

Yes I have already done that and saved the specification ... I can't find
where it saves the file to edit it so that I can give the col a type 'text'
with a 'width 255'
and even though I use the specification in the code it uses the default ...
That is why I am wondering wether this funtion even works properly ...

Ok .. I found the solution ...
I created a file like the schema.ini file and placed it in the directory
with the CSV file I wanted to Import ... the trick is to name the file with
no extension (no '.ini')
Sorry if I wasted anyones time ...

 

[s_wadhwa]

Hi Trebor,

I'm having the same kind of problem with transfertext Command. Can you
please elaborate your solution.

1. I want to know if you can use DoCmd.TransferText to transfer the
data created by view in SQL Server and .adp Access application into a
text file.

2. How do you create the specification file and what all parameters can
you specify in that specification file. and how to save that file.

Please if you can help me in the above questions I would really
appreciate it.
Thanks

 

[Guest]

Hi,
I investigated using the same code in an Access Project without success ...
Nor could I export a view/query to a file using the TransferText action ...
there isn't even an 'advanced' button to save a specification in the export
wizard ...
What is frustrating is that all the microsoft help files and MSDN etc all
say that this can be done ... so either it can and I'm an idiot or there is a
major fault in this particular method ....

I will continue to investigate and let you know if I eventually have succes
.... just hope I don't go crazy in the mean time ...

I used the followinq within an Access mdb with success ...

DoCmd.TransferText acImportDelim, "XREF2CSV", "XREF2", sCSV_File, False

sCSV_File was a sting containg the full path to the file and the File name
was XREF2.csv and The Table I Imported to was XREF2.
XREF2CSV was the schema file I created (note: no file extension)
(I created it using notepad and then removed the .txt extension)
and saved it in the same directory as XREF2.csv ... it looked like this

[XREF2.csv]
Format=CSVDelimited
ColNameHeader=False
Col1=STOCK_CODE Text Width 255
Col2=OEM_NUMBERS Text Width 255
CharacterSet=ANSI

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp
provides the information on the data types etc that can be used ...

Cheers,
Trebor

 

[s_wadhwa]

Thanks Trebor for all your efforts in answering my questions.

Hi,
I investigated using the same code in an Access Project without success ...
Nor could I export a view/query to a file using the TransferText action ...
there isn't even an 'advanced' button to save a specification in the export
wizard ...
What is frustrating is that all the microsoft help files and MSDN etc all
say that this can be done ... so either it can and I'm an idiot or there is a
major fault in this particular method ....

I will continue to investigate and let you know if I eventually have succes
... just hope I don't go crazy in the mean time ...

I used the followinq within an Access mdb with success ...

DoCmd.TransferText acImportDelim, "XREF2CSV", "XREF2", sCSV_File, False

sCSV_File was a sting containg the full path to the file and the File name
was XREF2.csv and The Table I Imported to was XREF2.
XREF2CSV was the schema file I created (note: no file extension)
(I created it using notepad and then removed the .txt extension)
and saved it in the same directory as XREF2.csv ... it looked like this

[XREF2.csv]
Format=CSVDelimited
ColNameHeader=False
Col1=STOCK_CODE Text Width 255
Col2=OEM_NUMBERS Text Width 255
CharacterSet=ANSI

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp
provides the information on the data types etc that can be used ...

Cheers,
Trebor

Hi Trebor,

I'm having the same kind of problem with transfertext Command. Can you
please elaborate your solution.

1. I want to know if you can use DoCmd.TransferText to transfer the
data created by view in SQL Server and .adp Access application into a
text file.

2. How do you create the specification file and what all parameters can
you specify in that specification file. and how to save that file.

Please if you can help me in the above questions I would really
appreciate it.
Thanks