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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  ( Here (http…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

758 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

22 Experts available now in Live!

Get 1:1 Help Now