Solved

VBA Code - MS Access 2007 Insert Query

Posted on 2010-11-22
3
698 Views
Last Modified: 2012-05-10
Hi,

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.

Rs

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

.Open

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

    rs.update

  Else

    MsgBox "Value in Cell A " & i & "is empty!, Row was not updated"

 End If



rs.Close

Set rs = Nothing

cn.Close

Set cn = Nothing



End Sub

Open in new window

0
Comment
Question by:Radshaykho
  • 2
3 Comments
 
LVL 12

Accepted Solution

by:
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.
0
 

Author Comment

by:Radshaykho
ID: 34197966
Yep, it works!

Thank you,

RS

0
 

Author Comment

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

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