Excel -> Access mit ADO
16.05.2008 10:58:17
Martin
Danke Euch!
Sub DatenübernahmeNachAccess()
Dim ADOC As New ADODB.Connection
Dim DBS As New ADODB.Recordset
Dim LetzteZeile As Integer, LetzteSpalte As Integer
Dim i As Integer
ADOC.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Test\TEST_DB.mdb;"
DBS.Open "OAAbatementModifier", ADOC, adOpenKeyset, adLockOptimistic
On Error Resume Next
Do Until DBS.EOF
DBS.Delete
DBS.MoveNext
Loop
Sheets("OAAbatementModifier").Activate
Range("A10").Select
LetzteZeile = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
For i = 10 To LetzteZeile
DBS.AddNew
DBS!PropID = ActiveCell.Value
DBS!VersionID = ActiveCell.Offset(0, 1).Value
DBS!AbatemodifierID = ActiveCell.Offset(0, 2).Value
DBS!Name = ActiveCell.Offset(0, 3).Value
DBS!SortOrder = ActiveCell.Offset(0, 4).Value
DBS!Base = ActiveCell.Offset(0, 5).Value
DBS!Step = ActiveCell.Offset(0, 6).Value
DBS!Porters = ActiveCell.Offset(0, 7).Value
DBS!Miscellaneous = ActiveCell.Offset(0, 8).Value
DBS!CPI = ActiveCell.Offset(0, 9).Value
DBS!Sales = ActiveCell.Offset(0, 10).Value
DBS!Reimbursement = ActiveCell.Offset(0, 11).Value
DBS.Update
ActiveCell.Offset(1, 0).Select
Next i
DBS.Close
ADOC.Close
Set ADOC = Nothing
Set DBS = Nothing
End Sub