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.
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.
Code:
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