getting data into access using VB, help and advice needed

[locutus]

Hi , some idiot guidance needed folks.

I need to get data from generated text files that are created from my vax basic program into an access db using VB

Whats the best way to go about this.
The text file that gets ftp'ed to the NT server looks like this:

"UnitNumber","WindUp","ReelNumber","TestNum1","TestDate" "51","2","2020515346000000","1101","20000709" "52","1","1111525346000000","1101","20000709" "52","2","2111525346000000","1101","20000709"

I want to put this into access using vb.

I set the table up like this
first field - number
second field number
third number - text
fourth number - text
fith number - date

then used code in VB to attach the text file to the defined table in the DB , but when I looked up the table after the prog had run all the fields had been changed back to text.

Is this the wrong way to go about reading in data ? is there a better way ? Help. Below is the code I used, but I need the data to be mixed ie numbers and text, as the data is extracted from the data base and imported to excell.

Many thanks
Locutus


Option Explicit
Dim mydb As Database
Dim myrs As Recordset


Private Sub Command1_Click()
Dim tdf As tabledef
Dim mystring As String
Set mydb = opendatabase("c:\reeldata_project\andrew.mdb")

On Error GoTo errorhandler
Set tdf = mydb.CreateTableDef("qcdata")
tdf.Connect = "text;database=c:\reeldata_project\;"
tdf.SourceTableName = "qcdata.txt"
mydb.TableDefs.Delete ("qcdata")
mydb.TableDefs.Append tdf
Exit Sub

errorhandler:
mystring = "Error number: " & Err.Number & vbCrLf
mystring = mystring & "Error description: " & Err.Description
MsgBox mystring
Exit Sub

End Sub

[locutus]

please help guys !

[Buzby]

You may find life a lot easier if you write your code in VBA in Access and use the TransferText command as this is designed specifically to import text files into database tables and is generally quite good.