Hi,

I'm a VBA novice and am really struggling with this....

Anyway. This is what i am trying to achieve. I've managed the first three steps in Excel, but have just found out Access has no macro recorder so i'm now completely stumped. The whole process should be:

Allow the user to browse to a .txt file.
That .txt file is opened in excel -
Excel runs text to columns to sort the data.
Access then imports the data as a table
As this will be run more than once, the previous table must be deleted or completed overwritten during the process

Seperately i can then create an update and an append query to update the database.

Please help! Thanks in advance for your time.

In excel the code to open the text file and sort it to columns is:

 
Sub ImportTextFile()

On Error GoTo ErrTrap
Application.ScreenUpdating = False

' Below allows user to open a txt file.

Dim TxtFileName As String
TxtFileName = GetTextFile("C:\Users\7092\Desktop\")
Workbooks.OpenText Filename:=TxtFileName, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1)), DecimalSeparator:=".", ThousandsSeparator:=",", TrailingMinusNumbers:=True
If Len(TxtFileName) Then
    If Right$(TxtFileName, 4) <> ".txt" Then
        MsgBox "You must select a text file!"
        Exit Sub
    End If
Else
    Exit Sub
End If

'below sorts data from the .txt file into columns

Sheets("sqlexec").Select
    ActiveSheet.Columns("A:A").Select
    Selection.Texttocolumns Destination:=Range("A:A"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 2), Array(7, 1), Array(9, 1), Array(37, 1), Array(65, 1), _
        Array(75, 1), Array(78, 1), Array(81, 1), Array(84, 1), Array(124, 1), Array(164, 1), Array _
        (204, 1), Array(234, 1), Array(244, 1), Array(260, 1), Array(269, 1)), _
        TrailingMinusNumbers:=True
    Cells.Select
    Cells.EntireColumn.AutoFit
    Columns("N:N").Select
    Selection.NumberFormat = "@"
    Columns("P:P").Select
    Selection.Texttocolumns Destination:=Range("P1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(8, 1)), Trailing

'function to retrieve file

Function GetTextFile(sPath) As String
    ChDir sPath
    GetTextFile = Application.GetOpenFilename( _
        FileFilter:="Text Files (*.txt), *.txt,", _
        FilterIndex:=1, _
        Title:="Select A Text File")
End Function