[AccessD] CSV file from hell

Hi Rocky,
I've been using the import wizard to get the file in  - then I pick up the 
field ordinal positions of the required fields using the fields collection 
and field names to get the values
This is all working perfectly when I do the manual import - my problem is 
just getting the file in via code
If Steve is correct and transfertext should create the table if it's not 
there then I think I might have a corruption problem ?

I read the original error message 3011 as meaning Access couldnt find the 
import file, as I'm now getting 3709 which seems to be linked with 
corruption.
It's also not finding the import file at all although it's sitting in the 
correct place..
I'll do the usual repair & import in t a new database & see if that helps...

Regards
Robin


-- Original Message ----- 
From: "Rocky Smolin" <rockysmolin at bchacc.com>
To: "'Access Developers discussion and problem solving'" 
<accessd at databaseadvisors.com>
Sent: Saturday, May 15, 2010 1:49 PM
Subject: Re: [AccessD] CSV file from hell


> Do you handle the varying number of fields by letting the transfer text
> create the fields, would it work to delete the table yourself in code , 
> then
> copy a template table which would only have to have one dummy field to
> va_orders?
>
> Rocky
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Robin Lawrence
> Sent: Saturday, May 15, 2010 3:16 AM
> To: 'Access Developers discussion and problem solving'
> Subject: [AccessD] CSV file from hell
>
> Hello to all,
> I haven't been active in Access for about 10 years, was in the forum then
> but I'm afraid more from the listening end (You're all so quick I hardly
> ever got a chance to get an answer in!) Anyway I've moved down to Cornwall
> (UK) and have a part time job administrating for a theatre, they have been
> writing tickets out by hand for 20 years ( 400 seater / 73 shows in the
> season!) Needless to say I couldnt resist offering to sort that one
> out.......
>
> Enough waffle - down to the problem
>
> I have a csv file coming from the web site ( no possibility of changing 
> the
> format at present) which I'm importing into the local box office system
>
> The problem is there is a section in the middle of the file which can 
> vary,
> ie between two fixed fields  extra fields can be added. I've figured out 
> how
> to handle this in code once the file is imported to a table in the 
> database.
>
>
> I've been importing the file manually - taking the option to overwrite the
> table when asked so that any extra fields (since the last import) will be
> included in the field definitions, it all works fine I'm now trying to
> automate this in code so that the operators can have a 'press button' menu
> to import and process the orders.
>
> Problem is with using 'docmd.transfertext' I dont get the option to
> overwrite the table, then the fields don't match and I get an error....
> If I delete the table completely first the it complains about it not being
> there....
>
> Just wanted to ask if there was any way to force docmd.transfertext to add 
> a
> new table before I start writng code to open the file directly and parse 
> it
> myself ?
>
> (I have tried to link rather than import the file but it doesn't read the
> values correctly)
>
> Regards
> Robin Lawrence
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>