SQLTeam.com | Weblogs | Forums

Create SP or access query


how to create stored procedure or access query msaccess in linked server sql server 2012


Try to query like this

Select * from Linkedserver..AccessTable


for access table I can do it but for access query in msaccess can you give me sample query in sql server ?

or how to create stored procedure on linked server to msaccess ?



Hope you can go till create new linked server...
Now in the provider name select Microsoft.Jet.OLEDB.4.0 Provider (OLEDB is faster then ODBC AFAIK)
Specify a linked server name you wish
Datasource will the Path to mdb file Ex: "c:\test.mdb"
Product Name will be "Access"

Else you can try below T-SQL too
exec sp_addlinkedserver @server='Access',
exec sp_addlinkedsrvlogin @rmtsrvname='Access',

Regarding login use Username Admin and NULL as password when you trying in GUI.

Now to get the remote table run below T-SQl
sp_tableex N'Access'