VBA Code - MS Access 2007 Insert Query

Posted on 2010-11-22
Last Modified: 2012-05-10

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

Question by:Radshaykho
  • 2
LVL 12

Accepted Solution

ErezMor earned 500 total points
ID: 34194541
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.

Author Comment

ID: 34197966
Yep, it works!

Thank you,



Author Comment

ID: 34198413
I may have pushed the wrong button?.

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
My experience with Windows 10 over a one year period and suggestions for smooth operation
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now