On a computer without Access, how do I import a csv into an access table? (using vb.net 2005)
Posted on 2007-07-23
Ok, I have this program which downloads csv files in order to update it's tables (in an access db). I want to import these csvs into my access db, (this is all in vb.net 2000).
First I tried the Access 11.0 library, but when I tried running the code to automate access and execute my macro to transferText it erred out. This is the code I was using:
Private Sub CallMacroInAccess(ByVal dbPath$, ByVal csvPath$, ByVal tableName$)
Dim oAccess As Access.ApplicationClass
'Start Access and open the database.
oAccess = New Access.ApplicationClass
'oAccess.Visible = True
'You will need to put the path to your own database here.
'Run the macro.
If tableName = "AffInfo" Then
' "", tableName, csvPath)
oAccess = Nothing
Anyways, I need to know how to do a TransferText into my access db when there Access is NOT installed on the computer.. Does anyone know a good fast way to do this? (My csvs are over 400,000 records apiece, and semi-sizable.)
Another thing I've tried is this builtin csv reader.. here is the code for that. It was agonizingly slow but it did work.. (perhaps because my csv has fields which include vbcrlf inside them to seperate strings, that is my theory..)
Private Function LoginForm_importCSV2(ByVal pathName As String, ByVal AffTrue_SplitFalse As Boolean) As Long
Me.ProgressBar1.Value = 0
Me.ProgressBar1.Maximum = countLinesInTextFile(pathName)
Me.ProgressBar1.Visible = True
Dim recordsAdded As Long = 0
Dim fields As String(), fieldNames As String()
Dim delimiter As String = ","
gImportDupKnt = 0
Using parser As New FileIO.TextFieldParser(pathName)
fieldNames = parser.ReadFields()
While Not parser.EndOfData
' Read in the fields for the current line
fields = parser.ReadFields()
Me.ProgressBar1.Value = Me.ProgressBar1.Value + 1
If UBound(fieldNames) <> UBound(fields) Then Stop
'If myDB.writeImportSet(fieldNames, fields) = True Then
Dim result As Boolean
If AffTrue_SplitFalse = True Then
Debug.Print(Replace(Join(fields, ","), vbCrLf, "|"))
result = write_AffInfo(fieldNames, fields)
result = write_SplitNames(fieldNames, fields)
If result = True Then
recordsAdded = recordsAdded + 1
gImportDupKnt += 1
Me.fldDoing.Text = recordsAdded & ":" & gImportDupKnt
'importCSV = recordsAdded
Me.ProgressBar1.Visible = False
Private Function write_AffInfo(ByVal fieldnames(), ByVal fieldvalues()) As Boolean
Dim mySQL$, strFields$, strValues$
strFields = UCase(makeListForInsertSQL(fieldnames, "[", "]"))
strValues = UCase(makeListForInsertSQL(fieldvalues, "'", "'"))
mySQL = "INSERT INTO AffInfo " & strFields & " VALUES " & strValues & ";"
myOLEDBCommand = New OleDb.OleDbCommand(mySQL, myOleDBConnection)
Dim result As Long = 0
On Error GoTo myErrHandler
result = myOLEDBCommand.ExecuteNonQuery()
Return IIf(result > 0, True, False)
If InStr(Err.Description, "duplicate") > 0 Then Err.Clear()
What's the best way to do this? Another function I'm worried about is how to compact & repair a db if they don't have access, but perhaps that is a different question..
Thanks Experts! ~ Michael