AccessBlog.net

News, links, downloads, tips and tricks on Microsoft Access and related

About Me Search
Alex
Name:Alex Dybenko

Location:Moscow, Russia

Tuesday, February 23, 2010

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

Labels:

Friday, February 19, 2010

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!

Labels:

Saturday, February 13, 2010

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.

Labels: , ,

Sunday, February 07, 2010

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.

Labels:

Thursday, February 04, 2010

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

Labels: ,