Avatar of Sandra Smith
Sandra Smith
Flag for United States of America asked on

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

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
Microsoft DevelopmentMicrosoft ApplicationsMicrosoft Excel

Avatar of undefined
Last Comment
Sandra Smith

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Helen Feddema

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Sandra Smith

ASKER
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 Smith

ASKER
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
Your help has saved me hundreds of hours of internet surfing.
fblack61