SQL Insert Statement > Start Insert Records from Row(3) and onwards

bemp87

Board Regular
Joined
Dec 10, 2016
Messages
100
Hi Community,

I need some help running a SQL statement to insert rows starting at Row #3 . The statement works and will insert records, but I can't get it to start inserting at Row #3 which is messing up the data that's being put into the Access Database. Any guidance?

Code:
Public Sub ShiftSwap()

Set rg = Worksheets("ShiftSwap").Range("A3")


Set cn = CreateObject("ADODB.Connection")
dbPath = Application.ActiveWorkbook.Path & "\ShiftSwapDB.mdb"
dbWb = Application.ActiveWorkbook.FullName
dbWs = Application.ActiveSheet.Name
scn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath
dsh = "[" & Application.ActiveSheet.Name & "$]"
cn.Open scn
ssql = "INSERT INTO ShiftSwap ([Req_Key], [Submitted_Date], [Swap_Req_Date], [Swap_Req_Shift], [Swap_Day_Work], [Swap_Req_Time]) "
ssql = ssql & "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh






cn.Execute ssql




End Sub
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,751
I'm not sure if this macro will do exactly what you require, or whether I've completely understood, because you haven't given any details about your Access table (e.g. the Req_Key column, which I assume is the primary key) and your Excel sheet data. Try the macro on a copy of your Access database (ShiftSwapDB_COPY.mdb in the code).

This might not be the best or most efficient method. The macro first creates a temporary sheet containing the existing ShiftSwap records and the data rows from the data sheet (the active sheet), then deletes all records in the ShiftSwap table (that's why you should test it on a copy of your database), then inserts all data rows from the temporary sheet to the ShiftSwap table. See the comments for more details about this sequence.

Code:
Public Sub Update_ShiftSwap_Table()

    Dim DBconn As Object
    Dim DBrs As Object
    Dim DataSheet As Worksheet
    Dim AccessDbFullName As String, ExcelWorkbookFullName As String
    Dim SQL As String
    Dim TempSheet As Worksheet
    Dim lr As Long
    Dim copyFrom As Range, copyTo As Range
    
    With ActiveWorkbook
        Set DataSheet = .ActiveSheet
        AccessDbFullName = .Path & "\ShiftSwapDB_COPY.mdb"
        ExcelWorkbookFullName = .FullName
    End With
        
    Set DBconn = CreateObject("ADODB.Connection")
    DBconn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & AccessDbFullName & ";"
        
    'Read ShiftSwap table into a recordset
    
    SQL = "SELECT * FROM ShiftSwap ORDER BY [Req_Key]"
    Set DBrs = DBconn.Execute(SQL)
    
    'Add a temporary sheet which will be populated with the ShiftSwap table recordset and data rows from the Data sheet
    
    On Error Resume Next
    Set TempSheet = ThisWorkbook.Worksheets("Temp")
    On Error GoTo 0
    If TempSheet Is Nothing Then
        Set TempSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count))
        TempSheet.Name = "Temp"
    Else
        TempSheet.Cells.Clear
    End If
    
    With DataSheet
        
        'Copy first row (containing column names) from Data sheet to row 1 in Temp sheet
        
        Set copyFrom = .Range("A1:F1")
        Set copyTo = TempSheet.Range("A1")
        copyTo.Resize(copyFrom.Rows.Count, copyFrom.Columns.Count).Value = copyFrom.Value
         
        'Copy ShiftSwap recordset to row 2 in Temp sheet
        
        Set copyTo = copyTo.Offset(1)
        copyTo.CopyFromRecordset DBrs
        
        'Copy data rows from Data sheet columns A:F to row 4 in Temp sheet, overwriting existing data in row 4 and below
        
        Set copyTo = copyTo.Offset(2)
        lr = .Cells(.Rows.Count, "A").End(xlUp).row
        Set copyFrom = .Range("A2:F" & lr)
        copyTo.Resize(copyFrom.Rows.Count, copyFrom.Columns.Count).Value = copyFrom.Value
        
    End With
    
    'Delete all records in ShiftSwap table
    
    SQL = "DELETE * FROM ShiftSwap"
    DBconn.Execute SQL
    
    'Insert all records on Temp sheet into ShiftSwap table
    
    SQL = "INSERT INTO ShiftSwap ([Req_Key], [Submitted_Date], [Swap_Req_Date], [Swap_Req_Shift], [Swap_Day_Work], [Swap_Req_Time]) " & _
          "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & ExcelWorkbookFullName & "].[" & TempSheet.Name & "$]"
    DBconn.Execute SQL
   
    DBconn.Close
    
    Set DBrs = Nothing
    Set DBconn = Nothing
    
    'Delete the Temp sheet
    
    Application.DisplayAlerts = False
    TempSheet.Delete
    Application.DisplayAlerts = True
    
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,129,497
Messages
5,636,662
Members
416,935
Latest member
Atulcp

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top