Drop blank tables

[Ben_Entrew]

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

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

 

[pbaldy]

Instead of the test for nothing try a DCount using obj.Name for the table argument.

 

[Ben_Entrew]

Hi Paul,

thanks for your quick reply.

I tried this, however it couldn't find the obj with:

 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

 

[pbaldy]

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

DCount("*", obj.Name)

 

[Ben_Entrew]

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?

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

 

[MarkK]

I would use TableDefs, not AccessObjects, since with TableDefs you can check .RecordCount very easily . . .

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

 

[Ben_Entrew]

Thanks lagbolt,
that's exactly what I needed. Now it works.

Thanks again.

Regards,
Ben