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:

Troubleshooting
Research
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
    'eventual---------------------------------------------------------------------------------------
    '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"
                    Err.Clear
                    Exit Sub
                    
                Else
                    'database and table exists.....so
                    Call transferDataP2
                End If
        
        Else

            '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
            a.Quit
            
            '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()

    Sheets("Data").Activate
    Sheets("Data").Select
    
    '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.AddNew
                    rs("Field1").Value = PKey
                Else
                    rs.Edit
                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)
                    Next
            rs.Update
        
            iRow = iRow + 1
        Loop

    rs.Close
    db.Close
    
    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
ASKER CERTIFIED SOLUTION
Avatar of rockiroads
Commented:
This problem has been solved!
Unlock 1 Answer and 9 Comments.
See Answers