VBA Code - MS Access 2007 Insert Query


I need to insert the results into another table "S_Table_BK as a back up history for the record, and then continue with the update.

I am not sure in this case if I have to close the connection and open a second connection?

Your advice will be appreciated.


Sub UpdateExcelToAccess(x As Integer, ByVal nw As Date)
Dim newCount As Long
Dim i As Long
Dim cn As New ADODB.Connection
Dim rs As Recordset
Dim sh As Worksheet
Dim WkDate As Date
Dim myData As String
Dim DT As String
Dim RowVal As Integer
DT = Format(nw, "Short Date")
i = x

Set sh = ActiveWorkbook.ActiveSheet
myData = "c:\Seating.accdb"

With cn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Properties("Data Source") = myData
End With

Set rs = New ADODB.Recordset
  RowVal = Cells.Range("R15").Value
  rs.Open "select * from S_Table where S_Table.Row = " & RowVal & " AND Week_Start = #" & nw & "#", cn, adOpenKeyset, adLockOptimistic

Need to insert the results into another table "S_Table_BK then continue

 If Cells.Range("A" & i).Value <> Empty Then
    rs.Fields("Name") = Cells.Range("A" & i).Value
    rs.Fields("Note & Comments") = Cells.Range("B" & i).Value
    rs.Fields("Desk Sharing") = Cells.Range("D" & i).Value
    rs.Fields("Touch Spot") = Cells.Range("C" & i).Value
    rs.Fields("In_Use") = Cells.Range("E" & i).Value
    rs.Fields("Cube") = Cells.Range("F" & i).Value
    rs.Fields("Desk") = Cells.Range("G" & i).Value
    rs.Fields("Fri") = Cells.Range("H" & i).Value
    rs.Fields("Mon") = Cells.Range("I" & i).Value
    rs.Fields("Tues") = Cells.Range("J" & i).Value
    rs.Fields("Wed") = Cells.Range("K" & i).Value
    rs.Fields("Thur") = Cells.Range("L" & i).Value
    rs.Fields("Starting_Date") = Cells.Range("M" & i).Value
    rs.Fields("Ending_Date") = Cells.Range("N" & i).Value
    rs.Fields("Week_Start") = DT
    rs.Fields("Row") = Cells.Range("R15").Value
    rs.Fields("DateStamp") = Now
    MsgBox "Value in Cell A " & i & "is empty!, Row was not updated"
 End If

Set rs = Nothing
Set cn = Nothing

End Sub

Open in new window

Who is Participating?

Improve company productivity with a Business Account.Sign Up

ErezMorConnect With a Mentor Commented:
no, you dont.
a connection can be left open to execute as many commands (or recordsets) as you want, and then only be closed.
which data is to be copied to the bk table? the data existing in the recordset before the update?
if so, and the tables are identical in structure, the connection object itself can do the trick:

cn.Execute("Insert Into S_Table_BK select * from S_Table where S_Table.Row = " & RowVal & " AND Week_Start = #" & nw & "#")

if the fields dont match (or you have an autonumber field), then you can list the specific fields from both source and destination tables to be copied.
RadshaykhoAuthor Commented:
Yep, it works!

Thank you,


RadshaykhoAuthor Commented:
I may have pushed the wrong button?.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.