CREATE TABLE in Access Database

This script creates a table tblCategory with ID is primary key and auto number,  CategoryName  VARCHAR(255)

CREATE TABLE [tblCategory] (
    [ID] AUTOINCREMENT,
    [CategoryName] TEXT(255),
    [ParentID] LONG,
    [GroupID] LONG,
    CONSTRAINT [PrimaryKey] PRIMARY KEY ([ID])
);

Create linked server to Access 2007 accdb file

EXEC master.dbo.sp_addlinkedserver @server = N'AccessLinkedServerName', @srvproduct=N'Access', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'D:\Projects\MyAccessDB.accdb'
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'AccessLinkedServerName', @locallogin = NULL , @useself = N'False'

MS Access & MS SQL data type

Do you know the equivalent data types between MS Access and MSSQL?
What is the equivalent of MSSQL 'text' data type in MS Access?

A. memo
B. text
C. nvarchar
D. OLE Object
E. Attachment
F. char

Answer:[A]
Highlight to find out the answer.

Create SQL Linked Server to Access Database

Sometimes, you need to query Access database directly in SQL Server. In that case, you can create a linked server between SQL Server and Access by using this query.

EXEC sp_addlinkedserver
@server = 'AccessNorwind',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = 'D:\AccessDatabase\NWind.mdb'

After creating, you can run query like this:
SELECT *
FROM AccessNorwind...Orders