Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2008
    Posts
    78

    Unanswered: Deleting Temporary Tables

    Is there a way to partially qualify a table name and have access delete them?

    After running some code/macros, access is creating tables like this:

    Scotch3M_ExportErrors
    Scotch3M_ExportErrors1
    Scotch3M_ExportErrors2
    Scotch3M_ExportErrors3

    and so on.

    I've examined these files and have determined that these aren't errors I need to worry about. I want to just remove these files at the end of my process. My process may or may not cause these files to be created every time I run it.

    Any easy way in VB? I'll just put it in my cleanup routine as my code nears completion.

    Thanks!

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    fuction DeleteTables()
    on error resume next
    Domd.deleteobject acTable, "Scotch3M_ExportErrors"
    DoCmd.DeleteObject acTable, "Scotch3M_ExportError1"
    Domd.deleteobject acTable, "Scotch3M_ExportErrors2"
    DoCmd.DeleteObject acTable, "Scotch3M_ExportError3"
    on error goto Done
    Done:
    end function

    and then just call this function (or imbed the above code where you need to.
    Last edited by pkstormy; 10-05-08 at 23:41.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Aug 2008
    Posts
    78
    Quote Originally Posted by pkstormy
    fuction DeleteTables()
    Domd.deleteobject acTable, "Scotch3M_ExportErrors"
    DoCmd.DeleteObject acTable, "Scotch3M_ExportError1"
    Domd.deleteobject acTable, "Scotch3M_ExportErrors2"
    DoCmd.DeleteObject acTable, "Scotch3M_ExportError3"
    end function

    and then just call this function (or imbed the above code where you need to.
    Thank for the reply. I should have been a little clear. I wanted to partially qualify the table name. I won't know how many ExportError files there are.

    I tried DoCmd.deleteobject acTable, "Scotch3M_ExportErrors*"

    but of course that didnt work. any ideas?

  4. #4
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    You should (under some circumstances) be able to query the Catalog object for a list of tables. Some of the references i have found state that you can only do this in ADOX. FWIW, I attach a clip of VB code which I got from a site Bytes.com. make of it what you will (or can!).

    Sub ListTablesADO()
    Dim cnn As ADODB.Connection:
    Set cnn = New ADODB.Connection
    Dim rsTables As ADODB.Recordset
    Dim rsColumns As ADODB.Recordset
    'Open connection you want To get database objects
    cnn.Provider = "MSDASQL" ' whatever needed...
    cnn.Open "DSN=...;Database=...;", "UID", "PWD"
    'Get all database tables. Set
    rsTables = cnn.OpenSchema(adSchemaTables)
    Do While Not rsTables.EOF
    'Get all table columns.
    Set rsColumns = cnn.OpenSchema(adSchemaColumns, _
    Array(Empty, Empty, "" & rsTables("TABLE_NAME")))
    Do While Not rsColumns.EOF
    Debug.Print rsTables("TABLE_NAME") & ", " & _
    rsColumns("COLUMN_NAME")
    rsColumns.MoveNext
    Loop
    rsTables.MoveNext
    Loop
    End Sub

    Sub ListTablesADOX()
    Dim cnn As ADODB.Connection
    Set cnn = New ADODB.Connection
    'Open connection you want To get database objects
    cnn.Provider = "MSDASQL" ' whatever you need...
    cnn.Open "DSN=...;Database=...;", "UID", "PWD"
    'Create catalog object
    Dim Catalog As New ADOX.Catalog
    Set Catalog.ActiveConnection = cnn
    'List tables And columns
    Dim tbl As ADOX.Table
    Dim col As ADOX.Column
    For Each tbl In Catalog.Tables
    Debug.Print tbl.Name
    For Each col In tbl.Columns
    Debug.Print col.Name
    Next
    Next
    End Sub

    You might be able to do a wild-card query on the catalogue. Otherwise, you could copy all the names into a temporary table and query that.

  5. #5
    Join Date
    Aug 2008
    Posts
    78
    That's sticky looking - being a relative newbie at this.

    Is there a way to test if a table exists? Since the error files are named in a predictable way, I'm hoping I could code something along the lines of :

    Code:
    errorFile = 'Scotch3M_ImportErrors'
    errorFileCTR = 0
    
    
    Do While TableExists(errorFile)
       DoCmd.DeleteObject acTable, errorFile
       errorFileCTR = errorFileCTR + 1
       errorFile = errorFile & errorFileCTR
    Enddo

    I know i have to check the syntax but at least that's the algorithm. Is there an exist test? Do you think my semi-code would work?

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    The only way I know of to test if a table is in existence is to attempt to open it with code and use error trapping / handling to test the Err.Number value.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Aug 2008
    Posts
    78
    Quote Originally Posted by StarTrekker
    The only way I know of to test if a table is in existence is to attempt to open it with code and use error trapping / handling to test the Err.Number value.

    I found a clean way to do it but this is super ugly code that I'm making pretty


    Code:
    Dim existsTable, errorFile, errorFileCTR, nextErrorFile, DCountName As String
    
    errorFile = "Scotch3M_ExportErrors"
    nextErrorFile = errorFile
    
    existsTable = DCount("*", "MSysObjects", "[Name]='Scotch3M_ImportErrors'")
    
    Do Until existsTable = 0
       
    '   DoCmd.DeleteObject acTable, errorFile
    
       errorFileCTR = errorFileCTR + 1
       nextErrorFile = errorFile & errorFileCTR
       DCountName = "[Name]=" & "'" & nextErrorFile & "'"
       existsTable = DCount("*", "MSysObjects", DCountName)
       MsgBox DCountName, existsTable
    Loop
    the code is gross but it works but i'll clean it up later on when i have some time...But at least it picks up every

    Scotch3M_ImportErrors
    Scotch3M_ImportErrors1
    Scotch3M_ImportErrors2
    Scotch3M_ImportErrors3

    and so on

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    hmmmmm

    try some DAO.

    Code:
        
    dim oTbl As dao.tabledef
        for each oTbl in currentdb.tabledefs
            debug.print oTbl.name
        next
    OK - debug.print doesn't exactly take you where you want to go, but once you have the name, DROP is only a line of code away.

    izy

    LATER - actually, you may be better off with deleteobject than with DROP

    LATER STILL - it also late-binds neatly (no DAO reference needed)
    Code:
        dim oTbl as object
        for each oTbl in currentdb.tabledefs
            debug.print oTbl.name
        next
    Last edited by izyrider; 10-07-08 at 13:13.
    currently using SS 2008R2

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Pon, that code is gonna spit it if it encounters a table with zero records in it.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I saw a function once based similar to your code for "TableExists" which you could call and pass the table name to test and see if that table existed. But I can't recall where it was. Try googling "IfExists" or "TableExists" but it was like your post.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Instead of trying to fix the resulting issue, why not try and nip it in the bud at the source:
    Quote Originally Posted by PonPending
    After running some code/macros, access is creating tables like this:
    Stop using Macros. Right click the offending Macro and chose Save As... Module.

    Voila, you have the code it is using.

    So, instead of Access deciding the table name, why don't you set it to a fixed value and add a little code to truncate the table before population.

    If you get stuck on this, post the VBA code back here and someone will take a look
    George
    Home | Blog

  12. #12
    Join Date
    Aug 2008
    Posts
    78
    Quote Originally Posted by georgev
    Instead of trying to fix the resulting issue, why not try and nip it in the bud at the source:

    Stop using Macros. Right click the offending Macro and chose Save As... Module.

    Voila, you have the code it is using.

    So, instead of Access deciding the table name, why don't you set it to a fixed value and add a little code to truncate the table before population.

    If you get stuck on this, post the VBA code back here and someone will take a look

    I dont run any macros - the errors are generated from a linked table. The source has fields longer than 255 and get truncated.. Warnings.. I can't intervene since it's a system task that's running.. not a macro that I've authored.

  13. #13
    Join Date
    Aug 2008
    Posts
    78
    StarTrekker: "Pon, that code is gonna spit it if it encounters a table with zero records in it"

    Correct, but if that table exists at all, it MUST have at least one record in it. The system is creating these tables


    IZY: you're code was great! He's your idea slimming down my buldozed approach. Much appreciated!


    Code:
        Dim oTbl As Object
        For Each oTbl In CurrentDb.TableDefs
          If Mid(oTbl.Name, 1, 21) = "Scotch3M_ImportErrors" Then
             DoCmd.DeleteObject acTable, oTbl.Name
          End If
        Next

Posting Permissions

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