conversion: *.*.txt to *.*.xls or *.*mdb

Private Sub Command1_Click()
Dim conImport As ADODB.Connection
Dim lngFileNumber As Long
Dim strLine As String
Dim varFields As Variant

' Create and open database object.
Set conImport = New ADODB.Connection
' Change this to reflect your server and database names.
conImport.Open "Provider=SQLOLEDB; Data Source=IS-DEV1; Initial Catalog=PaulsTestData; Integrated Security=SSPI;Persist Security Info=False"

' Get a valid file handle.
lngFileNumber = FreeFile
' Open file (change this path for your text file).
Open "e:\temp\test.txt" For Input As #lngFileNumber

' Grab lines of text until end of file.
Do While Not EOF(lngFileNumber)
' Grab a line from the file.
Line Input #lngFileNumber, strLine
' Split the line into fields based on the space character.
varFields = Split(strLine, " ")
' Insert the fields into the database table (Change the table and field names below to match yours).
' NOTE that numeric field values don't need to be surrounded by single quotes and both of the fields below are being treated as string/text values.
conImport.Execute "INSERT INTO Test3 (Field1, Field2) VALUES ('" & varFields(0) & "', '" & varFields(1) & "')"
Loop

' Close the text file.
Close #lngFileNumber

' Close and destroy database object.
conImport.Close
Set conImport = Nothing

MsgBox "Finished!", vbInformation, "Import"

End Sub

    max_row = excel_sheet.UsedRange.Rows.Count
    max_col = excel_sheet.UsedRange.Columns.Count
Use ADO to open the database. 

For each row in the Excel spreadsheet, loop through the row's columns composing an SQL INSERT statement. Use the ADO Connection object to execute the statement and create the record. 
 
 
 
Private Sub cmdLoad_Click()
Dim excel_app As Object
Dim excel_sheet As Object
Dim max_row As Integer
Dim max_col As Integer
Dim row As Integer
Dim col As Integer
Dim conn As ADODB.Connection
Dim statement As String
Dim new_value As String

    Screen.MousePointer = vbHourglass
    DoEvents

    ' Create the Excel application.
    Set excel_app = CreateObject("Excel.Application")

    ' Uncomment this line to make Excel visible.
'    excel_app.Visible = True

    ' Open the Excel spreadsheet.
    excel_app.Workbooks.Open FileName:=txtExcelFile.Text

    ' Check for later versions.
    If Val(excel_app.Application.Version) >= 8 Then
        Set excel_sheet = excel_app.ActiveSheet
    Else
        Set excel_sheet = excel_app
    End If

    ' Get the last used row and column.
    max_row = excel_sheet.UsedRange.Rows.Count
    max_col = excel_sheet.UsedRange.Columns.Count

    ' Open the Access database.
    Set conn = New ADODB.Connection
    conn.ConnectionString = _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & txtAccessFile.Text & ";" & _
        "Persist Security Info=False"
    conn.Open

    ' Loop through the Excel spreadsheet rows,
    ' skipping the first row which contains
    ' the column headers.
    For row = 2 To max_row
        ' Compose an INSERT statement.
        statement = "INSERT INTO Books VALUES ("
        For col = 1 To max_col
            If col > 1 Then statement = statement & ","
            new_value = Trim$(excel_sheet.Cells(row, _
                col).Value)
            If IsNumeric(new_value) Then
                statement = statement & _
                    new_value
            Else
                statement = statement & _
                    "'" & _
                    new_value & _
                    "'"
            End If
        Next col
        statement = statement & ")"

        ' Execute the INSERT statement.
        conn.Execute statement, , adCmdText
    Next row

    ' Close the database.
    conn.Close
    Set conn = Nothing

    ' Comment the Close and Quit lines to keep
    ' Excel running so you can see it.

    ' Close the workbook saving changes.
    excel_app.ActiveWorkbook.Close True
    excel_app.Quit

    Set excel_sheet = Nothing
    Set excel_app = Nothing

    Screen.MousePointer = vbDefault
    MsgBox "Copied " & Format$(max_row - 1) & " values."
End Sub