How to get records from Local Access Databse Using OPENROWSET Function
in [Programming SQL]
|
Prev: Can you improve this TSQL: I am converting DATETIME to CHAR()
Next: Connection strings going from local drive to a network drive using Wizards in Visual Web Developer?
From: SANJAY SHAH-MICROBRAIN COMPUTERS PVT. LTD. on 6 Feb 2010 07:19 Dear Sir, I am using OPENROWSET Function to open Access Database File and my Query is as follows. SELECT Field1 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'D:\Sanjay\My Projects\Settings.mdb'; 'admin'; '', AppVersion) If I run this application on client computer and the specified file is exist on client computer then it display error as follows because it tries to search file on server [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "'D:\Sanjay\My Projects\Settings.mdb' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.". Now please help me how I can get records from Local Access Database, when I connected to SQL Server ? Please note my SQL Server is online at Data Centre. Sanjay Shah
From: Erland Sommarskog on 6 Feb 2010 11:37 SANJAY SHAH-MICROBRAIN COMPUTERS PVT. LTD. (sanjay(a)microbrain.in) writes: > I am using OPENROWSET Function to open Access Database File and my Query > is as follows. > > SELECT Field1 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'D:\Sanjay\My > Projects\Settings.mdb'; 'admin'; '', AppVersion) > > If I run this application on client computer and the specified file is > exist on client computer then it display error as follows because it > tries to search file on server > > [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB provider > "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message > "'D:\Sanjay\My Projects\Settings.mdb' is not a valid path. Make sure > that the path name is spelled correctly and that you are connected to > the server on which the file resides.". > > Now please help me how I can get records from Local Access Database, > when I connected to SQL Server ? > > Please note my SQL Server is online at Data Centre. You would need to use UNC notation: \\Yourmachine\yourshare\Settings.mdb You would have to define a share on your computer which you give rights to the service account for SQL Server to access. You would also have to make sure that there is no firewall in the way. I cannot say that this is something to recommend. I think it would be better to put the Access database on a file server, to which both you and the service account for SQL Server has access. (But note that if the service account is LocalSystem or somesuch, SQL Server is entirely unable to access network resources.) I would suggest that you discuss with your DBA and/or your network administrator what could be the best solution. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: SANJAY SHAH-MICROBRAIN COMPUTERS PVT. LTD. on 8 Feb 2010 01:21 Hi Erland, I thanks for your reply. But my server is at Data Centre not in my office. So it is not possible your ways. Thanks, Sanjay Shah "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9D17B35EAF964Yazorman(a)127.0.0.1... > SANJAY SHAH-MICROBRAIN COMPUTERS PVT. LTD. (sanjay(a)microbrain.in) writes: >> I am using OPENROWSET Function to open Access Database File and my Query >> is as follows. >> >> SELECT Field1 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'D:\Sanjay\My >> Projects\Settings.mdb'; 'admin'; '', AppVersion) >> >> If I run this application on client computer and the specified file is >> exist on client computer then it display error as follows because it >> tries to search file on server >> >> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB provider >> "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message >> "'D:\Sanjay\My Projects\Settings.mdb' is not a valid path. Make sure >> that the path name is spelled correctly and that you are connected to >> the server on which the file resides.". >> >> Now please help me how I can get records from Local Access Database, >> when I connected to SQL Server ? >> >> Please note my SQL Server is online at Data Centre. > > You would need to use UNC notation: > > \\Yourmachine\yourshare\Settings.mdb > > You would have to define a share on your computer which you give rights > to the service account for SQL Server to access. You would also have to > make sure that there is no firewall in the way. > > I cannot say that this is something to recommend. I think it would be > better to put the Access database on a file server, to which both you > and the service account for SQL Server has access. (But note that if > the service account is LocalSystem or somesuch, SQL Server is entirely > unable to access network resources.) > > I would suggest that you discuss with your DBA and/or your network > administrator what could be the best solution. > > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >
From: Erland Sommarskog on 8 Feb 2010 03:34 SANJAY SHAH-MICROBRAIN COMPUTERS PVT. LTD. (sanjay(a)microbrain.in) writes: > I thanks for your reply. But my server is at Data Centre not in my office. > So it is not possible your ways. I suspected that. However, there are no other options. There is no way you can query a remote data source from SQL Server, which is so remote that SQL Server has no access to it. I don't know what your final aim is, but you will have to find a different solution. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: SANJAY SHAH-MICROBRAIN COMPUTERS PVT. LTD. on 8 Feb 2010 07:17
Hi Erland, Once again Thanks. This my Access Database is only to save setting of user. If there is no other way, I have to open then with other connection. If you find any way, then please reply. Sanjay Shah "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9D1961759CABCYazorman(a)127.0.0.1... > SANJAY SHAH-MICROBRAIN COMPUTERS PVT. LTD. (sanjay(a)microbrain.in) writes: >> I thanks for your reply. But my server is at Data Centre not in my >> office. >> So it is not possible your ways. > > I suspected that. However, there are no other options. There is no way > you can query a remote data source from SQL Server, which is so remote > that SQL Server has no access to it. > > I don't know what your final aim is, but you will have to find a different > solution. > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |