Loop thru SQL tables

"How can I loop through all the tables in a SQL Server database, and run T-SQL on each table where the name matches a pattern, without using cursors?"

This question was recently posted at a SQL forum. Here is the solution. . .

SQL Server has a system stored procedure sp_tables that displays all the tables in the database, and has an optional parameter that allows filtering the results. For example, to list all tables that have the prefix 'tbl' you would use:

sp_tables 'tbl%'

To use these results in subsequent processing, we need to save the output to a table. After looking up the sp_tables procedure in BOL, we can create a table to hold the results, and then execute the procedure to populate the table:

CREATE TABLE #temp
(
TABLE_QUALIFIER sysname,
TABLE_OWNER sysname,
TABLE_NAME sysname,
TABLE_TYPE varchar(32),
REMARKS varchar(254)
)

INSERT INTO #temp
EXEC sp_tables 'tbl%'

Now we need a strategy for looping thru these records & processing each table one-by-one - - We can add a flag to the temp table to mark when each record is processed:

EXEC sp_executesql N'ALTER TABLE #temp ADD Processed bit NOT NULL DEFAULT (0)'

Now, we select the first table name, run our T-SQL on that table, and mark the flag as being processed. The WHILE loop continues as long as there are any records that have not been processed. At the beginning of our code we need to insert the line:

DECLARE @TABLE_NAME sysname

and then we can write our loop:

WHILE (SELECT COUNT(*) FROM #temp WHERE Processed = 0) > 0
BEGIN
SET @TABLE_NAME = (SELECT TOP 1 TABLE_NAME FROM #temp WHERE Processed = 0)
PRINT @TABLE_NAME
UPDATE #temp SET Processed = 1 WHERE TABLE_NAME = @TABLE_NAME
END

...which simply prints the name of each table, where the table name matched our filter.

To execute a real SQL statement against each table, we need to add another variable at the top of our code:

DECLARE @sql nvarchar(4000)

and then the loop is written like this:

WHILE (SELECT COUNT(*) FROM #temp WHERE Processed = 0) > 0
BEGIN
SET @TABLE_NAME = (SELECT TOP 1 TABLE_NAME FROM #temp WHERE Processed = 0)
-- replace next line with your SQL statement
SET @sql = 'SELECT COUNT(*) FROM ' + @TABLE_NAME
EXEC sp_executesql @sql
UPDATE #temp SET Processed = 1 WHERE TABLE_NAME = @TABLE_NAME
END

...which prints the number of rows in the table, for each table where the name matched our filter.

You can view the complete code here:

SQL_Loop_thru_tables

Tracking Access Button Clicks

I am working with an older Access application that has almost 500 command buttons, and our task is to determine which features are still being used & which ones can be retired. Hopefully we'll recover screen space, plus improve the maintenance by removing obsolete queries & reports.

Of course, asking the users which features they are using is one option, but with over 100 users that is not really practical.

So we created some code to track button clicks, and using the Screen object in Access makes that quite simple. Basically we created a module to save the click info to a table, and then used Word to mass-update the code modules to add a line to invoke the tracking code. More on that later.

The ClickTracker code:

First, we test if the user clicked a command button. This is necessary because you can have a click event on a list box, combo box or even the form itself. The test looks like this:

If Screen.ActiveControl.ControlType = acCommandButton Then
' ( code goes in here )
End If

Then we can get the relevant info:

The form name is
Screen.ActiveForm.Name

The button name is
Screen.ActiveControl.Name

...and the button caption is
Screen.ActiveControl.Caption

The complete module can be viewed here. ( see * and ** below )

Part 2 - adding that line of code in 500 places...

With that many command buttons, we needed a quick way to insert that code. Easy. Select the text of the entire code module & copy / paste into Word. Then you can do a Find & Replace All, using some of the special characters available in Word:

Find this: _Click()
Replace with: _Click()^p^tClickTracker

...which will insert a new line right at the beginning of every click event to invoke the ClickTracker code. Then you just select all the text from Word & paste it back into the code module & overwrite the old version.

----
* our table uses an Autonumber for the first field, that why we skip rs.Fields(0). The other fields can be text(64) because that is the max length of a form or control name in Access.

** our version includes calls to custom functions 1: GetUser() calls the Windows API to get the ID of the Windows user, and 2: DateNoTime() strips off the time portion from Now() so we are saving only the date, not the time. You don't need these for the rest of it to work, but you do need to modify the code to skip those last two fields.

Access subform VBA

Forms behave differently when they are used as subforms, and this can be a challenge when using VBA. Normally, when you load a form, Access adds it to the Forms collection. So if you open a form MyForm then

Forms.Count = 1

and you can refer to a control on that form using:

Forms!MyForm!MyControl

However, if that form contains a subform, MySubform, then

Forms.Count = 1

and this will fail:

Forms!MySubform!MySubformControl

So if you want to refer to a control on the subform, you need to use:

Forms!MyForm!TheSubformControl.Form!MySubformControl

Note that TheSubformControl is the name of the subform control on MyForm that contains the form MySubform, not the name of the embedded form. Nowhere do you see the actual name of the form that is being used as a subform.

Confusing? You bet. If you use the Expression Builder it can allow you build the incorrect version, so you need to make sure to drill down through the main form to the subform.

Let's say that you want to invoke code on the subform when an event occurs on the main form - for example, when you navigate to a different record on the main form, you want to set the value of a combo box on the subform & then run its AfterUpdate code. Due to the fact that the subform properties & methods are not exposed, in the Form_Current event on the main form you might use this:

Forms!MyForm!TheSubformControl.Form!MySubformCombo = DCount("*","MyTable")

Forms!MyForm!TheSubformControl.Form.MySubformCombo_AfterUpdate

But wait - that doesn't work (yet). Why? The answer is quite simple: Scope.

By default, code in standard modules are declared as Public, so they can be accessed from anywhere in the application. But code-behind in forms is declared as Private. So in the subform code, all you have to do is change this

Private Sub MySubformCombo_AfterUpdate()
'
End Sub

to this:

Public Sub MySubformCombo_AfterUpdate()
'
End Sub

...and it will work as expected.