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!
Printable View
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!
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