Sandra Smith
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("M ainMenu"). Range("B1" )
'========================= ========== ========== =====
strConnectString = "Provider=Microsoft.Jet.OL EDB.4.0;Da ta Source=" & strDBPath & ";"
'Connect Database; insert a new table
Set objConnection = New ADODB.Connection
With objConnection
.Open strConnectString
'HOW TO GET THIS RUN??
''If fntDoesObjectExist("tblTEM PKMQuantit ites", "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
Sandra
'Set database name and DB connection string--------
strDBPath = ThisWorkbook.Worksheets("M
'=========================
strConnectString = "Provider=Microsoft.Jet.OL
'Connect Database; insert a new table
Set objConnection = New ADODB.Connection
With objConnection
.Open strConnectString
'HOW TO GET THIS RUN??
''If fntDoesObjectExist("tblTEM
.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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Sandra
ASKER
Sandra