T
Tcs
I'm having a problem where I don't quite know how to proceed.
I have 93 separate text files for each my 12 tables. In the test I just ran,
one file of 1876 records had a problem with one field in 2 of the rows. It was
a type conversion on a date. (The format of the date coming in is YYMMDD,
unlike the other 2 dates in the record, which are MMDDYY.)
The fact that Access can tell me that I had a problem and what row it is, is
nice, but with 93 possible files to search, well, this isn't exactly an easy
thing to do. And come to think of it, is the row number that Access reports the
row number of the file being imported? (Making the problem relatively easy to
trace.) Or the row in the table? (In which case there won't be any correlation
between the two. Say I'm on the third file to import, and the first 2 were 50
records each. Now Access reports row 123. I'd have to subtract the row counts
of the first two files before I got a meaningful row number.)
Right now my code cycles thru all of my 93 files, using:
DoCmd.TransferText acImportDelim, strImpSpecName, strTblName, strPathName, 0
I guess my first question is: Is it possible to have Access report *more* than
it's default, when it reports an error during importing? Can I somehow get it
to tell me the path of the import file in which the error occured?
And my second question: If I can't get Access to report any more then it
currently does with my DoCmd.TransferText statement, can I somehow "manually"
perform the import. And by this I mean, can I generate code to read the import
file, move/convert the data, add the new record to the table, etc.? I've added
records to tables, but I don't know how to read data I need to import, convert,
etc.
So my third question would be: If I *can* use code to read the import file,
convert and add the data to my tables, could someone provide any details to get
me started? Is there anything special about the reading of the import file?
How do I pluck out the data I need and convert it for my table? (My input files
are all comma delimited, and every field - text, numbers, dates - are contained
in double quotes.) And I'm *assuming* since I have 12 tables, I'd need 12 subs
to handle the individual tables.
Any assistance and/or guidance you could provide would be greatly appreciated.
Thanks in advance,
Tom
I have 93 separate text files for each my 12 tables. In the test I just ran,
one file of 1876 records had a problem with one field in 2 of the rows. It was
a type conversion on a date. (The format of the date coming in is YYMMDD,
unlike the other 2 dates in the record, which are MMDDYY.)
The fact that Access can tell me that I had a problem and what row it is, is
nice, but with 93 possible files to search, well, this isn't exactly an easy
thing to do. And come to think of it, is the row number that Access reports the
row number of the file being imported? (Making the problem relatively easy to
trace.) Or the row in the table? (In which case there won't be any correlation
between the two. Say I'm on the third file to import, and the first 2 were 50
records each. Now Access reports row 123. I'd have to subtract the row counts
of the first two files before I got a meaningful row number.)
Right now my code cycles thru all of my 93 files, using:
DoCmd.TransferText acImportDelim, strImpSpecName, strTblName, strPathName, 0
I guess my first question is: Is it possible to have Access report *more* than
it's default, when it reports an error during importing? Can I somehow get it
to tell me the path of the import file in which the error occured?
And my second question: If I can't get Access to report any more then it
currently does with my DoCmd.TransferText statement, can I somehow "manually"
perform the import. And by this I mean, can I generate code to read the import
file, move/convert the data, add the new record to the table, etc.? I've added
records to tables, but I don't know how to read data I need to import, convert,
etc.
So my third question would be: If I *can* use code to read the import file,
convert and add the data to my tables, could someone provide any details to get
me started? Is there anything special about the reading of the import file?
How do I pluck out the data I need and convert it for my table? (My input files
are all comma delimited, and every field - text, numbers, dates - are contained
in double quotes.) And I'm *assuming* since I have 12 tables, I'd need 12 subs
to handle the individual tables.
Any assistance and/or guidance you could provide would be greatly appreciated.
Thanks in advance,
Tom