Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Deleting error tables (2002)

    Hi,

    I have query that occassionally causes a table listing errors to be formed. The errors are related to the format of certain date fields and cause no problems with the database. My question is is there a way to keep these tables from being generated?
    Thanks,
    Leesha

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Deleting error tables (2002)

    Are you wanting to delete the tables, or stop them being generated?


    You can delete a table with the code:

    Dim strTablename as string
    strTablename ="tblimporterrors" // or whatever it is called
    DoCmd.DeleteObject acTable, strTablename

    I don't know about stopping them being generated. What is the query, and what are you doing with it ?
    Regards
    John



  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Deleting error tables (2002)

    Hi,

    I'd like to stop them from being generated but if that is not possible I'd have them deleted when the database is exited. The names of the tables differ. They are "Query1$_ImportErrors", "Query1$_ImportErrors1" and so on. The number of tables that are created varies each time the database is openned and depends on whether the user is importing this particuar table or not.

    >>I don't know about stopping them being generated. What is the query, and what are you doing with it ?

    The database imports various excel spreadsheets that have been exported from another program written is SQL. This data is then manipulated for various reports, and at times stored for trending over time. There are occassionally rows of data, usually within [birthdate] or [injurydate] where the format is not the same as in the table. It may be 4-5 rows that are different out of thousands and it does not consistenly happen. Since this isn't data that I need, I haven't spent a lot of time trying to work around it. What I do want to do is delete the tables in code, probably on exit from the database so that they don't build over time.

    I'm wondering if there is a way to use your code so that it deletes any tables starting with "Query" vs specifically naming the table. Sometimes there are no tables generated and other times there are many.

    Thanks,
    Leesha

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Deleting error tables (2002)

    Try this:

    Sub DeleteImportErrorTables()
    Dim dbs As DAO.Database
    Dim i As Long
    Set dbs = CurrentDb
    For i = dbs.TableDefs.Count -1 To 0 Step -1
    If Instr(dbs.TableDefs(i).Name, "ImportErrors") > 0 Then
    dbs.TableDefs.Delete dbs.TableDefs(i).Name
    End If
    Next i
    Set dbs = Nothing
    End Sub

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Deleting error tables (2002)

    You are in dead wonderful! That worked like a charm!

    Thanks a bunch,
    Leesha

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •