Why is this function only working some of the time?

[Laurel]

Why doesn't this work every time when the same thing DOES work for queries?

        If TableExists(gLookUpsDB, "tmpJobNames") Then gLookUpsDB.TableDefs.Delete "tmpJobNames"
        gLookUpsDB.TableDefs.Refresh

Private Function TableExists(ByVal DB As Database, ByVal TableName As String) As Boolean
Dim bFound As Boolean
Dim td As TableDef
    
    For Each td In DB.TableDefs
        If td.Name = TableName Then
            bFound = True
            Exit For
        End If
    Next

    TableExists = bFound

End Function

    'Check to see if queries exist
    With gLookUpsDB.QueryDefs
        If QueryExists(gLookUpsDB, "PerItemSubReportQuery") Then .Delete "PerItemSubReportQuery"
        If QueryExists(gLookUpsDB, "MiscChargesReportQuery") Then .Delete "MiscChargesReportQuery"
        If QueryExists(gLookUpsDB, "PerItemandMiscChargesReportQuery") Then .Delete "PerItemandMiscChargesReportQuery"
        .Refresh
    End With

Private Function QueryExists(ByVal DB As Database, ByVal QueryName As String) As Boolean
Dim bFound As Boolean
Dim qd As QueryDef
    
    For Each qd In DB.QueryDefs
        If qd.Name = QueryName Then
            bFound = True
            Exit For
        End If
    Next

    QueryExists = bFound

End Function

I'm befuddled and unamused! >:[

Laurel

P.S. Did you miss me?

[mstraf]

Hi Gorgeous,
indeed we did!! How have you been?
I can't help you today, you know I do not do Access (windows, sometimes
Marco

[Laurel]

TYVM, marco <wink>

Yes, the database is an Access database but this is VB6!

L

[Laurel]

I guess I should tell y'all what I'm trying to do!

I'm trying to delete a table if it exists (and I HATE creating and deleting tables!) and then recreating it.

I do the above and then:

    sql = "SELECT DISTINCT PerItemSubReportQuery.JobName INTO tmpJobNames FROM PerItemSubReportQuery;"
    gLookUpsDB.Execute (sql)
    sql = "INSERT INTO tmpJobNames(JobName) Select DISTINCT MiscChargesReportQuery.JobName from MiscChargesReportQuery;"
    gLookUpsDB.Execute (sql)

Sometimes it deletes the existing table and sometimes it doesn't!

OH MY!
L

[mstraf]

the strange (related to your first question) is that "sometimes" works. when you do something like

foreach td in tabledefs
debug.print td.name
next

which results do you get? do you always get all the tables? There isn't anything wrong in the VB code, this is why I suspect the problem is related to the database.

Marco

[triqshot]

Hi,

Maybe the problem is with case sensitivity.

Try converting the case of your table name in the comparison.

Private Function TableExists(ByVal DB As Database, ByVal TableName As String) As Boolean
Dim bFound As Boolean
Dim td As TableDef

For Each td In DB.TableDefs
If UCase(td.Name) = UCase(TableName) Then
bFound = True
Exit For
End If
Next

TableExists = bFound

End Function

Then maybe apply the same with QueryExists if it works.

[gupex]

Laurel

Yes

Greg

[pclement]

I'm befuddled and unamused! >:[

Laurel

What happens if you delete the table via SQL?

DROP TABLE TableName

P.S. Did you miss me?

Uh, who are you? ;-)