Please help - import text file to access in code

[velkave]

Is there an easier way to write a function to import a text file onto an access database? Somebody plesae help me - this thing is killing me. Please Please Please!

[locutus]

This should work for you, it is easiest to set up the definition of the table first in access or visdata (ie set fields)this table is then used as the "blueprint" for the textfile

within access an external data source (such as a sequential text file can
be attached to a table definition)

Option Explicit
Dim mydb As Database
Dim myrs As Recordset 'defining database and recordset objects


Private Sub Command1_Click()
Dim tdf As TableDef 'define tabledef object
Dim mystring As String
Set mydb = OpenDatabase("c:\begDB\contacts.mdb") 'set database object to point to real db

On Error GoTo errorhandler
Set tdf = mydb.CreateTableDef("mytable") ' blueprint to base tabledef on
tdf.Connect = "text;database=c:\Mark\;" ' connect to dir holding txt file
tdf.SourceTableName = "abc.txt" ' actual text file to use
mydb.TableDefs.Delete ("mytable") ' delete so the new table can be attached (we can't have 2)
mydb.TableDefs.Append tdf ' attach newly populated table
Exit Sub


errorhandler: ' to catch errors and stop crashes
mystring = "crashed and burned baby!" & vbCrLf
mystring = mystring & "error number: " & Err.Number & vbCrLf
mystring = mystring & "error description: " & Err.Description
MsgBox mystring

Exit Sub
End Sub


hope this is clear and works for you
> locutus