Public Sub OpenMDB(varsMDBPhatWithFileName As String, variDB As Integer)
On Error GoTo err_flash
Set oDB(variDB) = New ADODB.Connection ' Verbindung aufbauen
With oDB(variDB)
.CursorLocation = adUseClient
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Data Source") = varsMDBPhatWithFileName
.Open
End With
Exit Sub
err_flash:
MsgBox Err.Description, vbCritical, "Hinweis - modADOdb.OpenMDB"
End Sub
Public Sub CloseMDB(variDB As Integer)
On Error GoTo err_flash
oDB(variDB).Close ' Datenbank schliessen
Set oDB(variDB) = Nothing ' Objekt entfernen
Exit Sub
err_flash:
MsgBox Err.Description, vbCritical, "Hinweis - modADOdb.CloseMDB"
End Sub
Public Function WriteData2MDB(varsDataPhatName As String, varsDataFileName As _
String, _
varsMDBPhatName As String, varsMDBFileName As String, varsTableName As _
String, _
Optional variDB As Integer = 1, Optional bFirstFieldIsID As Boolean = True, _
Optional varsTextSeparator As String = ";", Optional varsTextDelimiter As _
String = """", _
Optional varsFeldName As String = "")
' On Error GoTo err_flash
Dim varsFeldNameArray() As String
Dim varsDataPhatWithFileName As String
Dim varsMDBPhatWithFileName As String
Dim varlCreateFields As Long
Dim varlCountFields As Long
Dim variF As Integer
Dim varlSize As Long
Dim varsLine As String
Dim varasData() As String
Dim varlFieldsWithData As Long
Dim varsSQL As String
Err.Clear
' FeldNamen Array erstellen
varsFeldNameArray = Split(varsFeldName, varsTextSeparator, , _
vbTextCompare)
' Dataa PhatName & FileName
varsDataPhatWithFileName = SetVarsPhatWithFileName(varsDataPhatName, _
varsDataFileName)
' MDB PhatName & FileName
varsMDBPhatWithFileName = SetVarsPhatWithFileName(varsMDBPhatName, _
varsMDBFileName)
' Datenbank öffnen
OpenMDB varsMDBPhatWithFileName, variDB
' Daten auslesen
variF = FreeFile
Open varsDataPhatWithFileName For Input As #variF
varlSize = LOF(variF)
While Not EOF(variF)
Line Input #variF, varsLine
If varsLine <> "" Then
' Daten aus Datei in Array splitten
varasData = Split(varsLine, varsTextSeparator)
' Fields erstellen falls nicht vorhanden
If UBound(varasData) > varlCountFields Then
For varlCreateFields = varlCountFields To UBound( _
varasData)
oDB(variDB).Execute "ALTER TABLE " & _
varsTableName & " ADD COLUMN " & _
varsFeldNameArray(varlCreateFields) & " TEXT(" & _
"255)"
Next
varlCountFields = UBound(varasData)
End If
' Daten in die Datenbank schreiben
varsSQL = ""
For varlFieldsWithData = LBound(varasData()) To UBound( _
varasData())
varsSQL = varsSQL & "'" & varasData( _
varlFieldsWithData) & "', "
Next ' varlFieldsWithData
varsSQL = Left$(varsSQL, Len(varsSQL) - 2)
oDB(variDB).Execute "INSERT INTO " & varsTableName & "" & _
"VALUES (" & varsSQL & ");"
End If
DoEvents
Wend
' Datenbank schliessen
CloseMDB variDB
If Err.Number = 0 Then
WriteData2MDB = True
Exit Function
End If
err_flash:
MsgBox Err.Description, vbCritical, "Hinweis - modADOdb.WriteData2MDB"
End Function*greetz*
Tim
.
http://www.DotNetWorld.de |