Ultra-simple PHP Thumbnail Web Gallery

I used this once when I wanted to share a bunch of photos that I took on a group holiday. Years later, this is still my favourite simple option. All you need is a for this to work is a folder called ‘thumbs’ and a folder called ‘images’ (guess what goes where) – just make sure the file names are identical in each. Then simply make your index.php for the root folder out of the below code and you are done!

<?php
if ($handle = opendir('thumbs')) {
      $list = array();
    while (false !== ($file = readdir($handle))) {
            array_push($list, $file);
            sort($list);
    }
     closedir($handle);

    foreach ($list as $filename) {
            if ($filename == '.' || $filename == '..') {
                  //do nothing
            } else {
                  echo '<a href="images/'.$filename.'"><img src="thumbs/'.$filename.'" border=0></a>';
            }
      }
}
?>

Record MS Access field changes to in change log table

I just wrote this little gem which is dynamic enough to withstand all the fields that I am yet to add to my form. It records changes to textboxes and comboboxes. It even takes the displayed content of the comboboxes rather than the bound data so that is end-user friendly.

Steps are:

1. Create the table (mine is in SQL then linked in Access hence the difference in name).

CREATE TABLE [dbo].[tbl_Members_ChangeLog](
[ChangeLog_ID] [int] IDENTITY(1,1) NOT NULL,
[Store_ID] [int] NOT NULL,
[ChangedBy] [varchar](50) NULL,
[DateTime] [smalldatetime] NULL,
[Field] [varchar](50) NULL,
[OldValue] [varchar](500) NULL,
[NewValue] [varchar](500) NULL,
CONSTRAINT [PK_Members_ChangeLog] PRIMARY KEY CLUSTERED
(
[ChangeLog_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tbl_Members_ChangeLog] ADD CONSTRAINT [DF_Members_ChangeLog_DateTime] DEFAULT (getdate()) FOR [DateTime]
GO

2. Added the fOSUserName() function to your VBA module to get the windows user from here: http://access.mvps.org/access/api/api0008.htm

3. Add this code to catch the changes. You can call it from your form’s BeforeUpdate Sub:

Sub UpdateChangeLog()
Dim sSql As String
Dim rs As Recordset
Dim OldVal As String, NewVal As String

Debug.Print “—-”
‘cycle through text and combo boxes
For Each ctl In Me.Controls

If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
If (ctl.Value <> ctl.OldValue) Or (Nz(ctl.OldValue, “”) = “” And ctl.Value <> “”) Then
If ctl.ControlType = acTextBox Then
OldVal = Nz(ctl.OldValue, “”)
NewVal = Nz(ctl.Value, “”)
ElseIf ctl.ControlType = acComboBox Then

‘get text for combobox lookup old value
If Nz(ctl.OldValue, “”) = “” Then
OldVal = “”
Else
Set rs = CurrentDb.OpenRecordset(ctl.RowSource, dbOpenSnapshot)
rs.MoveFirst
rs.FindFirst rs.Fields(0).Name & ” = ” & ctl.OldValue
OldVal = rs.Fields(1)
rs.Close
End If

‘get text for combobox lookup new value
Set rs = CurrentDb.OpenRecordset(ctl.RowSource, dbOpenSnapshot)
rs.MoveFirst
rs.FindFirst rs.Fields(0).Name & ” = ” & ctl.Value
NewVal = rs.Fields(1)
rs.Close

End If

Debug.Print ctl.Name
sSql = “INSERT INTO dbo_tbl_Members_ChangeLog (Store_ID, ChangedBy, Field, OldValue, NewValue) ” & _
“VALUES (” & Me.Store_ID & “, ‘” & fOSUserName() & “‘, ‘” & ctl.ControlSource & “‘, ‘” & OldVal & “‘, ‘” & NewVal & “‘);”

DoCmd.RunSQL sSql

End If
End If

Next ctl

Set rs = Nothing
End Sub

SSRS Query for Report Executions

I whipped up this query to put in a report to give me a list of the executions of our SQL Server Reporting Services reports and their run counts with the user and additional details being available on a drill down. It’s a simple query and very easy to adapt.

SELECT C.[Path], C.CreationDate, CU.UserName as CreatedBy, C.ModifiedDate, MU.Username as ModifiedBy,  E.Username as RunBy, E.TimeStart as DateTimeRun, DATEDIFF("ss", E.TimeStart, E.TimeEnd) as ExecutionTime, E.Parameters as RunParameters

FROM [Catalog] C 
 LEFT JOIN Users CU ON C.CreatedById = CU.UserID
 LEFT JOIN Users MU ON C.ModifiedById = MU.UserID
 LEFT JOIN ExecutionLog E ON C.ItemID = E.ReportID
WHERE C.[Type] = 2 -- reports only
ORDER BY C.[Path], RunBy, E.TimeStart

Get values from an SQL Stored Procedure into Excel (VBA macro)

I’m using the below function in an Excel macro to get the results of a simple stored procedure into an Excel spreadsheet. The function needs the connection string defined within it, but this can easily setup to be dynamic in the function parameters if needed. Also I’m only using one parameter, but I have no doubt that you create a multi-value function parameter to take several stored proc parameters.

In your macro, simply call the function and it will populate cells with your stored proc results beginning at the current active cell.

Function insertStoredProcValues(spName As String, strParameter As String)

    Dim Conn As ADODB.Connection
    Dim ADODBCmd As ADODB.Command
    Dim rs As ADODB.Recordset
    Dim i As Integer
    Dim sConnect As String

    '--DEFINE CONNECTION STRING HERE---------------------------------------------------------
    sConnect = "driver={sql server}; server=MYSERVERNAME; Database=MYDATABASENAME; UID=MYUSERNAME; PWD=MYPASSWORD;"
    '----------------------------------------------------------------------------------------

    'Establish connection
    Set Conn = New ADODB.Connection
    Conn.ConnectionString = sConnect
    Conn.Open

    'Open recordset
    Set ADODBCmd = New ADODB.Command
    ADODBCmd.ActiveConnection = Conn
    ADODBCmd.CommandText = spName
    ADODBCmd.CommandType = adCmdStoredProc
    ADODBCmd.Parameters.Refresh
    ADODBCmd.Parameters(1).Value = strParameter
    Set rs = ADODBCmd.Execute()

    'Loop through recordset and place values
    rs.MoveFirst
    Do While rs.EOF = False
        For i = 0 To rs.Fields.Count - 1
            ActiveCell.Value = rs.Fields(i).Value   'insert value into cell
            ActiveCell.Offset(0, 1).Activate        'move to next cell for next value
        Next i
        ActiveCell.Offset(1, -i).Activate           'move to next row for next record
    rs.MoveNext
    Loop

    'Clean up
    rs.Close
    Set rs = Nothing
End Function
 

Having trouble bridging your modem and your Astaro Security Gateway?

I know I was. I just couldn’t get the thing to connect when I change my ASG’s external interface from ‘Cable Modem (DHCP)’ to PPPoE.

The answer? Instead of changing the interface delete it first and just re-create it – worked a charm for me!

Also worth noting that deleting the interface took my NAT settings with it – I just reverted to the backup, took some screen shots and then recreated them.