Why is this function only working some of the time?


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 8 of 8

Thread: Why is this function only working some of the time?

  1. #1
    Join Date
    Jun 2004
    Location
    Houston area
    Posts
    557

    Why is this function only working some of the time?

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

    Code:
    
            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
    
    Code:
    
        '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?
    Last edited by Laurel; 06-22-2005 at 01:58 PM.
    A balanced diet is a cookie in each hand.

  2. #2
    Join Date
    Nov 2003
    Location
    Alameda, CA
    Posts
    1,737
    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
    "There are two ways to write error-free programs. Only the third one works."
    Unknown

  3. #3
    Join Date
    Jun 2004
    Location
    Houston area
    Posts
    557
    TYVM, marco <wink>

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

    L
    A balanced diet is a cookie in each hand.

  4. #4
    Join Date
    Jun 2004
    Location
    Houston area
    Posts
    557
    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:

    Code:
    
        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
    A balanced diet is a cookie in each hand.

  5. #5
    Join Date
    Nov 2003
    Location
    Alameda, CA
    Posts
    1,737
    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
    "There are two ways to write error-free programs. Only the third one works."
    Unknown

  6. #6
    Join Date
    Jun 2005
    Location
    Philippines
    Posts
    22
    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.

  7. #7
    Join Date
    Feb 2004
    Location
    Sydney, Australia
    Posts
    498
    Laurel

    Yes

    Greg

  8. #8
    Join Date
    Dec 2003
    Posts
    2,750
    Quote Originally Posted by Laurel

    I'm befuddled and unamused! >:[

    Laurel
    What happens if you delete the table via SQL?

    DROP TABLE TableName

    Quote Originally Posted by Laurel
    P.S. Did you miss me?
    Uh, who are you? ;-)
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center