Importing into access using VBA

[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

 

[PieterLinden via AccessMonster.com]

start over.

if you create an import specification, you can set it up so that everything
gets chunked the way you want. (first X letters map to field 1, second chunk
maps to field 2...).

Then you can just use TransferText and the import specification. No coding
required.

 

[sue49203 via AccessMonster.com]

Thanks soooo very much. I want this to be able to be done by end users. Can
I use this method to attach to a button on a form so that the end user simply
clicks the button - selects the file - and tada it imports? I'm sorry, I'm a
newbie to Access 2007 so need a few details if you don't mind.

Could you point me in the direction of any examples? Thanks for your help.

Sue