Importing .txt File into Access using VBA

[PSUIVERSON]

I am attempting to import a .txt file into MS ACCESS 2000 using VBA.

Here is my rough attempt - I am stuck right off the bat because I am trying to call on a database I haven't created yet!? So I guess

Sub importTEXT()

Dim db As Database
Dim rst As Recordset

Dim field0 As Variant
Dim field1 As Variant
Dim field2 As Variant
Dim field3 As Integer
Dim field4 As Variant
Dim field5 As String
Dim tablename As Variant

Open "c:\file.txt" For Input As #1

tablename = "TABLE" & Date

Set dbs = CurrentDb
Set tdf = dbs.TableDefs(tablename)

Do While Not EOF(1)
Input #1, field0
Input #1, field1
Input #1, field2
Input #1, field3
Input #1, field4
Input #1, field5

* Here I would like to take these variables and begin writing them into the .mdb file

Loop

End Sub

Any help would be appreciated!

Thanks

PSUIVERSON

 

[WayneRyan]

psuiverson,

Dim dbs As Database
Dim rst As RecordSet
Dim strSQL As String

Set dbs = CurrentDB
strSQL = "Select * from tblMytable"
Set rst = dbs.OpenRecordSet(strSQL)


Do While Not EOF(1)
Input #1, field0
Input #1, field1
Input #1, field2
Input #1, field3
Input #1, field4
Input #1, field5
rst.Edit
rst.AddNew
rst!Field0 = field0
rst!Field5 = field5
rst.Update

general idea ...
Wayne

 

[PSUIVERSON]

Thanks man...Check this out I just found in Mastering Microsoft 2000 ACCESS development...

Sub ImportText()

Dim filePath As Variant
Dim tblNAME As Variant

filePath = InputBox("Please enter a complete file path for the file you would like to import into ACCESS.&quot
tblNAME = InputBox("Please enter a name for the table you are importing.&quot

DoCmd.TransferText _
TransferType:=acImportDelim, _
tableName:=tblNAME, _
FileName:=filePath

End Sub

* I through in the input boxes but so the standard way would be:

Sub ImportText()
DoCmd.TransferText _
TransferType:=acImportDelim, _
TableName:="tblCustomerText", _
FileName:=APPPATH & "\Customer.txt"
End Sub

You can also throw in HasFieldHeadings:=False to avoid the first line of the file being the field names!!

Thanks again man...I appreciate your help as I get started as a VBA developer in ACCESS...

psuiverson