Tabledefs().Fields().Delete

[simon1974]

I am going to use Tabledefs().Fields().Delete in VBA code to delete all columns in certain tables that contain all null values (I must do this at runtime as tables are created). What I need is to get a list from Access (query, command, property????) of the fields that sum to null so that I can put them into an array and use a for... next loop to cycle through the above line of code to delete them. Can anyone tell me what creative way I can use to obtain the field names for all fields in a table that sum to null? Thanks in advance.

 

[allanon]

I thought your question sounded interesting so here is what I did:

I wrote a little bit of code: (big suprise)

Private Sub Command1_Click()
Dim rs As Recordset
Dim varItem As Variant
Dim i As Integer
Dim strSQL As String

For Each varItem In CurrentDb.TableDefs
Debug.Print " compare: ", varItem.Name, InStr(varItem.Name, "MSys&quot
If (InStr(varItem.Name, "MSys&quot = 0) And (InStr(varItem.Name, "Switch&quot = 0) Then
For i = 0 To varItem.Fields.Count - 1
Debug.Print varItem.Name, varItem.Fields(i).Name, varItem.Fields(i).Type
If varItem.Fields(i).Type = 4 Then
strSQL = "SELECT SUM(" & varItem.Fields(i).Name & &quot AS TheSum "
strSQL = strSQL & "FROM " & varItem.Name
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
Debug.Print "After SQL Sum: ", varItem.Name, varItem.Fields(i).Name, rs!TheSum
End If
Next i
End If
Next varItem

End Sub

The instr checks are to ignore system tables and the switchboard tables.

I just put debug.print in mine BUT as you can see with a little effort you can do pretty much whatever you want with the results. Here were my results on my playing around database:

compare: dbo_cma_equipment 0
dbo_cma_equipment claim_no 10
dbo_cma_equipment equipment_type 10
dbo_cma_equipment date_issued 8
dbo_cma_equipment returned 1
dbo_cma_equipment date_returned 8
compare: DWALL 0
DWALL key 4
After SQL Sum: DWALL key 6
DWALL RES_BEGIN 8
DWALL RES_END 8
DWALL LATEST_STD 10
compare: InvoiceInformation 0
InvoiceInformation Key 4
After SQL Sum: InvoiceInformation Key 36
InvoiceInformation Invoice_ID 4
After SQL Sum: InvoiceInformation Invoice_ID 17
InvoiceInformation Product_ID 4
After SQL Sum: InvoiceInformation Product_ID 14
InvoiceInformation Quantity 4
After SQL Sum: InvoiceInformation Quantity 50
InvoiceInformation Price IncVAT 5
InvoiceInformation Price ExVAT 5
compare: Invoices 0
Invoices Key 4
After SQL Sum: Invoices Key 18
Invoices Invoice_ID 4
After SQL Sum: Invoices Invoice_ID 6
Invoices Total Price IncVAT 5
Invoices Total Price ExVAT 5
compare: MSysACEs 1
compare: MSysModules 1
compare: MSysModules2 1
compare: MSysObjects 1
compare: MSysQueries 1
compare: MSysRelationships 1
compare: Product_ServiceS 0
Product_ServiceS Key 4
After SQL Sum: Product_ServiceS Key 6
Product_ServiceS Product_ID 4
After SQL Sum: Product_ServiceS Product_ID 6
Product_ServiceS Unit Price IncVAT 5
Product_ServiceS Unit Price ExVAT 5
compare: Switchboard Items 0
compare: Table1 0
Table1 ID 4
After SQL Sum: Table1 ID 15
Table1 Key 10
Table1 field2 10
Table1 datefield 8
Table1 money 10
Table1 money2 5
Table1 field01 4
After SQL Sum: Table1 field01 22
Table1 field02 4
After SQL Sum: Table1 field02 35
Table1 field03 4
After SQL Sum: Table1 field03 27
compare: Table2 0
Table2 key 4
After SQL Sum: Table2 key 45
Table2 rent_id 4
After SQL Sum: Table2 rent_id 20
Table2 last_paid 8
Table2 amount_paid 5
Table2 Temp 10
compare: Table3 0
Table3 key 4
After SQL Sum: Table3 key 28
Table3 vehicle_id 4
After SQL Sum: Table3 vehicle_id 12
Table3 Normal_labour_hours 4
After SQL Sum: Table3 Normal_labour_hours 21
Table3 Overtime_labour_hours 4
After SQL Sum: Table3 Overtime_labour_hours 13
compare: Table4 0
Table4 key 4
After SQL Sum: Table4 key 3
Table4 vehicle_id 4
After SQL Sum: Table4 vehicle_id 3
Table4 normal_hourly_rate 4
After SQL Sum: Table4 normal_hourly_rate 30
Table4 overtime_hourly_rate 4
After SQL Sum: Table4 overtime_hourly_rate 45
compare: Table5 0
Table5 ID 4
After SQL Sum: Table5 ID 15
Table5 Key 10
Table5 field2 10
Table5 datefield 8
Table5 money 10
Table5 money2 5
Table5 field01 4
After SQL Sum: Table5 field01 22
Table5 field02 4
After SQL Sum: Table5 field02 35
Table5 field03 4
After SQL Sum: Table5 field03 77
compare: Table6 0
Table6 key 4
After SQL Sum: Table6 key 45
Table6 date 8
Table6 center 10
Table6 copies 4
After SQL Sum: Table6 copies 13752
Table6 Nulled 4
After SQL Sum: Table6 Nulled Null
compare: temp 0
temp RES_BEGIN 8
temp RES_END 8
compare: WORKING 0
WORKING key 4
After SQL Sum: WORKING key 10
WORKING RES_BEGIN 8
WORKING RES_END 8
WORKING LATEST_STD 10

If you look careful you will see a bunch of lines that start with: "After SQL Sum". These are the lines you would be interested. If you look you will see some where the line ends in "Null"....that would be paydirt for you.

Have fun with it.

 

[simon1974]

Thanks, allanon. I will play around with this later today and see if I can get it to work. I knew there must be some creative way of lining up this information in a way I can use it.

Regards.

 

[simon1974]

Allanon,

I tried the code, it seems to run up until the following line:

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

It keeps telling me that it is using a reserved word and then stops the code. I'm using Access97. Any help would be appreciated.

Simon

 

[allanon]

Check and see what references you are using.

With a code window open select Tools/References.

The only ones that I have selected are:

Visual Basic For Applications
Microsoft Access 8.0 Object Library
Microsoft DAO 3.5 Object Library

If that is now the problem then if you have made any changes to the code post the code and I will look at it. Failing that you could always send me the db and I will look at it for you.

 

[simon1974]

alanon,

It is working now - thanks so much for your help. I guess i will try first to do a maketable with the output. One problem that I have is that it is not grabbing all of my tables, only some. Let me know if you can think of why it would only be grabbing certain tables. (Can print.debug only hold a certain number of output lines? Maybe that is why my output is limited) Thanks.

 

[allanon]

If you want to make sure you get everything then set up a work table and write all of your table names to the work table. I am not sure how much the debug window is capable of displaying.