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

Permutation and Combination in SQL Server :

declare @String varchar(10) = 'TIGER'
;with s(t,n)
as 
(
select substring(@String,1,1),1
union all
select substring(@String,n+1,1),n+1
from s where n<len(@String)
)
,j(t) 
as 
(
select cast(t as varchar(10)) from s
union all
select cast(j.t+s.t as varchar(10))
from j,s where patindex('%'+s.t+'%',j.t)=0
)
select t from j where len(t)=len(@String)

Vlookup ,fetching values from one sheet to another Sheet in Excel:

Note : If col1 from Sheet1 is matching with Col1 Sheet2 then return
[Sheet1].Extracted Values]=[Sheet2].[Fetching Values]
Formula :
=IF(LEN(A3)>1,VLOOKUP(A3,Sheet2!A:B,2,0),"")
Notes : (A3) is my start postion in sheet1
or
=IF(LEN(A1)>1,VLOOKUP(A:A,Sheet2!A:B,2,0),"")
Notes : (A1) is my start postion in sheet1

Check database Recovery model status :

Execute the below query , we can see type of recovery model consisting by the database.
SELECT name, (SELECT DATABASEPROPERTYEX(name, 'RECOVERY')) RecoveryModel 
FROM master..sysdatabases ORDER BY name

Update Third column if First Column has Duplicate values and Second column are Distinct with Comma delimiter:

create table #temp(ID int, ID2 Varchar(10),Results varchar(50))

insert into #temp values(1,'2',null)

insert into #temp values(1,'3',null)

insert into #temp values(1,'4',null)

insert into #temp values(2,'2',null)

insert into #temp values(2,'3',null)

insert into #temp values(2,'4',null)

insert into #temp values(2,'4',null)

insert into #temp values(3,'1',null)

insert into #temp values(4,'5',null)

insert into #temp values(5,'6',null)

insert into #temp values(5,'6',null)

select * from #temp

Execute below code:

;with cte

as

(

 select distinct t.ID,t.ID2,Results,

  STUFF((SELECT distinct ', ' + t1.ID2

         from #temp t1

         where t.[id] = t1.[id]

            FOR XML PATH(''), TYPE

            ).value('.', 'NVARCHAR(MAX)')

        ,1,2,'') department

from #temp t

)

Update t set t.Results=c.department from #temp t

inner join cte c

on t.ID=c.ID

Some Tricky Ideas:

Swap values from one column to another with some Criteria's :
Most of interviewers will ask this question.Please check below example
declare @a varchar(10)
declare @b varchar(10)
set @a='Male'
set @b='Female'
select @a, case when @a='Male' then 'Female' end
select @b, case when @b='Female' then 'Male' end
Output:
@a if Male then print Female or
@b if Female then print Male

Update column A with column B values if A is Male and B is Female.

create table #tmp (A varchar(20),B varchar(20))

insert into #tmp values('Male','Female')

insert into #tmp values('Male','AA')

insert into #tmp values('Female','VV')

insert into #tmp values('Male','Female')

update #tmp set A=B , B=A 

where A='Male' and B='Female'

Transfer table records into different Schema through SSIS :

1 - Create a new Schema in Sql Server
create schema msm
2- Go to Visual Studio and create a new package
3- Select Source file and connect your server table (OLE DB Source)
4- Select Destination (OLE DB Destination)
5- Double click on OLE DB Destination and configure your Server Name
6-

 Click second New button and add your table name with new schema details. It will come like
CREATE TABLE [OLE DB Destination] (
    [CustomerId] int,
    [FirstName] nvarchar(50),
    [LastName] nvarchar(50),
    [Salary] money
)
put your schema and any name for the new table like
CREATE TABLE [new_schema].[Table_name] (
    [CustomerId] int,
    [FirstName] nvarchar(50),
    [LastName] nvarchar(50),
    [Salary] money
)
7- Execute your package
8-
select * from [dbo].[Customer]
select * from [msm].[new_tab]
Data copied into new schema with new_tab name

Trigger for text or Ntext or image data type in SQL Server:

--Main table--
CREATE TABLE [dbo].[Employee_Test]
(
[Emp_ID] [int] IDENTITY(1,1) NOT NULL,
[Emp_name] [text] NULL,
[Emp_Sal] [int] NULL,
[Address1] [varchar](100) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
--Trigger table--
CREATE TABLE [dbo].[Emp_trigg]
(
[Name] [varchar](max) NULL,
[Old_name] [varchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

--Trigger--

ALTER TRIGGER [dbo].[FieldUpdated]

ON [dbo].[Employee_Test]

INSTEAD OF UPDATE

AS

  UPDATE [Employee_Test] SET Emp_name = (SELECT Emp_name FROM Inserted)                     

  WHERE Emp_ID = (SELECT Emp_ID FROM Inserted)                                     

   IF (UPDATE (Emp_name))

  BEGIN

    DECLARE @oldValue nvarchar(max)

    DECLARE @newValue nvarchar(max)

    SET @newValue = (SELECT CONVERT(nvarchar(max), Emp_name) FROM Inserted)

    SET @oldValue = (SELECT CONVERT(nvarchar(max), Emp_name) FROM Deleted)

    IF (@oldValue != @newValue)

    BEGIN

    insert into Emp_trigg(Name,Old_name) values(@newValue,@oldValue)

    END

  END

If Filed exist in MS Access then Drop it:

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:
If ifFieldExists("INDC", "Import_process") Then
Else
db.Execute "Alter table Import_process add column INDC text"
End If

IF table exist in MS Access then Drop it :-

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:-
If ifTableExists("Import_process") Then
db.Execute "Drop table Import_process"
End If

Select mid string between two string :

DECLARE @c varchar(100)
SET     @c = '0116522656517989898'
SELECT SUBSTRING(STUFF(@c, 1, CHARINDEX('01',@c), ''), 0, CHARINDEX('17', STUFF(@c, 1, CHARINDEX('01',@c), '')))
Output:
1165226565

Select only Numeric values from a Column in MS Access:

Public Function fExtractNumeric(strInput) As String
    ' Returns the numeric characters within a string in
    ' sequence in which they are found within the string
    Dim strResult As String, strCh As String
    Dim intI As Integer
    If Not IsNull(strInput) Then
        For intI = 1 To Len(strInput)
            strCh = Mid(strInput, intI, 1)
            Select Case strCh
                Case "0" To "9"
                    strResult = strResult & strCh
                Case Else
            End Select
        Next intI
    End If
    fExtractNumeric = strResult
End Function
OutPut: