TransferText in VBA

  • Thread starter Thread starter Chris F via AccessMonster.com
  • Start date Start date
C

Chris F via AccessMonster.com

Hello
I am trying to import into a table called tblStakePointsRaw a text file which
looks like this:
1,5000.0000,5000.0000,100.00,PAD Point1
2,5000.0000,5100.0000,101.00,PAD Point2


My tblStakePointsRaw has the following fields:
sprPointNumber Number (Integer) Indexed(No Duplicates)
sprNorthing Number (Double)
sprEasting Number (Double)
sprElevation Number (Double)
sprRawDescription Text

I created a form with a button who's Click Event is set to
DoCmd.TransferText acImportFixed, "Standard Option", "tblStakePointsRaw", "C:\
Downloads\719051.txt", False

I went under File>Get External Data>Import
Found the File C:\Downloads\719051.txt
Clicked On the "Advanced" Button
Created the Spec "Standard Option" with the following:
Deliminator: ,
Field Name Data Type Indexed
sprPointNumber Integer Yes No Duplicates
sprNorthing Double No
sprEasting Double No
sprElevation Double No
sprRawDescription Text No

I then test out the button and the following error pops up every time:
Microsoft Access was unable to append all the data to the table:
The contents of fields in 0 record(s) were deleted, and 29 record(s) were
lot due to key viorlations.

Where did I go wrong? Any help would be greatly appreciated.
Thank You
Surveyor from VA
 
Chris:

One suggestion would be to change the first parameter of the TransferText
method from acImportFixed to acImportDelim. Your text file appears to be
comma delimited.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


Hello
I am trying to import into a table called tblStakePointsRaw a text file
which
looks like this:
1,5000.0000,5000.0000,100.00,PAD Point1
2,5000.0000,5100.0000,101.00,PAD Point2


My tblStakePointsRaw has the following fields:
sprPointNumber Number (Integer) Indexed(No Duplicates)
sprNorthing Number (Double)
sprEasting Number (Double)
sprElevation Number (Double)
sprRawDescription Text

I created a form with a button who's Click Event is set to
DoCmd.TransferText acImportFixed, "Standard Option", "tblStakePointsRaw",
"C:\
Downloads\719051.txt", False

I went under File>Get External Data>Import
Found the File C:\Downloads\719051.txt
Clicked On the "Advanced" Button
Created the Spec "Standard Option" with the following:
Deliminator: ,
Field Name Data Type Indexed
sprPointNumber Integer Yes No Duplicates
sprNorthing Double No
sprEasting Double No
sprElevation Double No
sprRawDescription Text No

I then test out the button and the following error pops up every time:
Microsoft Access was unable to append all the data to the table:
The contents of fields in 0 record(s) were deleted, and 29 record(s)
were
lot due to key viorlations.

Where did I go wrong? Any help would be greatly appreciated.
Thank You
Surveyor from VA
 
David said:
Chris:

One suggestion would be to change the first parameter of the TransferText
method from acImportFixed to acImportDelim. Your text file appears to be
comma delimited.

Hello
I am trying to import into a table called tblStakePointsRaw a text file
which
looks like this:
1,5000.0000,5000.0000,100.00,PAD Point1
2,5000.0000,5100.0000,101.00,PAD Point2

My tblStakePointsRaw has the following fields:
sprPointNumber Number (Integer) Indexed(No Duplicates)
sprNorthing Number (Double)
sprEasting Number (Double)
sprElevation Number (Double)
sprRawDescription Text

I created a form with a button who's Click Event is set to
DoCmd.TransferText acImportFixed, "Standard Option", "tblStakePointsRaw",
"C:\
Downloads\719051.txt", False

I went under File>Get External Data>Import
Found the File C:\Downloads\719051.txt
Clicked On the "Advanced" Button
Created the Spec "Standard Option" with the following:
Deliminator: ,
Field Name Data Type Indexed
sprPointNumber Integer Yes No Duplicates
sprNorthing Double No
sprEasting Double No
sprElevation Double No
sprRawDescription Text No

I then test out the button and the following error pops up every time:
Microsoft Access was unable to append all the data to the table:
The contents of fields in 0 record(s) were deleted, and 29 record(s)
were
lot due to key viorlations.

Where did I go wrong? Any help would be greatly appreciated.
Thank You
Surveyor from VA
Click to expand...

Thank you for help. I have been pulling my hair out for something so simple.
It worked just like I wanted it too. Thanks again.

Surveyor in VA
 
Back
Top