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 ?
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'