Drop blank tables

Ben_Entrew

Registered User.
Local time
Today, 08:22
Joined
Dec 3, 2013
Messages
177
Hi all,

I wanna export all tables beginning with NEW_TRP . Blank Tables shouldn't be exportet, therefore I tried to delete them before.

Somehow my check it the tables are blank or not doesn't work out.
It still exports me all tables of the database.

Can someone give me a hint please?

Thanks in advance.
Regards,
Ben



Code:
Public Sub ExportAll()
    Dim obj As AccessObject, dbs As Object
    Set dbs = Application.CurrentData
    For Each obj In dbs.AllTables
        If obj Is Nothing Then
        DoCmd.DeleteObject 
        Else
        If Left(obj.Name, 7) = "NEW_TRP" Then
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, obj.Name, folderlocation & obj.Name & ".xls", -1
        End If
        End If
    Next obj
End Sub
 
Instead of the test for nothing try a DCount using obj.Name for the table argument.
 
Hi Paul,

thanks for your quick reply.

I tried this, however it couldn't find the obj with:
Code:
 If DCount("obj") = 0 Then
        DoCmd.DeleteObject
        Else
        If Left(obj.Name, 7) = "NEW_TRP" Then
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, obj.Name, folderlocation & obj.Name & ".xls", -1
        End If
        End If
Is there a way to connect both conditions? If a table name begins with NEW_TRP and this is not Bland Then
....


Regards,
Ben
 
You didn't use what I recommended, nor is the syntax complete.

DCount("*", obj.Name)
 
I used your advice.
Now it tells me that it can't delete the FORM TEST in the database, while it's open.

I forgot to mention that I got a form called TEST in the database.

Somehow it tries also to delete this form, although it's not blank.
Can I select only tables as an object or add the obj.name <> "TEST" in the If condition?

Code:
Public Sub ExportAll()
    Dim obj As AccessObject, dbs As Object
    Set dbs = Application.CurrentData
    For Each obj In dbs.AllTables
        If DCount("*", obj.Name) = 0 Then
        DoCmd.DeleteObject
        Else
        If Left(obj.Name, 7) = "NEW_TRP" Then
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, obj.Name, folderlocation & obj.Name & ".xls", -1
        End If
        End If
    Next obj
End Sub
 
I would use TableDefs, not AccessObjects, since with TableDefs you can check .RecordCount very easily . . .
Code:
Private Sub ExportTables()
    Dim tdf As DAO.TableDef
    For Each tdf In CurrentDb.TableDefs
        If Left(tdf.Name, 7) = "New_TWP" And tdf.RecordCount > 0 Then
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
                tdf.Name, folder & tdf.Name & ".xls", -1
        End If
    Next
End Sub
 
Thanks lagbolt,
that's exactly what I needed. Now it works.

Thanks again.

Regards,
Ben
 

Users who are viewing this thread

Back
Top Bottom