importing error

[metalman]

i am importing a .txt file into access and get a file conversion error on some of the records. i delete some of the good records and rerun and then the errored records pull into access with no errors. can anyone tell me why it wont pull all the files when they will pull in when i break it into two imports.

DoCmd.DeleteObject acTable, "jrnitm_importerrors"
DoCmd.DeleteObject acTable, "Items"
DoCmd.DeleteObject acTable, "HEADER"
DoCmd.DeleteObject acTable, "Entry"

DoCmd.TransferText acImportDelim, , "Items", "L:\cw\rpts\JRNITM.TXT", False
DoCmd.TransferText acImportDelim, , "HEADER", "L:\cw\rpts\JRNHDR.TXT", False
DoCmd.TransferText acImportDelim, , "Entry", "L:\cw\rpts\JRNENT.TXT", False

 

[thornmastr]

I was having a similar problem. I'm not sure,exactly, what caused it, but i do know when I defined import specs for each import, the problem stopped. I suspect it may be because some of your records do not comply with the table description. Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[metalman]

thank you thornmastr. but it is only one column of a file and in that caolumn is a 10 digit number have looked at the raw data and all seem to be a number but it stil wont pull in that part of the column but will pull in the rest of the columns. will try to define and see if that helps.

 

[thornmastr]

Just an additional thought which may or may not have a bearing here. On the table definition side. Are you defining that 10 digit number as a text field or as a number. If it is a number, are you sure the type is such that it will hold a number of up to 10 digits. Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[metalman]

it is defined as a number long int the data that goes in this feild is all a 10 digit reference4 number. out of 23497 records it only imports 19,000 records in that feild but in the other 7 feilds it pulls all 23497 records just not the refence number for the last couple thousand. do you think i might have to expand than feild that for some reason on that feild it only alows a certain number of records.

 

[thornmastr]

Possibly this could be the problem. Check the values of your reference numbers. A long integer (first clue) can hold up to ten digits but is restricted within these constraints:

-2,147,483,648 to +2,147,483,547

Hope this helps. Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[metalman]

i think thats it my reference number goes up to 3087405397 and starts at 187400001. how would i get access to acept the higher numbers.
and i really do appreciate all your help.
thanks.

 

[thornmastr]

Basically, you have two choices. You can define your table entry for this field as text and then use formatting to display it. This has both advantages and disadvantages.

The other solution, and the one I favor is to define it as a double number. Keep in mind a few quirks about doubles.
They are floating point which means 64 bits. Yhou need to be careful how you will be displaying the information. You can format it any way you wish.

But, I think, this will at least get your tables loaded without a problem. If you run into problems manipulating the data, post back. Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[metalman]

it looks like they all pulled with the double number thanks for all your help.