TransferText Function (Access API) using VB

[Egg]

Hi fellas

Ive managed to successfully use the TransferText function to port a csv file to an access database. But of course there is one last nagging problem.

For a certian field, the data that i get can sometimes contain just integers or it can contain char's and integers.

This is where the probem arises....
TransferText seems to automate some sort of recognition that automatically does a determination on the type that is being read... after filling the access DB using TransferText i get an error 'Type Conversion Failure'. The error concerns the data that is a combination of chars and integers (it doesnt like it!) and looking at the other data in the same field it seems that the data has been converted to integer? as I have the following:

C00000000012 <=== data giving error and hence not being
populated
678 <=== data is populated into DB like this but it was
originally 000000000678

How can I fix this or specify (using VB Code) the type that TransferText should be converting too? (I want text type only!)

Thanks for ANY Help!

[Gaffer]

OK, this is one of the weaker sides of Access97 I feel. When you import csv text into Acces, where it be through VB automation, or access itself, it will determine the DataType of the field based on the first line of data it encounters. So (using your example), if C00000000012 was first, it would define the field as text.
You need to use Import Specs. This (as far as I know) can only be set in Access (I am assuming you have access to Access). You need to emulate what you are trying to do in VB6, but in Access. Right click on the Database window (Table screen), and select import. Select csv, and select your file. A wizard is now envoked. You need to use this to set your field types. Before the wizard finishes, click Advanced, and save your spec. Now, when you use objAccess.DoCmd.TransferText acImportDelim, "2101Spec", "tbl2101", strFileName, True.

Hope this helps

[paulw]

The field you are trying to fill is a numeric field - this will do an implicit conversion to the right type

e.g.
Field1 = 0000000678 => Field1 = CLng("0000000678") = 678

Change this field type to text and you should get rid of the problem.

Cheers,

Paul.

P.S. TransferText is fine but you'd have better control if you opened the file yourself and parsed the contents in a function (e.g. Open #1; Get #1, myvar etc.)

[Egg]

Thanks gaffer ill give it a go, I actually thought of that but preferred using code (more flexible) I guess ill have to do it the dodgy way!

Cheers
B.

PS Paul the real problem is what Gaffer is talking about... its not a DB typing problem but a conversion prob thanks anyway!

[Gaffer]

The problem with dodgy data sources (and I know this from working in the banking industry!), especially if you are involved in RAD, is that although using Access native commands are seen as being not true to the VB cause, it gets the job done!

[paulw]

Been ther, done that.

1-0 to Gaffer (but I won the home leg!)

Cheers,

Paul.