Check for a table from Excel that may exit in ACCESS 2003

Sandra Smith
Sandra Smith used Ask the Experts™
on
I have a function, fntDoesObjectExit, in an ACCESS 2003 database.  It checks for the object, in this case a table, and if it exsits, deletes it.  But how can I get Excel to run this line of code?  I need it to run just before the table creation code in an Excel module.  what I have now works, if the table does not exist, but if does, the code throws an error.  Below is the code at it stands now.

Sandra

    'Set database name and DB connection string--------
    strDBPath = ThisWorkbook.Worksheets("MainMenu").Range("B1")
    '==================================================
   
    strConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";"
 
    'Connect Database; insert a new table
    Set objConnection = New ADODB.Connection
    With objConnection
        .Open strConnectString
       
   'HOW TO GET THIS RUN??
''If fntDoesObjectExist("tblTEMPKMQuantitites", "Table") Then DoCmd.DeleteObject acTable, "tblTEMPKMQuantities")
       
       
        .Execute "CREATE TABLE tblTEMPKMQuantities ([KMID] text(10), " & _
                 "[Quantity] Long, " & _
                 "[Description] text(150), " & _
                 "[KMDate] Date, " & _
                 "[UserID] text(15))"
                 
        .Execute "INSERT INTO tblTEMPKMQuantities ( KMID, Description, KMDate, UserID)" & _
                 "SELECT tblKM.KMID, tblKM.Description, #" & dteDate & "#, '" & gstrUserId & "' " & _
                 "FROM tblKM WHERE tblKM.Active = -1 AND tblKM.DeptID = " & intDeptID
    End With
 
    Set objConnection = Nothing
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Top Expert 2009
Commented:
You can run an Access function from Excel VBA as follows:

Public Function UpdateCounterFields(strDBPath)
'Created by Helen Feddema 19-Jun-2009
'Last modified 19-Jun-2009

On Error GoTo ErrorHandler

   Set appAccess = GetObject(, "Access.Application")
   appAccess.OpenCurrentDatabase strDBPath
   appAccess.Run procedure:="UpdateCounterField"
   
ErrorHandlerExit:
   appAccess.Quit
   Set appAccess = Nothing
   Exit Function

ErrorHandler:
   'Access is not running; open Access with CreateObject
   If Err.Number = 429 Then
      Set appAccess = CreateObject("Access.Application")
      Resume Next
   Else
      MsgBox "Error No: " & Err.Number & "; Description: "
      Resume ErrorHandlerExit
   End If

End Function

Open in new window


So, first make an Access function with the code that checks for the table, and deletes it if found, and call that function from Excel.
Sandra SmithRetired

Author

Commented:
I have the function, fntDoesObjectExist, but this is in an If statement - or perhaps I don't understand what you are trying to tell me.

Sandra
Sandra SmithRetired

Author

Commented:
Helen, I got it.  I realized I need to put it in the code that first opens the database before running the CreateKMTemptable procedure.  Works, thank you.

Sandra

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial