Here's code that will convert a comma-delimited text file to Access 97. When I try to convert it to Access 2000, I get "Error Opening Database". I can't figure it out. What am I missing...
Private Sub cmdImport_Click()
Dim delimiter As String
Dim wks As Workspace
Dim db As Database
Dim fnum As Integer
Dim text_line As String
Dim sql_statement As String
Dim pos As Integer
Dim num_records As Long
delimiter = cboDelimiter.Text
If Len(delimiter) = 0 Then
MsgBox "Please select a delimiter"
Exit Sub
End If
If delimiter = "<space>" Then delimiter = " "
If delimiter = "<tab>" Then delimiter = vbTab
' Open the text file.
fnum = FreeFile
On Error GoTo NoTextFile
Open txtTextFile.Text For Input As fnum
' Open the database.
On Error GoTo NoDatabase
' Set wks = DBEngine.Workspaces(0)
Set wks = DBEngine.Workspaces(0)
Set db = wks.OpenDatabase(txtDatabaseFile.Text)
On Error GoTo 0
' Read the file and create records.
Do While Not EOF(fnum)
' Read a text line.
Line Input #fnum, text_line
If Len(text_line) > 0 Then
' Build an INSERT statement.
sql_statement = "INSERT INTO " & _
txtTable.Text & " VALUES ("
Do While Len(text_line) > 0
pos = InStr(text_line, delimiter)
If pos = 0 Then
' Add the rest of the line.
sql_statement = sql_statement & _
"'" & text_line & "', "
text_line = ""
Else
' Add the next field.
sql_statement = sql_statement & _
"'" & Left$(text_line, pos - 1) & _
"', "
text_line = Mid$(text_line, pos + Len(delimiter))
End If
Loop
' Remove the last comma.
sql_statement = Left$(sql_statement, Len(sql_statement) - 2) & ")"
' Insert the record.
On Error GoTo SQLError
db.Execute sql_statement
On Error GoTo 0
num_records = num_records + 1
End If
Loop
' Close the file and database.
Close fnum
db.Close
wks.Close
MsgBox "Inserted " & Format$(num_records) & " records"
Exit Sub
NoTextFile:
MsgBox "Error opening text file."
Exit Sub
NoDatabase:
MsgBox "Error opening database."
Close fnum
Exit Sub
SQLError:
MsgBox "Error executing SQL statement '" & _
sql_statement & "'"
Close fnum
db.Close
wks.Close
Exit Sub
End Sub
Private Sub Form_Load()
' Enter default file and database names.
txtTextFile.Text = App.Path & "\testdata.txt"
txtDatabaseFile.Text = App.Path & "\testdata2.mdb"
End Sub
Text file has the following info:
REF*87*X096
ZAP*R0103*9
ZAP*R0104*INFO34
