Help for code to import data from flat file into access

[sue49203]

I'm trying to learn how to import data from a flat file into an access table using VBA. Could you help me please? I have created several very simplified elements to make it easier to learn.

What I have:

1. An Access 2007 database called importtest.accdb
2. There is one table in the database called names with 4 columns: id, fname,lname,nickname
3. I have a flat file called importflatfile.txt
in the flat file are three lines
123456789987654321123456789
987654321123456789987654321
123456789987654321123456789

Where the first 9 characters indicate the fname, the second 9 characters indicate the lname and the third 9 characters indicate the nickname

4. I have a module in the database called ImportTestMod with the following Sub:

Option Compare Database

Public Sub ImportTextFile()
Dim fd As FileDialog

Set fd = Application.FileDialog(msoFileDialogFilePicker)
Dim strPath As String

With fd
.Title = "Data Import Software"
.Filters.Clear
.Filters.Add "All Files", "*.*"

If .Show = -1 Then 'pressed OK
strPath = .SelectedItems(1)
'read text file
Dim strTextLine As String
Dim strfname As String
Dim strlname As String
Dim strnickname As String
Dim rst As ADODB.Recordset
Dim cn As ADODB.Connection

Set cn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.ActiveConnection = cn
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic


Open strPath For Input As #1
Do While Not EOF(1) ' processing text file input
Line Input #1, strTextLine ' read line and load into variables
' test variables
strfname = Trim(Mid(strTextLine, 1, 9))
strlname = Trim(Mid(strTextLine, 10, 9))
strnickname = Trim(Mid(strTextLine, 19, 9))
msg = "F Name:" & strfname & " Last Name Is: " & strlname
Style = vbYesNo
Title = strfname & " " & strlname
response = MsgBox(msg, Style, Title)

With rst
.Source = "select * from names"
.Open
.AddNew
.Fields("fname") = strfname
.Fields("lname") = strlname
.Fields("nickname") = strnickname
.Update
.Close
End With
Loop
Close #1
MsgBox "Import Completed" & vbCrLf & vbCrLf
End If
End With
End Sub

5. I have a form in the database with one button so when that button is clicked it runs the module -- the file dialog opens -- I select the text file -- it goes through the message boxes (successfully reading the flat file) and then it errors on the .open

Could someone please help me? I'm trying to understand this part so that I can move ahead on a much larger project that will require me to import in relevant information.

Thank you very much!
Sue

[GolferGuy]

Have you tried to use the import functionality within Access? Click here for a website that explains the text file import functionality. You will want to look at the "fixed" file format rather than the delimited file format.

[HiTechCoach]

I'm trying to learn how to import data from a flat file into an access table using VBA. Could you help me please? I have created several very simplified elements to make it easier to learn.

What I have:

1. An Access 2007 database called importtest.accdb
2. There is one table in the database called names with 4 columns: id, fname,lname,nickname
3. I have a flat file called importflatfile.txt
in the flat file are three lines
123456789987654321123456789
987654321123456789987654321
123456789987654321123456789

Where the first 9 characters indicate the fname, the second 9 characters indicate the lname and the third 9 characters indicate the nickname

4. I have a module in the database called ImportTestMod with the following Sub:

Option Compare Database

Public Sub ImportTextFile()
Dim fd As FileDialog

Set fd = Application.FileDialog(msoFileDialogFilePicker)
Dim strPath As String

With fd
.Title = "Data Import Software"
.Filters.Clear
.Filters.Add "All Files", "*.*"

If .Show = -1 Then 'pressed OK
strPath = .SelectedItems(1)
'read text file
Dim strTextLine As String
Dim strfname As String
Dim strlname As String
Dim strnickname As String
Dim rst As ADODB.Recordset
Dim cn As ADODB.Connection

Set cn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.ActiveConnection = cn
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic


Open strPath For Input As #1
Do While Not EOF(1) ' processing text file input
Line Input #1, strTextLine ' read line and load into variables
' test variables
strfname = Trim(Mid(strTextLine, 1, 9))
strlname = Trim(Mid(strTextLine, 10, 9))
strnickname = Trim(Mid(strTextLine, 19, 9))
msg = "F Name:" & strfname & " Last Name Is: " & strlname
Style = vbYesNo
Title = strfname & " " & strlname
response = MsgBox(msg, Style, Title)

With rst
.Source = "select * from names"
.Open
.AddNew
.Fields("fname") = strfname
.Fields("lname") = strlname
.Fields("nickname") = strnickname
.Update
.Close
End With
Loop
Close #1
MsgBox "Import Completed" & vbCrLf & vbCrLf
End If
End With
End Sub

5. I have a form in the database with one button so when that button is clicked it runs the module -- the file dialog opens -- I select the text file -- it goes through the message boxes (successfully reading the flat file) and then it errors on the .open

Could someone please help me? I'm trying to understand this part so that I can move ahead on a much larger project that will require me to import in relevant information.

Thank you very much!
Sue

If it were me, I would just use an append query.

Replace this chunk:

With rst
.Source = "select * from names"
.Open
.AddNew
.Fields("fname") = strfname
.Fields("lname") = strlname
.Fields("nickname") = strnickname
.Update
.Close
End With

with this:

CurrentDB.Execute "Insert Into [names] (fname, lname, nickname) Values(" & Chr(34) & strfname & Chr(34) & ", " & Chr(34) & strlname & Chr(34) &  ", " & Chr(34) & strnickname) & Chr(34) & ";"

[sue49203]

Thanks! I will try your suggestions today!! I appreciate the ideas.