Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Access - delete all entries in table from excel macro

Avatar of brukilla
brukillaFlag for United States of America asked on
Microsoft AccessMicrosoft Excel
9 Comments1 Solution695 ViewsLast Modified:
I am having trouble getting the DoCmd line to delete all entries in a table before I repopulate it with new data.  I am getting the error, "the runSQl was cancelled at line 78.  prog-Template.mdb test-sheet.xls
Option Explicit

Dim dbPath07 As String
Dim dbName07 As String
Dim dbPath03 As String
Dim dbName03 As String
Dim dbTable As String
Dim progAC As String
Dim db As Database
Dim td As TableDef
Dim nRow As Integer
Dim wbPath As String
Dim a As Object
Dim PKey
Dim rs As dao.Recordset
Dim sSQL As String, iSQL As String, j As Integer
Dim iRow As Integer
Dim iStartCol As Integer
Dim dbTemplate As String
Dim dbOOB As String
Dim dbNMCI As String
Dim dbfailonerrors

Sub transferDataP1()
    progAC = Sheets("other").Range("A1").Value
    'dbOOB = "Z:\somefolder\"
    'dbNMCI = "Z:\somefolder"
    'dbName07 = progAC & ".accdb"
    'dbName03 = progAC & ".mdb"
    'dbPath07oob = dbOOB & dbName07
    'dbPath03oob = dbOOB & dbName07
    'dbPath07nmci = dbNMCI & dbName03
    'dbPath03nmci = dbNMCI & dbName03
    'for now
    dbName03 = progAC & ".mdb"
    dbPath03 = "c:\DEAP\" & dbName03
    dbTable = progAC & "_VP_table"
    dbTemplate = "c:\DEAP\prog_Template.mdb"
        If FileExists1(dbPath03) Then
            Set db = OpenDatabase(dbPath03)
                'If GetVersion() > "11" Then
                    'MsgBox "ACCESS 2007 VER: " & GetVersion
                'End If
            On Error Resume Next
            Set td = db.TableDefs(dbTable)
                If Err.Number <> 0 Then 'table not found
                    'call something to create the specific table
                    'needs to open template file, then transfer the template table to 'dbPath03'
                    MsgBox "table not found"
                    Exit Sub
                    'database and table exists.....so
                    Call transferDataP2
                End If

            'create db from template file and create tables from template.....rename db and tables....then call transferDataP2
            MsgBox "file does not exist"
            FileCopy dbTemplate, dbPath03 'copy the template file to the new destination with new name
            Set db = OpenDatabase(dbPath03)
            db.Execute ("DELETE * FROM table2"), dbfailonerrors

            Set a = CreateObject("access.application") 'seems to be necessary in order to run DoCmd
            a.OpenCurrentDatabase (dbPath03) 'seems to be necessary in order to run DoCmd
            DoCmd.Rename "new_name_table", acTable, "table2"
            DoCmd.RunSQL "DELETE new_name_table.* FROM new_name_table;" 'this will delete all records from a table with the name new_name_table
            'Call transferDataP2
        End If
    'Docmd.runSQL "DELETE UnitSize.* FROM UnitSize;" 'this will delete all records from a table with the name UnitSize
    'Call screenON
    'Call protectWBandWS

End Sub

Sub transferDataP2()

    'get the amount of rows with data
    nRow = Worksheets("Data").Range("BG8:BG507").Cells.SpecialCells(xlCellTypeConstants).count
    'take into account the starting place of BG8
    nRow = nRow + 8
    wbPath = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
    'Set db = OpenDatabase(dbPath03)
    iRow = 8
    iStartCol = 45
    sSQL = "SELECT * FROM " & dbTable
    Set rs = db.OpenRecordset(sSQL)
        Do While iRow < nRow
            PKey = Range("AS" & iRow).Value
            Debug.Print "key", PKey
            rs.FindFirst "field1='" & PKey & "'"
                'Record does not exist
                If rs.NoMatch = True Then
                    rs("Field1").Value = PKey
                End If
                    For j = 1 To rs.Fields.count - 1
                        Debug.Print "Setting", rs("field" & j + 1).Name, Worksheets("Data").Cells(iRow, j + iStartCol)
                        rs("field" & j + 1).Value = Worksheets("Data").Cells(iRow, j + iStartCol)
            iRow = iRow + 1

    Set rs = Nothing
    Set db = Nothing

End Sub

Public Function FileExists1(Fname) As Boolean
    If Fname = "" Or Right(Fname, 1) = "\" Then
        FileExists1 = False: Exit Function
    End If
    FileExists1 = (Dir(Fname) <> "")

End Function

Function GetVersion() As String
    GetVersion = SysCmd(acSysCmdAccessVer)
End Function
Avatar of rockiroads
This problem has been solved!
Unlock 1 Answer and 9 Comments.
See Answers