Export Query Result to an Existing Excel Sheet in MS Access

The below function will export your all data from MS Access Query result to an existing Excel sheet.

Public Function SendTQ2XLWbSheet2(strTQName As String, strSheetName As String, strFilePath As String)
 Dim db As DAO.Database
 Set db = CurrentDb()
    Dim rst As DAO.Recordset
    Dim ApXL As Object
    Dim xlWBk As Object
    Dim xlWSh As Object
    Dim fld As DAO.Field
    Dim strPath As String
    Const xlCenter As Long = -4108
    Const xlBottom As Long = -4107
    On Error GoTo err_handler
    strPath = strFilePath
     Set rst = CurrentDb.OpenRecordset(strTQName)
    Set ApXL = CreateObject("Excel.Application")
    Set xlWBk = ApXL.Workbooks.Open(strPath)
    ApXL.Visible = True
     Set xlWSh = xlWBk.Worksheets(strSheetName)
     xlWSh.Activate

 'formatting Excel tab
 '======A1 row formatting======='
    xlWSh.Range("A1").Value = "Heading_Name" 
    xlWSh.Range("A1").Interior.Color = RGB(255, 228, 196)
    xlWSh.Range("A1").Columns.Font.Bold = True
    xlWSh.Range("A1").Font.Size = 14
    xlWSh.Range("A1").HorizontalAlignment = xlCenter
    '====Table Header moving and fomatting========'
    xlWSh.Range("A2:S2").Select
    xlWSh.Range("A2:S2").Interior.Color = RGB(169, 169, 169)
    For Each fld In rst.Fields
        ApXL.ActiveCell = fld.Name
        ApXL.ActiveCell.Offset(0, 1).Select
    Next
    rst.MoveFirst
    xlWSh.Range("A3").CopyFromRecordset rst
' =====Data will pasted from A3 row ======='
     rst.Close
     Set rst = Nothing
     xlWBk.Close True
    Set xlWBk = Nothing
    ApXL.Quit
    Set ApXL = Nothing
Exit_SendTQ2XLWbSheet4:
    Exit Function
err_handler:
    DoCmd.SetWarnings True
    MsgBox Err.Description, vbExclamation, Err.Number
    Resume Exit_SendTQ2XLWbSheet4
   End Function

Execution Process

Private Sub Command5_Click()
Dim db As DAO.Database
Set db = CurrentDb()
Dim path As String
path = CurrentProject.path
Dim fName As String
fName = "23 MHS_Q1-Q2-Q3-Q4 Status_12Jun17-QuarterlyReport.xlsx" (excel file name)
Dim p As String
p = path & "\" & fName
If SendTQ2XLWbSheet2("Query_Name", "Tab_Name(in Excel)", p ) = True Then
End If
MsgBox "Excel Report Created...!!!"
End Sub

Drop Existing table in MS Access :

I have posted an another option to drop an existing table in MS Access. Through VBA function we can drop an existing table Drop Existing table in MS Access (VBA). Here an alternate way we can do the same operation through a small code in VBA.

Dim Tbl As TableDef      
Dim TABLEDEFS
    For Each Tbl In db.TABLEDEFS
    If Tbl.Name = "DUP" Then
    db.Execute "DROP TABLE [DUP]"
    End If
Next Tbl

How to use CDC (Change Data Capture) in SQL Server

Step 1 -

Check for all Databases that are already Enabled with CDC tracking.

USE master 
GO 
SELECT [name], database_id, is_cdc_enabled  
FROM sys.databases       
GO  

Step 2 -

On above example we can is_cdc_enables all are 0. it means still we are not using.

USE Your_DB
GO 
EXEC sys.sp_cdc_enable_db 
GO 

Database MSO is now 1. Means CDC is enabled for MSO DB.

Step 3 -

Let me check all tables where CDC mode are enabled.

USE MSO 
GO 
SELECT [name], is_tracked_by_cdc  
FROM sys.tables 
GO  

Step 4 -

Enable CDC on MSO Database for Items table

USE MSO 
GO 
EXEC sys.sp_cdc_enable_table 
@source_schema = N'dbo', 
@source_name   = N'items', 
@role_name     = NULL 
GO

Once you will execute above query it will create Two jobs in SQL  Server Agent

Step - 5 

Go to Your Database =>Table=>System Table. Some CDC table you will get.

Step 6 - Update Your table

update Items set ITEMID=451350 where ITEMID=12345

USE MSO 
GO 
SELECT * 
FROM [cdc].[dbo_items_CT]
GO  --this table contains in System Table in Your Particular DB

All changes are recorded into a tracker CDC table.

SQL SERVER – FIX : ERROR : Cannot find template file for new query (C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql\ SQLFile.sql)

To fixed this error follow the below steps.
Step 1- Open a New Query (Right click on Database)
Step 2- Save it under the showing above location till SQL Folder.
Step 3- Rename it as SQLFile.SQL
Notes:
There might be a chance to get a permission from administrator. 

In this case save this file it into an another location and paste again on the same path.
Hope : Your error will be fixed.

Drop Existing Table in SQL Server:

IF OBJECT_ID('dbo.Table_Name', 'U') IS NOT NULL
DROP TABLE Table_Name

Track Your Execution Query through Trigger

I am creating a Tracker table where I can store my DML operation execution Query for INSERT, UPDATE, and DELETE operation.

Create table Query_Tracker
(
ID int unique identity(1,1),
Host varchar(15),
[Date] datetime,
Context varchar(max)
)

Creating trigger with name of Query_tracker_Trig

Create trigger [dbo].[Query_tracker_Trig]

ON [dbo].[Items_Copy]
after UPDATE,INSERT,DELETE
AS
BEGIN
DECLARE @sql nvarchar(max)
SET @sql = 'DBCC INPUTBUFFER(' + CAST(@@SPID AS nvarchar(100)) + ')'
CREATE TABLE #SQL
(
    EventType varchar(100),
    Parameters int,
    EventInfo nvarchar(max)
)
INSERT INTO #SQL
EXEC sp_executesql @sql
SELECT @sql = EventInfo FROM #SQL
INSERT INTO Query_Tracker(Host,[Date],Context)VALUES(Host_name(),getDate(),@sql)
DROP TABLE #SQL
END

select * from Query_Tracker