Check if table is empty - within a macro?

[ppataki]

Dear All,

I have a macro that contains a series of actions, DeleteObject, TransferSpreadSheet, etc
Before one of the actions is run I would need to check if one of my tables is empty or not (if empty run the action if not do not run the action)
Can I do this within the macro, or only with the help of a VBA code?

Many thanks in advance

 

[namliam]

If you want to do anything that looks remotely like serious programming, use VBA.

 

[ppataki]

Hello,

I have started to compile some code, here it is:

Sub plant_comments()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sqlstring As String
Set dbs = CurrentDb
'plant_comments contains the list of the plant comments tables
Set rst = dbs.OpenRecordset("plant_comments", dbOpenDynaset)
' now I need to check if each table - table names specified in rst - is empty or not
Rem sqlstring = "SELECT Count([plant comments 1110].Dateofreport) AS CountOfDateofreport FROM [plant comments 1110];"
sqlstring = "SELECT Count(rst![tables].Dateofreport) AS CountOfDateofreport FROM rst![tables];"
End Sub

Now the next step would be I guess to check if each table - the names of which are defined in the recordset - is empty or not, but unfortunately I could not figure it out how

Could you please give me some hints?

Thank you very much!

 

[namliam]

There are a couple of ways to do this....

Dcount can do a count of the records in a table.
Rs.Recordcount will do it if you have a recordset open of the table

set rs = ... Select Coun(*) as ACount from your table

rs!ACount contains the count

Why do you need to know if there is data or not?

 

[ppataki]

Hello,

The thing is that these tables are imported from Excel files, and it may happen that one or more of them is/are empty
If an empty one gets imported, all the field properties are gone
So I thought that a good solution would be to check if the table is empty after importing and if so, copy the field properties from another table that is not empty

Or maybe instead of checking just set the properties to all of them immediately after importing?

Thank you!

 

[namliam]

Eh? Field properties?? You setup your table once dont you?? Not each time drop the table and create a new one ???

 

[ppataki]

Now it works in a way that the Excel file is imported (DeleteObject, TransferSpreadsheet), replacing the current table and then the content gets appended to another table after several modifications by other queries

Is there a better way to do this?
(and then comes the question: how to deal with the empty Excel fiels?)

Thank you very much!

 

[namliam]

Not deleting the object but rather delete the contents using a delete query.
The appending the data into the table instead of creating a new one.

This will stop you from losing your properities.

 

[ppataki]

Woow, that solution is a bit simpler than compiling a code... )

Many thanks that would definitely solve my problem!!

 

[namliam]

is a bit simpler

Just a bit?? This is why I allways say... I love (to) KISS!

 

[Datagopher]

Another Access developer successfully led away from the pitfalls of macros and led to the endless possibilities of VBA....Good work, All

 

[dkinley]

Hehehe....

 

[namliam]

Another Access developer successfully

On this forum, or another or ??

Link?