April 18, 2009 at 5:36 am
Hi i have posted this question in otehr forum also, but dint get any reply. SO posting it here. ,
I need to connect to Ms-access database which is present in another machine(Server) and use the tables in it for querying.. also make use of soem joins....
I have seen openrowset function but, i guess it works only if access database is present in the local machine.
My local server which does not have any Ms-Acess installed should be able to connect to another machine which has Access DB in it..
Is this Possible.. Can anyone suggest a solution.
April 18, 2009 at 6:41 am
Hi
I'm no pro for MS-Access but did you have a look to BOL for "linked servers"? Look for "sp_addlinkedserver", there are some examples how to connect to MS-Access databases. Sure the UNC path to the Access database needs to be available from your server.
Greets
Flo
April 18, 2009 at 6:46 am
Florian Reischl (4/18/2009)
HiI'm no pro for MS-Access but did you have a look to BOL for "linked servers"? Look for "sp_addlinkedserver", there are some examples how to connect to MS-Access databases. Sure the UNC path to the Access database needs to be available from your server.
Greets
Flo
I am sorry.. but i havent understood 'UNC'. what do u mean by that
April 18, 2009 at 6:52 am
swathi.g (4/18/2009)
Florian Reischl (4/18/2009)
HiI'm no pro for MS-Access but did you have a look to BOL for "linked servers"? Look for "sp_addlinkedserver", there are some examples how to connect to MS-Access databases. Sure the UNC path to the Access database needs to be available from your server.
Greets
Flo
I am sorry.. but i havent understood 'UNC'. what do u mean by that
The network path to your Access database like:
\\ServerOrIp\SharedFolderName\Path1\Path2\YourDb.mdb
It has to be reachable from your server and the server needs read/write rights for MS-Access. For further information about UNC paths see Wikipedia UNC Path.
Greets
Flo
April 29, 2009 at 5:24 am
Swati
You can try this:
SELECT CustomerID, CompanyName
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'\\servername\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb'; -- change the path here
'admin';'',Customers)
GO
Rgds
Mohan Kumar VS
April 29, 2009 at 6:06 am
Thanks For Your Reply Mohan.:-)
The above code which you gave works fine when logged in using sql server authentication. But does not with Windows authentication. The Error Message was like - The file which is been accessed is either in use or you dont have permissions to open it.
April 29, 2009 at 6:51 am
Swati
Windows Authentication requires that SQL Server services must run as domain users in trusted domains or as network services.
To authenticate both ends of a connection, Windows Authentication uses the credentials of the Windows user account on which the SQL Server instances are running. Therefore, the user account of each server instance must have the permissions needed to log in and send messages to each of the other server instances.
Rgds
Mohan Kumar VS
April 29, 2009 at 10:27 pm
Mohan Kumar (4/29/2009)
SwatiWindows Authentication requires that SQL Server services must run as domain users in trusted domains or as network services.
To authenticate both ends of a connection, Windows Authentication uses the credentials of the Windows user account on which the SQL Server instances are running. Therefore, the user account of each server instance must have the permissions needed to log in and send messages to each of the other server instances.
Rgds
Mohan Kumar VS
So how do i give permission to the user account. I dont have any knowledge about this topic.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply