My file Schema.ini is in the same folder as the
FooSample.txt text file I'm trying to import, the C:\
folder. Here is why I need to do the import:
The data in my text file is two fields separated by a |
symbol. The data is laid out by row in segments. The
first x number of rows regard a client, the next x number
of rows regard their liability information. The next x
number of rows are a series of credit scores. Each row
has a segment number (e.g. 0001.001) So an example of a
credit score is
0611.000
0611.001|1
0611.002|EXP
0611.003|SMITH
0611.005|634
etc.
There can be several of these 0611.000 series'
My code is looking through the resulting imported table
using FindNext to find the right segment value and then
pull the info on the other side of the | into a
variable. However not every series of credit information
has all the same segment numbers. One may be missing a
name. Another may be missing a score. Yet I have to bring
them all in. So my FindNext code also has an If Then
statment checking the NoMatch to see if the cursor
travelled to many rows. If it has it means its probably
pulling data from the next 0611 segment and that's not
the data I'm looking for. I can't do a sorted query
because then all my 0611.001s would be bunched together
and I'd have no idea which ones went with the rest of
their subsequent data. FindNext is the only way to work
through the table. That's why the imported table has to
look exactly like the original text file. Granted I
could just import it as one field and do some string
examination to pull my data but that means editing my
entire code to do so.
-----Original Message-----
Hi Zachariah,
Schema.ini must be in the same folder as the file you are importing. See
http://msdn.microsoft.com/library/default.asp? url=/library/en-us/odbc/htm/odbcjetsdk_98.asp
for the structure.
But I'm with Bruce: there's no way you can control the order in which
Access stores data in its tables, and if you want to see the data in a
particular order the only way is to use a query sorted on the field(s)
or expression in question.
I'm trying another tack in using a Schema.ini file. The
code:
DoCmd.TransferText
acImportDelim, "C:\Schema.ini", "tblFooSample", "C:\FooS mpl
e.txt"
But I get an
Run-time error '3625':
The text file specification 'C:Schema.ini' does not exist.
I've tried passing the location of the Schema.ini file as
a string, leaving out the C:\, nothing helps. Any ideas on
this one?
-----Original Message-----
See my problem? The order has been re-arranged. Why?
Isn't
this more work for access than just leaving the order
alone during the import?
I see the problem. The MDB file format has never been
fully documented and I
don't know how Access manages imported data in memory, so
I really have no
better answer for you with respect to this issue. If the
order is important to
you, simply create a query view sorted by the relevant
field.
--
Bruce M. Thompson, Microsoft Access MVP
(e-mail address removed) (See the Access FAQ at
http://www.mvps.org/access)
NO Email Please. Keep all communications
within the newsgroups so that all might benefit.<<
.
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.