Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2002
    Posts
    34

    Unanswered: Delete Temp Tables

    Hi All,

    I want to delete all my tables that start with tblTemp. Does anyone know how I would do this? I would like to do it in VBA.

    Thanks,
    SD

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Delete Temp Tables

    Originally posted by Schoon Dog
    Hi All,

    I want to delete all my tables that start with tblTemp. Does anyone know how I would do this? I would like to do it in VBA.

    Thanks,
    SD
    Here's my routine I use ... You can adapt it very easily:

    Code:
    Function RemovePreviousTemporaryFiles() As Integer
        On Error GoTo Err_RPTF
        
    '    Dim FormsConnect As ADODB.Connection
        Dim TrgRecSet As ADODB.Recordset
        
    '    Set FormsConnect = New ADODB.Connection
        Set TrgRecSet = New ADODB.Recordset
        
    '    FormsConnect.CursorLocation = adUseClient
    '    FormsConnect.Open "DSN=Billing Forms;"
        
        TrgRecSet.CursorType = adOpenForwardOnly
        TrgRecSet.LockType = adLockOptimistic
        TrgRecSet.CursorLocation = adUseClient
        
    'SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND
    '(Left$([Name],4) <> "Msys") AND (Right$([Name],8) <> "Template")  AND (MSysObjects.Type)=1 ORDER BY MSysObjects.Name;
        SQLString = "SELECT MSysObjects.Name FROM MsysObjects"
        SQLString = SQLString & " WHERE ((Left$([Name],1)<>'~') AND (Left$([Name],4) <> 'Msys') AND (Right$([Name],8) <> 'Template') AND (MSysObjects.Type)=1) ORDER BY MSysObjects.Name;"
        TrgRecSet.Open SQLString, CurrentProject.Connection
        If TrgRecSet.BOF = False Then
            TrgRecSet.MoveFirst
            While TrgRecSet.EOF = False
                If Right(TrgRecSet.Fields(0).Value & "", Len(CurrentUser())) = CurrentUser() Then
                    DoCmd.SetWarnings False
                    DoCmd.DeleteObject acTable, TrgRecSet.Fields(0).Value
                End If
                TrgRecSet.MoveNext
            Wend
        End If
        TrgRecSet.Close
        
    '    FormsConnect.Close
    '    Set FormsConnect = Nothing
        Set TrgRecSet = Nothing
        
        RemovePreviousTemporaryFiles = 1
    Exit_RPTF:
        Exit Function
        
    Err_RPTF:
        
        MsgBox Err.Number & ": " & Err.Description
        RemovePreviousTemporaryFiles = 0
        Resume Exit_RPTF
    End Function

Posting Permissions

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