I want to automate this process in order to reduce the risk of mistakes.
This is my first day I'm using VBA so my skills are somewhat lacking. I'm trying to automate some steps in my work, and therefore I need to copy 1 column from Access and paste it into an Excel worksheet. And after the mutations, I have to copy/paste the results back into Access (another column in the same Table). I've been looking for a working VBA code, and testing them, but until now I haven't found any.
enter code here
Const TARGET_DB = "test.accdb"
Sub Test()
Dim cnn As ADODB.Connection
Dim MyConn
Dim rst As ADODB.Recordset
Dim i As Long, j As Long
Dim Rw As Long
Sheets("Informatie").Select
Rw = Range("A2", Range("A2").End(xlDown)).Select
Set cnn = New ADODB.Connection
MyConn = ThisWorkbook.Path & Application.PathSeparator & TARGET_DB
With cnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Open MyConn
End With
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Open Source:="Table1", ActiveConnection:=cnn, _
CursorType:=adOpenDynamic, LockType:=adLockOptimistic, _
Options:=adCmdTable
'Load all records from Excel to Access.
For i = 2 To Rw
rst.AddNew
For j = 1 To 7
rst(Cells(1, j).Value) = Cells(i, j).Value
Next j
rst.Update
Next i
'Close the connection
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
MsgBox "Alan's work is finished for today"
I expected to see some mutations in the Access file. Any mutation would be a start. But nothing happened apart from the fact I selected the correct range in the Excel worksheet.
