I need to take a text file with data separated by commas and convert the files into a access 2000 database. I have not been able to find anything close enough to what I need.

Data looks like : 19990101,111,AZ,e

Thanks,

In VB's help, on the index tab, look up the following...

Open Statement
FreeFile Function
Line Input
Split Function

Then search this site or look up at the top of this forum if I remember correctly for the tutorual on ADO.

Good Luck

If you're using VBA inside Access, another option might be to use a schema.ini file to define your incoming data.

The ini file layout is pretty easy:


This file says that when TMPFILE.TXT is encountered, there will be no header row in the incoming data, which is CSVDelimited, and it identifies the five columns of incoming data.

I tried the following code, but ended up gettign this error "Multiple-step operation genated errors. Check each status value"



I am stuck and not sure if I am going i the right direction here.

MOD EDIT: Code Tags Added.

Instead of .addnew/.update method. I would suggest a command object with an insert string...


However, I just noticed that you do not have an .Update statement in your loop. So add it after you set the field=value part and test.

Good Luck

Read this tutorial to learn how to deal with file handling in VB6 and once you read it, you'll be able to handle and split text from files as you require.

I assume you wanted me to try the .Update first? ( did not work). I am confused as to where to put the insert string in the code. Do I replace the .AddNew/.Update section? the values part of the Insert string is not working for me. I keep getting a compile errror (Expected expression). So I am clear on this, where you have textvalue and numericvalue that would be my variables:
Dim Acc As String
Dim AccDate As String
Dim AccNum As String
Dim AccSt As String
Dim AccLot As String
Incidently, I forgot to delcare these properly when I first started. See code at the beginning.

Thanks for continuing to help.

>Do I replace the .AddNew/.Update section?

Yes.

strSQL = "INSERT INTO YourTableName(YourFieldName) VALUES ('" & variable & "')"

Where YourFileName is you will need to list the fields of the database seperated by a comma (Field1, Field2) and where variable is "('" & var1 & "," & var2 & "')"

Good Luck

Here is what I have at this point:
Private Sub Command1_Click()

Dim conConnection As New ADODB.Connection
Dim cmdCommand As New ADODB.Command

Dim AccPath As String

AccPath = App.Path & "\AccessFiles\"

conConnection.ConnectionString "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\" & "database.mdb;Mode=Read|Write"
conConnection.CursorLocation = adUseClient

conConnection.Open

With cmdCommand
.ActiveConnection = conConnection
.CommandText = "SELECT * FROM tabTestTable;"
.CommandType = adCmdText
End With

Dim Acc As String
Dim AccDate
Dim AccNum
Dim AccSt
Dim AccLot
Open AccPath & "Access.txt" For Input As #1
Do While Not EOF(1)
Line Input #1, Acc
AccDate = Mid$(Acc, 1, 8)
AccNum = Mid$(Acc, 10, 3)
AccSt = Mid$(Acc, 14, 2)
AccLot = Mid$(Acc, 17, 1)
strSQL = "INSERT INTO tabTestTable(Date,LotNum,State,LotType) VALUES ('" & AccDate & "," & AccNum & "," & AccSt & "," & AccLot & "')"
con.Execute strSQL
Loop
Close #1
conConnection.Close

Set conConnection = Nothing
Set cmdCommand = Nothing
End Sub

After I run it I get this error "Invalid use of property" and the code highlights the conConnection.ConnectionString. I did not find anything out on the internet to give me a hint. Anything you see or know could be causing the error?

Thanks,

remove...

Mode=Read|Write

and see...

http://www.connectionstrings.com/access#85

Good Luck