Format VBA/TSQL code online

A simple way to format your C#, VB, HTML, XML, T-SQL or MSH code for publishing on web.

Update: one more from John Mishefske

Access PowerTools Add-In

Shamil Salakhetdinov has published  Access PowerTools Add-In, an open source project on Access COM add-in, which helps to learn how to make such add-in, create setup and distribute it. And even run unit test!

Build your own Object Browser

Sometimes people ask – how to get a list of methods or events of some ActiveX DLL or control, or just any COM library. VB6 (and perhaps earlier versions) was shipped with TLBINF32.DLL - TypeLib Information Object Library, which you can use in Access or any other VBA host. And MSDN article Visual Basic: Inspect COM Components Using the TypeLib Information Object Library helps to understand basics of this library usage, you can also download Help Files for Tlbinf32.dll.

ShowplanCapturer

For these who are lazy setting ShowPlan feature in registry and opening output file - Sascha Trowitzsch, Access MVP, have made a small tool – ShowplanCapturer.

TSQL: increase series number

Here a function to increase series number, I used it in inventory report to show currently available series. If you have series like ABC1001, and 5 items were sold - you can use this function to get next available series, e.g. ABC1006. Based on a GetNumbers() function from  Extracting numbers with SQL Server  article.

CREATE FUNCTION dbo.fSeriesAddQty(@DATA VARCHAR(8000), @intQty int)
RETURNS VARCHAR(8000)
AS
BEGIN  
    declare @Pos1 int, @Pos2 int
    declare @Result VARCHAR(8000)
    select @Pos1=0, @Pos2=0
    set @Pos1=PATINDEX('%[0-9.-]%', @DATA)
    set @Pos2=PATINDEX('%[-]%', @DATA)
    if @Pos2>@Pos1
        set @Pos1=@Pos2+1
    set @Pos2=PATINDEX('%[^0-9.-]%', SUBSTRING(@DATA, @Pos1, 8000) + 'X')
    if @Pos1>0 AND @Pos2 >0
        begin
        set @Result=SUBSTRING(@DATA, @Pos1, @Pos2-1)
        if isnumeric(@Result)=1
            set @Result=Left(@DATA,@pos1-1) + cast(cast(@Result as int) + @intQty as varchar(100)) + SUBSTRING(@DATA,@Pos2+@Pos1-1, 8000)
        else
            set @Result=@DATA
        end
    else
        set @Result=@DATA

    return @Result
END