How to create a function and pass table and import

[Gil149]

Access noob here with two questions...

1) I use the code below, out of a book, that does EXACTLY what I want and I can redo the portions of the code as needed. But, is it better to import data into a table then query it out, or to import the data correctly with no need to query it out. The code below imports the data directly from text files that are static and unchanging, just the number of rows change.

2) The code below says to create a function and pass the table name through the function...I am not sure how to do that. It seems as though this should work in the Sub, similar to Excel.

Thanks guys.

Public Sub ParseOrdersExtractFile(fileName As String)


    Const DividerCharacter As String = "-"
    Const delimiter As String = "|"
    Const DividerLength As Long = 128
    
    Dim hFile As Long
    Dim lineItem As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim rgItems() As String
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("DatatypeParseExampleDestinationTable")
    
    On Error GoTo Err_Handler
    
    hFile = FreeFile
    Open fileName For Input As hFile




    '// Skip the first three rows (header data)
    Line Input #hFile, lineItem
    Line Input #hFile, lineItem
    Line Input #hFile, lineItem
    
    '// Loop through the remainder of the data file
    Do While Not EOF(hFile)
        Line Input #hFile, lineItem
        
        '// if this is a divider line, skip it
        If String(DividerLength, DividerCharacter) <> lineItem Then
            rgItems = Split(lineItem, delimiter)
            
            '// prepare recordset for new row
            rs.AddNew
            With rs
                '// skip the first item (rgItems(0)), since it's empty
                
                '// Parse OrderID into a Long
                rs("OrderID") = CLng(rgItems(1))
                    
                '// Parse CustomerID into a string (no conversion, but trim the contents)
                rs("CustomerName") = Trim$(rgItems(2))
                    
                '// Parse OrderDate into a date field
                rs("DateOrdered") = CDate(Trim$(rgItems(3)))
                    
                '// Parse ShippedDate into a date field (don't bother to trim
                '// it first; notice that conversion functions handle whitespace
                '// gracefully)
                rs("DateShipped") = CDate(rgItems(4))
                    
                '// Parse Freight into a currency field
                rs("Freight Charges") = CCur(rgItems(5))
                
                '// Parse Ontime into a boolean field
                rs("Was the order shipped on time?") = ConvertBoolean(rgItems(6))
            End With
            
            '// Commit the record
            rs.Update
        End If
    Loop
    
Cleanup:
    On Error Resume Next
    Close #hFile
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    
Terminate:
    On Error GoTo 0
    Exit Sub
    
Err_Handler:


    Select Case Err.Number
        Case 3022:  '// Duplicate record in table
            '// cancel the addnew
            rs.CancelUpdate
            If MsgBox("An entry already exists for this record.", vbOKCancel, Err.Number & ": " & Err.Description) = vbCancel Then
                GoTo Cleanup
            End If
            Resume Next
        Case Else
            MsgBox Err.Description, vbCritical, Err.Number
            Resume: GoTo Cleanup
    End Select
    
    Resume Next


End Sub

 

[Gil149]

Solved