Extract Data from MS Access 2010 to Excel using ADODB Connection

Option Explicit
Dim conn As ADODB.Connection, i As Integer
Dim rst As ADODB.Recordset, querystring As String
Sub accesData()
Set conn = New ADODB.Connection
Set rst = New ADODB.Recordset
i = 1
querystring = "Select [Order Id], sum(([Unit Price]*Quantity))  as Sales  from [Order Details] group  by [Order Id]"
conn.ConnectionString = "Provider=Microsoft.Access.OLEDB.10.0;Persist Security Info=False;Data Source=somu"
conn.Open
conn.CursorLocation = adUseClient
rst.Open querystring, conn, adOpenStatic
    Do While Not rst.EOF
        ThisWorkbook.Sheets(1).Range("A" & (i + 1)) = rst.Fields(0).Value
        ThisWorkbook.Sheets(1).Range("B" & (i + 1)) = rst.Fields(1).Value
        rst.MoveNext
        i = i + 1
    Loop
rst.Close
conn.Close
Set rst = Nothing
Set conn = Nothing
End Sub


Download Excel

Sample Database

Create and Populate ListBox from MSSQL Database

Set conn = New ADODB.Connection
    Set rst = New ADODB.Recordset
   
    conn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Data Source=.;Initial Catalog=CourseMasterDB;"
    conn.Open
    querystring = "Select GeoName from MST_Geo"
    i = 0
    k = 3
   
           
            rst.Open querystring, conn, adOpenStatic
            ReDim geoArray(rst.RecordCount)
            Do While Not rst.EOF
            geoArray(i) = rst.Fields(0).Value
            rst.MoveNext
            i = i + 1
            Loop
   
            rst.Close
            conn.Close
            Set rst = Nothing
            Set conn = Nothing
           For k = 3 To ThisWorkbook.Sheets.Count
                            Set lookuprng = ThisWorkbook.Sheets(k).Columns("J:J").Find("Select GEO", LookIn:=xlValues, lookat:=xlWhole)
                            If (Not lookuprng Is Nothing) Then
                             ThisWorkbook.Sheets(k).Range(lookuprng.Address).ClearContents
                             ThisWorkbook.Sheets(k).Range(lookuprng.Offset(0, 1).Address).Validation.Delete
                            End If
                            rowcount = ThisWorkbook.Sheets(k).Range("J" & Rows.Count).End(xlUp).Row
                            ThisWorkbook.Sheets(k).Range("J" & (rowcount + 15)) = "Select GEO"
                            ThisWorkbook.Sheets(k).Range("J" & (rowcount + 15)).FontSize = 15
                            ThisWorkbook.Sheets(k).Range("J" & (rowcount + 15)).Interior.ColorIndex = 24
                         With ThisWorkbook.Sheets(k).Range("K" & (rowcount + 15)).Validation
                             .Delete
                             .Add Type:=xlValidateList, Formula1:=Join(geoArray, ",")
                             .InCellDropdown = True
                             .InputTitle = ""
                             .ErrorTitle = ""
                             .InputMessage = ""
                             .ErrorMessage = ""
                             .ShowInput = True
                             .ShowError = True
                        End With

Add Form Control CheckBox in VBA

objrng.Offset(k, 0) = rst.Fields(0).Value
                'add check box
                ThisWorkbook.Sheets(shtcnt).CheckBoxes.Add(Left:=objrng.Offset(k, 1).Left, Top:=objrng.Offset(k, 1).Top, Width:=objrng.Offset(k, 1).Width, Height:=objrng.Offset(k, 1).Height).Select
                ThisWorkbook.Sheets(shtcnt).Range(objrng.Offset(k, 1).Address).NumberFormat = ";;;"
                With Selection
               
                    .Caption = ""
                    .Name = ""
               
                    .LinkedCell = objrng.Offset(k, 1).Address
                End With

Using ShellExecute to open an .exe File

Public Declare Function ShellExecute _
    Lib "shell32.dll" _
        Alias "ShellExecuteA" ( _
            ByVal Hwnd As Long, _
            ByVal lpOperation As String, _
            ByVal lpFile As String, _
            ByVal lpParameters As String, _
            ByVal lpDirectory As String, _
            ByVal nShowCmd As Long) _
As Long
Sub my_Procedure()
pathname = "C:\Program Files (x86)\TechSmith\Camtasia Studio 8\CamRecorder.exe"


'ThisWorkbook.Windows(1).WindowState = xlMinimized
procId = ShellExecute(0, "Open", pathname, vbNullString, "C:\", SW_SHOWNORMAL)
Application.Wait (Now + TimeValue("00:00:03"))
'AppActivate procId
Application.SendKeys ("{F9}"), True


end Sub

Select Query with Case

Select t3.LocationName, t1.CourseSDate,t1.CourseEDate,
CASE WHEN GI.CorporateId = 0 THEN GI.CompanyName ELSE CO.CORPORATENAME END AS ComapyName , ' ' as Venue from TRAN_SCINTERESTEDCOURSES t1
INNER JOIN TRAN_SCGENERALINFO GI ON T1.StudnetCardId = GI.StudnetCardId
 left JOIN MST_LOCATION t3 on t3.LocationId=t1.LocationId
 LEFT JOIN MST_CORPORATEDETAIL CO ON CO.CORPORATEID = GI.CorporateId where T1.StudnetCardId=4127

Populating ComboBox from SQl ResultSet

querystring = "Select AccomodationName from MST_ONSITEACCOMMODATION where CityName=" & Chr(39) & cityname & Chr(39)
                    conn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=.;Initial Catalog=Test;"
                    conn.Open
                    rst.Open querystring, conn, adOpenStatic
                    With ComboBox7
                                .Clear
                                    Do While Not rst.EOF
                                       .AddItem rst![AccomodationName]
                                                   
                                        rst.MoveNext
                                    Loop
                    End With
                                       
                    rst.Close
                    conn.Close
                    querystring = vbNullString
                    Set rst = Nothing
                    Set conn = Nothing

Extract Data Using Select Query in VBA

Option Explicit

Dim conn As New ADODB.Connection, querystring As String, blockname As String, hadoopblockrowcount As Long
Dim rst As New ADODB.Recordset

Sub extractData()
blockname = Application.InputBox("Please Mention Block Name to be Created")
querystring = "Select t1.Cid,t1.CName,t1.PSName,t1.StDuration,t2.CourseExamId from MST_COURSE t1"
querystring = querystring & " join LNK_COURSE_EXAM t2 on t1.CId=t2.courseid"
querystring = querystring & " where t1.CId in(Select distinct CourseId from Lnk_COURSE_VENDOR where VendorId=32)and PSName is not null"
querystring = querystring & " Union Select t1.Cid, t1.CName,t1.PSName,t1.StDuration,null from MST_COURSE t1"
querystring = querystring & " where t1.CId in(Select distinct CourseId from Lnk_COURSE_VENDOR where VendorId=32)and PSName is not null;"

hadoopblockrowcount = 3
Application.ScreenUpdating = False
    conn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=.;Initial Catalog=CourseMasterDB;"
    conn.Open
    rst.Open querystring, conn, adOpenStatic
    Do While Not rst.EOF
    hadoopblockrowcount = hadoopblockrowcount + 1
    ThisWorkbook.Sheets(1).Range("A" & (hadoopblockrowcount)) = rst.Fields(0).Value
    ThisWorkbook.Sheets(1).Range("B" & (hadoopblockrowcount)) = rst.Fields(1).Value
    ThisWorkbook.Sheets(1).Range("C" & (hadoopblockrowcount)) = rst.Fields(2).Value
                If (rst.Fields(4).Value <> vbNullString) Then
                        ThisWorkbook.Sheets(1).Range("E" & (hadoopblockrowcount)) = "Y"
                Else
                        ThisWorkbook.Sheets(1).Range("E" & (hadoopblockrowcount)) = "N"
                End If
    ThisWorkbook.Sheets(1).Range("G" & (hadoopblockrowcount)) = rst.Fields(3).Value
        rst.MoveNext
    Loop
   
    rst.Close
    conn.Close
    Set rst = Nothing
    Set conn = Nothing
   
End Sub

Trim Entire Column in VBA

Sub TrimCol()
    Dim r As Range
    Set r = Intersect(Range("A1").EntireColumn, ActiveSheet.UsedRange)
    r.Value = Evaluate("IF(ROW(" & r.Address & "),IF(" & r.Address & "<>"""",TRIM(" & r.Address & "),""""))")
End Sub

Copy Range to Array

Dim myArray()
Sub copyRange2Array()
myArray = ThisWorkbook.Sheets(1).Range("A1:A4").Value
For i = 1 To UBound(myArray)
    MsgBox myArray(i, 1)
Next
End Sub

Finding Next Blank Row no in VBA

Sub test()
Dim myrng As Range
Set myrng = Range(ThisWorkbook.Sheets(1).Range("A1").Offset(0, 0), ThisWorkbook.Sheets(1).Range("A1").Offset(0, 0).End(xlDown))
MsgBox myrng.Rows.Count + 1
End Sub

VBA code for autofilter on the basis of Color

keywordsmasterfile.Sheets(1).Range("A2:B2").AutoFilter field:=1, Criteria1:=RGB(204, 153, 255), Operator:=xlFilterCellColor

How to enable Power Pivot and Power View Report in Excel 2013

Enable Power Pivot and Power View Report in Excel 2013

Power BI

Power BI

VBA code for PDF converter

Convert PDF2Excel

Window Schedule task

Yhttps://www.youtube.com/watch?v=Va-GdAjwOcY

Types of Cursor in MSSQL

SQL Server Different Types of Cursors

           

A Cursor allow us to retrieve data from a result set in singleton fashion means row by row. Cursor are required when we need to update records in a database table one row at a time. I have already explained the basic of cursor.

A Cursor impacts the performance of the SQL Server since it uses the SQL Server instances' memory, reduce concurrency, decrease network bandwidth and lock resources. Hence it is mandatory to understand the cursor types and its functions so that you can use suitable cursor according to your needs.
You should avoid the use of cursor. Basically you should use cursor alternatives like as WHILE loop, sub queries, Temporary tables and Table variables. We should use cursor in that case when there is no option except cursor.

Windows Installer Setup With Custom Actions

Custom Action Report

Cam Recording code in VBA

Download Link

Install Window Service Programmitcally

You can do it all in one executable. To pass installation parameters, you will need to derive classes System.ServiceProcess.ServiceProcessInstaller and System.ServiceProcess.ServiceInstaller. You only need to implement constructors of your classes.

Installation and uninstallation will be done by System.Configuration.Install.AssemblyInstaller. This class should use the assembly where the classes based on ServiceProcessInstaller and ServiceInstaller are implemented; the implementation will be found in your assembly automatically (which would create a hard-to-detect failure if you have a bug in the implementation). Not too bad though.

Finally, start/stop, etc. are handled by your service code which you create deriving the class System.ServiceProcess.ServiceBase. Triggering this actions is done by Service Controller. To do it programmatically you should use the class System.ServiceProcess.ServiceController.

You need to have a good reading of MSDN on the topics around the classes I listed above, which is well illustrated by code samples. This information is easy to find, a bit harder to understand the work flow and what happens in which process. (A Windows Service process is always a separate one; it takes another process to run installation and Service Controller part.) You can use all this like a cookbook, but I suggest you thoroughly understand it; in this case you won't have any problems.

You can even try to implement it all in one application as I did, but you still need to run it in at least two different processes: one would work in the Windows Service mode, another is must be interactive mode, which can be anything else, such as UI, Console or just invisible batch-mode application.

Important! You code can test System.Environment.UserInteractive during run time to calculate is currently running code is run as Windows Service or not.

Add Installer for Window Service

In the service project do the following:

1. In the solution explorer double click your services .cs file. It should bring up a screen that is all gray and talks about dragging stuff from the toolbox.
2. Then right click on the gray area and select add installer. This will add an installer project file to your project.
3. Then you will have 2 components on the design view of the ProjectInstaller.cs (serviceProcessInstaller1 and serviceInstaller1). You should then setup the properties as you need such as service name and user that it should run as.