Delete rows from a sheet with an equal number of Intervals in Excel Macros

Sub deleteeveryotherrow()
For x = 3 To 100 Step 1
 Rows(x & ":" & x).Select
    Selection.Delete Shift:=xlUp
 Rows(x & ":" & x).Select
    Selection.Delete Shift:=xlUp
    x = x + 1
    Next x
End Sub

What number comes inside the Circle:

Rename a Table Under a MS Access File

Edit Table Name in MS Access (.accdb or .mdb format) through a click button.

Dim tdf As TableDef
    Set db = DBEngine.Workspaces(0).OpenDatabase("InputPath")
    Set tdf = db.TABLEDEFS("TableName")
    tdf.Name = "TableName" + "_SomeThing_NEW"

Split Rows into Multiples Rows If they have more than One Counts.

I have a Input table with a Name and Counts Columns.
I want to Split my Name with multiple times based on Counts column values.
Please , Check an above Screen Short that i have pasted.

create table #SPLITROW 
(Name varchar(10)
,Counts int 
)
INSERT INTO #SPLITROW
VALUES ('AA',5)
INSERT INTO #SPLITROW
VALUES ('BB',4)
INSERT INTO #SPLITROW
VALUES ('CC',3)
INSERT INTO #SPLITROW
VALUES ('DD',2)
INSERT INTO #SPLITROW
VALUES ('EE',1)

declare @Rc as int
declare @inital as int=1
select @rc=max(Counts) from #SPLITROW
declare @rowTab as table
(Countss int)
while (@inital<=@Rc)
begin
insert into @rowTab values(@inital)
set @inital=@inital+1
end
SELECT Name,Counts
FROM #SPLITROW  sr
JOIN (select Countss as RN from @rowTab) AS  oft
ON oft.RN <= Counts
order by Name

Alternate Option
SELECT Name , Counts
FROM @SPLITROW  sr
JOIN (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN
      FROM sys.columns) AS  oft
ON oft.RN <= Counts

Output Table

Color Only filled cells in Excel Macros

This post will help you to fill a color in a cell if it has some values. 

Sub NonBlankCells()
    On Error Resume Next
    Union(Cells.SpecialCells(xlCellTypeFormulas, 23), Cells.SpecialCells(xlCellTypeConstants, 23)).Select
    If Err.Number <> 0 Then
        Cells.SpecialCells(xlCellTypeFormulas, 23).Select
           Cells.SpecialCells(xlCellTypeConstants, 23).Interior.Color = vbRed
    Else
        Exit Sub
    End If
    On Error GoTo 0
End Sub

The Output would be :

Send Mail after Executing a Job:

Before Executing a job and sending mail to an User we need to configure our mail services in SQL Server:
Follow the steps below to configure a mail services in SSMS2014.
Step : 1
Open SSMS
Step : 2
Go to Management -> Database Mail->Configure Database Mail

Step : 3

Step : 4

Step : 5
Choose any Profile Name

Step : 6

Step 7 :
Click OK->OK
Step : 8
Go to SQL Server Agent->Operators->Right Click->New Operator

Operator Created with your given Name.
Now we can go for testing steps,Lets have a look
Step : 9
Go to Management->Database Mail->Right Click->

Step : 10
Select Profile Name

Step :11

Create a new job with following steps

Go to SQL Server Agent->Jobs->New Job->General

Keep some name->Go To Steps tab ->Steps Name->Select Database->Command 

Put below query to Command box 

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'Operator Name',

@recipients = 'mail ID',

@query = 'SELECT COUNT(*) as No_Counts
FROM DB.Schema_Name.Table_Name',

@subject = 'No of Employees
Working';

Execute the Job and see the mail. I hope you will get.

Create an Index and dropping if it Exist

CREATE NONCLUSTERED INDEX  Index_Name
ON dbo.TableName(Column_Name)
WITH (DROP_EXISTING = ON);

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

Bulk Insert from multiple text file into a SQL Server Table in Dynamic way:

I have five different text files in my Local Drive(D). Lets try to Insert this all data from text file to below created structure table in SQL Server.

Creating table structure

create table Foo
(
ID varchar(10),
Name varchar(30),
Mobile varchar(10)
)

Creating a Store Procedure to insert all data from different text file to a SQL table

Create procedure [dbo].[Foo_Insert]
as
declare @file varchar(30)
declare @c int=1
declare @n int =5
begin
while (@c<=@n)
begin
EXEC
('
BULK
INSERT Foo
FROM ''D:\SSIS_Work\Foo'+@c+'.txt''
WITH
(
FIELDTERMINATOR = ''\t'',
ROWTERMINATOR = ''\n'')'
)
set @c=@c+1
end
end

Now execute the SP
Exec [Foo_Insert]

Use of Sub datasheet in MS Access

When two tables have one or more fields in common, you can embed the datasheet from one table in another. An embedded datasheet, which is called a subdatasheet, is useful when you want to view and edit related or joined data in a table or query.

On above scenario Table1 and Table2 has a common values in ID column. To fetch respective Address from Table2 where ID is matching with Table1.
Follow the below steps one by one.

Select table name and common column name

Click ok

Note : If values are not matching it will show null values.

Drop Column if Exists in MS Access

Function ifFieldExists(ByVal fieldName As String, ByVal TableName As String) As Boolean
Dim db As DAO.Database
    Dim tbl As TableDef
    Dim fld As Field
    Dim strName As String
    Set db = CurrentDb
    Set tbl = db.TableDefs(TableName)
    For Each fld In tbl.Fields
        If fld.Name = fieldName Then
            ifFieldExists = True
            Exit For
        End If
    Next
End Function
Execution Process:
paste this code under a button
If ifFieldExists("Column_Name", "Table_Name") Then
db.execute "Alter table Table_Name drop column Column_Name"
Else
db.Execute "Alter table Table_Name add column Column_Name text"

End If

Drop table if Exist in MS Access

Public Function ifTableExists(TableName As String) As Boolean
Dim rs As Recordset 'Sub DAO Vars
On Error GoTo fs
'This checks if a Table is there and reports True or False.
Set db = CurrentDb()
'If Table is there open it
Set rs = db.OpenRecordset("Select * from " & TableName & ";")
ifTableExists = True
rs.Close
db.Close
Exit Function
fs:
'If table is not there close out and set function to false
Set rs = Nothing
db.Close
Set db = Nothing
     ifTableExists = False
  Exit Function
End Function
Execution Process:
paste this code under a button
If ifTableExists("Table_name") Then
db.Execute "Drop table Table_Name"
End If

Export Table from MS Access to SQL Server and Execute Store Procedure

Private Sub Command2_Click()
Dim db As DAO.Database
Set db = CurrentDb
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
'==========Execute Store Procedure with parameter passing===============
Dim P
P = InputBox("Please Enter Table Name")
cnn.ConnectionString = "Provider=SQLOLEDB;Data Source=SEREVER_NAME;Database=DB_Name;UID=USER_ID;PWD=USER_Passowrd"
cnn.Open
Set rs = New ADODB.Recordset
Set rs = cnn.Execute("EXEC [Process_Normalisation] " & P & " ")
Set rs = Nothing
cnn.Close
MsgBox "Sp Execution Successfully Done !!!"
'================export table to the server===========================
DoCmd.TransferDatabase _
  acExport, _
  "ODBC Database", _
  "ODBC;Driver={SQL Server};Server=SEREVER_NAME;Database=DB_Name;UID=USER_ID;PWD=USER_Passowrd;", _
  acTable, _
  "Import_process", _
  "Process"    
  MsgBox "File Exported to the server !!!"
END SUB

Import Access file through button in VBA MS Access

Private Sub Command2_Click()
Dim db As DAO.Database
Set db = CurrentDb
Const msoFileDialogFilePicker As Long = 3
Dim objDialog As Object
Set objDialog = Application.FileDialog(msoFileDialogFilePicker)
With objDialog
    .AllowMultiSelect = False
    .Show
    If .SelectedItems.Count = 0 Then
        MsgBox "!! No file selected.Please Select One !!"        
    Else
    For Each vrtSelectedItem In .SelectedItems
                FileName = vrtSelectedItem
               DoCmd.TransferDatabase acImport, "Microsoft Access", FileName, acTable, "Process", "Import_process", False
            Next
    End If
End With
END SUB

Select any particular Alphanumeric values from a column in MS Access:

From above scenario , i am just fetching BX values with numeric digits. Like 1000BX,6BX and etc..
Execute the below code and face the desire output.
SELECT F1, Mid(F1,4,Instr (F1, "BX")-2)
FROM tk
WHERE F1 Like '*[0-9]BX*';

Trim more than one spaces between words and keep only one space in MS ACCESS:

We can see three spaces are contain between AA in FNAME column. Execute the below query and see the results.

SELECT Replace(Replace(Replace(Replace(Fname,'  ',' '),'  ',' '),'  ',' '),'  ',' ') AS Expr1

FROM Table1;

Output:

Access Deny from one Database to another Database

Step 1 : Use master database and create a new DB with any name
use master
create database p_test
use p_test
Step 2 : Go to Security -> Logins , can see all users that have created earlier.

Step 3 : Will create a new user with a given password
create login Test_Login with password='test', check_policy = off

User created successfully.

Step 4 : If we execute select statement to access another DB still we can.

Step 5 : Follow the below Query(changing ownership).

use p_test

go

sp_changedbowner 'Test_Login'

Step 5 : Use master DB and execute the below query to access deny another DB

use master

go

deny VIEW any DATABASE to Test_Login

use master

go

execute as login ='Test_Login'

go

Step 6 : select count(*) from mso.dbo.Items_Copy

Following error will get from output:

Msg 916, Level 14, State 1, Line 5

The server principal "Test_Login" is not able to access the database "MSO" under the current security context.

Notes : After creating the user permission you can access only three databases.
select * from sys.databases

Change created DB owner to distinct owner

use p_test 

GO

sp_changedbowner 'sa'

Drop Login and DB

drop login Test_Login
drop database p_test

Protect Your Back end code in MS Access :

In this post i will show you how to set a password to protect your back end code from third party's in MS ACCESS.
Step 1 : Open MS ACCESS Database.
Step 2 : Create a new form with a Button
Step 3 : Right Click on that Button and go to Built Event

Step 4 : Go to Tools then Database Properties

Step 5 : Click Protection and set your own choice password

Step 6 : Close your Database and open it again

Select what are the new values has been updated in a table on a particular column :

Step 1 : create table
create table Auto_track(ID int unique identity(1,1),
Name varchar(50) default 'Reza',Value int)
Step 2 : Inserting Records with Default values
insert into Auto_track values(default,null)
go 10
Select * from Auto_track

Notes : Must be a primary key in your table
alter table Auto_track add primary key(ID)

Step 3 : Enable database tracking mode for a specific periods

ALTER DATABASE [YOUR_DB]
SET CHANGE_TRACKING = ON  
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)  
Step 4 :Enable Table tracking mode
ALTER TABLE [dbo].[Auto_track]  
ENABLE CHANGE_TRACKING  
WITH (TRACK_COLUMNS_UPDATED = ON)  
Step 5 :Update value column with some values
update Auto_track set Value=ID+100
Select * from Auto_track

Step 6 :Execute the below code will show the modified records
SELECT ISNUll(pn.Value,0) as Value 
from changetable(changes Auto_track, 1) as ct
INNER JOIN Auto_track pn on pn.ID = CT.ID
WHERE SYS_CHANGE_VERSION > 1 and CT.Sys_Change_Operation <> 'D'

Dynamically Transposing values into multiple columns if it contains with Delimiter :

I have a table named is #temp with following values

create table #Temp(ID int,barcode_type varchar(50))
insert into #Temp values(12345,'AAAA,BBBB,CCCC,DDDD,EEEE,FFFF,GGGG')
insert into #Temp values(12346,'GGGG,FFFF,EEEE,DDDD,CCCC,BBBB,AAAA')
insert into #Temp values(12347,'MMMM,NNNN,OOOO,PPPP,QQQQ,RRRR,SSSS')
select * from #Temp
Step1: will create multiple columns after counting the maximum words from #temp 
table (Barcode_Type column) into a separate table named #len_of_words.
Step2: Execute the following code and let see the results what will come.

Select ID,barcode_type,len(barcode_type) - len(replace(barcode_type, ',', '')) + 1 No_of_Count

into #len_of_words from #Temp

declare @c as int=1

declare @i as int

select @i=max(No_of_Count) from #len_of_words

while (@c<=@i)

begin

EXEC ('ALTER TABLE #len_of_words ADD barcode_type'+@c+' VARCHAR(100);')

exec('update t1 set barcode_type'+@c+'= NewXML.value(''/Product[1]/Attribute['+@c+']'',''varchar(50)'')

FROM #len_of_words t1

CROSS APPLY (SELECT XMLEncoded=(SELECT barcode_type AS [*] FROM #Temp t2 WHERE t1.ID = t2.ID FOR XML PATH(''''))) EncodeXML

CROSS APPLY (SELECT NewXML=CAST(''<Product><Attribute>''+REPLACE(XMLEncoded,'','',''</Attribute><Attribute>'')+''</Attribute></Product>'' AS XML)) CastXML')

set @c=@c+1

end

select * from #len_of_words