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

[bemp87]

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?

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

 

[John_w]

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.

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