SQLTeam.com | Weblogs | Forums

Create SP or access query


#1

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


#2

Try to query like this

Select * from Linkedserver..AccessTable


#3

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 ?


#4

Hi,

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',
@srvproduct='Access',
@provider='Microsoft.Jet.OLEDB.4.0',
@datasrc='c:\test.mdb'
exec sp_addlinkedsrvlogin @rmtsrvname='Access',
@useself='false',
@rmtuser='Admin',
@rmtpassword=''

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'