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