How to get records from Local Access Databse Using OPENROWSET Function

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


0
SANJAY
2/6/2010 12:19:38 PM
sqlserver.programming 1873 articles. 0 followers. Follow

5 Replies
1877 Views

Similar Articles

[PageSpeed] 34

SANJAY SHAH-MICROBRAIN COMPUTERS PVT. LTD. (sanjay@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@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

0
Erland
2/6/2010 4:37:36 PM
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@sommarskog.se> wrote in message 
news:Xns9D17B35EAF964Yazorman@127.0.0.1...
> SANJAY SHAH-MICROBRAIN COMPUTERS PVT. LTD. (sanjay@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@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
> 


0
SANJAY
2/8/2010 6:21:20 AM
SANJAY SHAH-MICROBRAIN COMPUTERS PVT. LTD. (sanjay@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@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
0
Erland
2/8/2010 8:34:51 AM
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@sommarskog.se> wrote in message 
news:Xns9D1961759CABCYazorman@127.0.0.1...
> SANJAY SHAH-MICROBRAIN COMPUTERS PVT. LTD. (sanjay@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@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 


0
SANJAY
2/8/2010 12:17:07 PM
SANJAY SHAH-MICROBRAIN COMPUTERS PVT. LTD. (sanjay@microbrain.in) writes:
> 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 have a client program that access SQL Server, the client program
would have to access the Access database directly. Which is a better 
solution, even if you could access the Access database from SQL Server.
To borrow a proverb from Swedish, that would be to cross the brook for
water.

-- 
Erland Sommarskog, SQL Server MVP, esquel@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

0
Erland
2/8/2010 11:06:02 PM
Reply:

Similar Artilces:

Business Portal Error-SQL server does not exist or access denied
Hi, We are running business portal 4.0 for one of our customer. It was running correctly, however, they have changed the SQL server port (previously it was set as default 1433). After that the business portal becomes very slow and while creating a new request (purchase requisition) if we open the item pop up; it is showing exception "SQL Server does not exist or access denied...." Can any body tell me how can I provide the new port number to business portal connection to the database. Thanks and Regards, Waliullah, Thanks for using the newsgroups. I have a...

What's happened to the findfirst function in Access 2010?
I have a working Access 2007 Application which is now failing miserably in 2010. It hinges on location the first available working document in a table runing the following VBA code: With Me.Recordset .FindFirst "([fld1] + [fld2]) = 0" If .NoMatch Then .FindLast "([fdl1] + [fld2]) <> 0" Exit Sub End If .... FURTHER PROCESSING ... What must I do to correct this? Go through a record by record search? End With In 2007, this works correctly, stopping at the appropriate record (approc. rec 1385 in the recordset...

open two different Access reports
Hi, I was wondering if i can get some help here. I have two different reports that i want to open when a user clicks a button to view the reports for printing. Is there any way of popping them up at the same time in VBA? Thank you in advance Associates wrote: >Hi, > >I was wondering if i can get some help here. I have two different reports >that i want to open when a user clicks a button to view the reports for >printing. Is there any way of popping them up at the same time in VBA? > >Thank you in advance Yes. Call the DoCmd.OpenReport command twic...

I cannot get the pictures to show on screen in Publisher web previ
I have tried embedding and linked but nothing seems to work. Do I have to apply a master page? It is to be a one page web site. This is probably so basic but I am trying to teach myself how to use it. What do I have to do to have them show in preview and once uploaded? Thank you. Are you using FireFox to preview or IE? DavidF "rendul" <rendul@discussions.microsoft.com> wrote in message news:C9B0A640-6C87-4E9A-8A0E-5F32F7DAC0DE@microsoft.com... >I have tried embedding and linked but nothing seems to work. Do I have to > apply a master page? It is to be a one ...

Using mouse wheel in VB editor
Just tried using the mouse wheel to scroll within visual basic editor in excel but it wont let me. How can I turn this feature on. I am using Excel 2003. Thanks in advance. This is a known problem with the latest MS mouse drivers. You can either install version 4 of the drivers (current version is 5), or use FreeWheel, and freeware program at http://www.geocities.com/SiliconValley/2060/freewheel.html . -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "fullers" <fullers@discussions.microsoft.com> wrote in message ne...

Access 2007 and Vista code problem
I am developing a database on a XP SP2 machine. On a form I have created some buttons with event procedures that run 2 queries and open a form. I sent the db to someone running Access 2007 on a Vista machine and nothing happens when she clicks the buttons and there are no messages. I know nothing about Vista so thanks in advance for any help. Tim Might be a reference problem, have them open the open a code window and click tools references. See what says missing and tell them what should be there, Add it and it should work. Duff "Tim Reid" <TimReid@discussions.micr...

Send to certain person using specific email account
I send an email to a certain receipient but don't want to use the default email account. Is it possible that when I send to them (plain text) , that it will choose the other account I want to use to send it? Please advise how I set this up. Thanks. I do not believe that this can be achieved automatically through standard use of Outlook as you need to manually specify the name of the account to send from if different than the default. However, it would be possible using the Outlook object model. An Addin would need to be developed that monitored each mail item prior to sending. If the d...

Macro for Sharepoint List and another Table?? Same # records?
I am creating a database which we have a form made up of prepopulated data (from a sharepoint list) and also data input needed by a user (for example, comments, etc.). I have two tables: one sharepoint list that is linked to sharepoint website and another table that houses the additional user inputs [table 2]. I have linked the files based on the primary key. So, every record in my table 2 has a plus sign and it will expand and you will see the linked data from the sharepoint list. Is there a way that I can automatically create a record for every corresponding record ...

Use exchange from a trusted domain
Hi folks, I have domain a and domain b. Domain a is all set upp with exchange etc. Domain b was bought, and they don't have exchange. I have trust between the domains and file sharing and authentication are ok. What we want to do is, make users on domain b and let them use the exchange (on a seperate storage) on domain a. The users on domain b must logon to domain b, but need to have mailbox on domain a. Domain a are running on win2003 and exchange 2003. Domain are running on win2003 with forest and domain ready for exchange. Any advices? thanks in advance, - Bjarni Hi, Thi...

Qrp Date functions
Where do I find the various functions to modify the Reports like cast(DatePart(Dd,[Transaction].Time) as nvarchar) and others and what they mean???? Barry Found the information at MSDN Transact-SQL Reference Barry "Barry L" <barryl@eryanjewelers.com> wrote in message news:usd3uP1CIHA.1188@TK2MSFTNGP04.phx.gbl... > Where do I find the various functions to modify the Reports > like cast(DatePart(Dd,[Transaction].Time) as nvarchar) and others > and what they mean???? > > Barry > ...

Using Access 2003 on client to see Sharepoint 2007
Testing a Sharepoint environment and I can easily manipulate and run the database from Access 2007, but when I try to use Access 2003 it wants me to save the database to a location. The Access 2003 has the compatiblity pack, but it seems to not work when I download it. So the question is using Sharepoint 2007 and posting the Access 2007 format to it, can a user go to the sharepoint site and use a 2003 Access database that has the compatibility pack and open without having to download it? -- Message posted via http://www.accessmonster.com Send check for $500.00 to: David A Jenn...

Getting the BCC field by default.
I would like the BCC field to show by default but cannot find the setting for this in 2007. Anyone know where this is located? In a new message window, use the Options chunk to display the "Show BCC." --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, JC HARRIS asked: | I would like the BCC field to show by default but cannot find the | setting for this in 2007. Anyone know where this is located? Thanks Milly. I had...

Some Excel formatting functions taking a long time to work #2
Excel 2000 SP3 When I use some formatting functions for the first time in a session, such as bold, increasing font size etc, it takes up to 30 seconds to work. Meantime Excel is locked up until it completes that formatting call. I suspect faulty DLL? Has anyone experienced this? How to fix (other than a complete re- install) ? Any advice appreciated. Piri On Nov 5, 8:39=A0am, Piri <wiremu.pare...@hotmail.com> wrote: > Excel 2000 SP3 > When I use some formatting functions for the first time in a > session, such as bold, increasing font size etc, it takes =A0up to 30 > secon...

compact database in VBA access 2007
Hello, I have recently upgrade to office 2007 from office 2003. To compact a database from within the database itself, I used the follwing code. Unfortunately it no longer works in access 2007. Is there some similar code that will work? Public Function FncCompactTheCurrentDB() CommandBars("Menu Bar"). _ Controls("Tools"). _ Controls("Database utilities"). _ Controls("Compact and repair database..."). _ accDoDefaultAction End Function Thank You, SL On Thu, 28 Jan 2010 17:34:01 -0800, SL <SL@discussions.microsoft....

Getting rid of selection
How can I get rid of the selection rectangle? It seems that it's always there, with a heavy black rectangle, or there's a light black rectangle marking where it was. I'm trying to get rid of it altogether, so I can capture an image of the sheet for use in a webpage. I can achieve the effect that I want by selecting a cell which is outside the area that I'm trying to capture, but now that I've found that I cannot get rid of it entirely, it is driving me nuts trying to do so. -- Steve Swift http://www.swiftys.org.uk/swifty.html http://www.ringers.org.uk You could al...

LDAP Write access?
My ldap server allows Write access to entries - and a few clients now support this. Any plugins available for Outlook to allow this too? Thanks None that I'm aware of. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Outlook Tips by email: dailytips-subscribe-request@lists.outlooktips.net Subscribe to Exchange Messaging Outlook newsletter: EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH.EASE.LSOFT.COM Outlook Tips: http://www.outlook...

Adding a Macro to a VLookup Function
In Excel 2000 -- I would like to create a summary spreadsheet (sheet2). For each time that "Name" appears in sheet 1, row *, take information from the same row, but columns 2 and 6 and bring it over to sheet 2 in the designated area. I know that you need to do a "VLookup" function and I got that to work. I know that you also need a "Loop" statement, so that it will continue to perform the Vlookup and bring over the information for each time that the name appears. I don't know how to write the formula so that the VLookup and the Loop are combined. Examp...

Workplace Queues
We just rolled out CRM a few weeks ago. I'm getting a lot of complaints from the users about the thousands of items showing up in their My Work\Queues\In Progress folder. When I look at my own items, I have about 1000 activities showing in my In Progress folder but when I open them up most of them are owned by someone else. According to the Help description of this folder, only items that I have accepted should show up in my In Progress folder. I've never accepted anything, so I'm not sure anything whatsoever would be showing up in this folder. We used Scribe to import ...

How Use ShellExecute with a CMemFile
Hello, I need to store files of various types (such as .doc, .xls, .jpg, .pdf, ..wmv, ...) in a database. Upon retrieval of such a file I have the file as data in memory in a CMemFile. I need to then "display" the file in the aproriate application. If I were to write the CMemFile to disk and create an actual file I could then, of course, use ShellExecute() to launch , say Word to display a .doc file. However, I would much prefer to avoid the overhead of writing the data to an actual file and then dealing with having to detect when the User is done and cleaning up the file I had ...

Using INDIRECT within functions
I learned a bit from JLathman in a previous post but tried to use the same methodology with this formula (also learned from this Group to get the last entered value in a column) without much luck. The original formula was this with 2009 sheet being static: =LOOKUP(2,1/('2009'!H3:H65536<>""),'2009'!H3:H65536) Trying to make it dynamic I tried the following without success: =LOOKUP(INDIRECT("2,1/(" & O1 & "!$H$3:$H$65536<>"")," & O1 & "!$H$3:$H$65536)")) Did I miss something or are...

Date function quit working
Hi, I have an Access 2002 application that I have been running on Windows XP SP2 without issue. I just installed the application (running in Access Runtime) on a Windows Vista Home Premium machine. Now, anywhere I used the =Date() function, it fails and just shows #Name? I also have a subform on one of my forms that has now gone blank. It also uses the date function. I had this problem when I converted to Windows XP several years ago and updating the OWC10.dll to version 6619 fixed both issues. However, everything I have read says that reference file makes no difference to the Access...

MS Access 2007 code not running
I have created a brand new database in Access 2007 and linked some VB code to run on Form_Load. This code worked perfect with no problems the day I created it. Now today I opened the same form and it is not running the Form_Load code anymore (and yes it is still there, I did not delete it). I got frustrated so I copied and pasted the code to Form_Current and several On_Change events and set breakpoints at the beginning of all the events just to see if it was the Form_Load that was not triggering, but NO events are triggering my VB code. Any ideas? See this page: http://accessjunkie.com/fa...

unable to grant existing user access to TEST company
Set up test company using live company data; ran the script referenced in CustomerSource article ID#871973; can grant and remove user access in all other companies; ran grantsql 9.2; the dexsql log shows the user already has access probably because of using the other company data for the test; I can set up a new user and grant them access, existing users who already have access can view the company...but, I cannot figure out how to grant access to the TEST company to an existing user. The error popup reads "The user could not be added to one or more databases." Dynamics 9.0,...

Get info from Fidelity
I use money's portfolio manager to retrieve info from my Fidelity accounts. However, it only retrieve stock and bond informations. It did not retrieve the money market fund information. How can I make it download money market fund info? In microsoft.public.money, Peter Tso wrote: >I use money's portfolio manager to retrieve info from my >Fidelity accounts. However, it only retrieve stock and >bond informations. It did not retrieve the money market >fund information. How can I make it download money market >fund info? Fidelity does things a differently th...

total group & max function
I am using Access 2003 I created a Db for Machine Mtce. The table consists of 3 tables, (1) Basic Mach Infor (ie Mach#, Mach Name, Mfg etc) - (2) Operations Table - consists of different prev mtce (PM) that need to be completed at various time frames. Ex: "Mach 100" could have 3 different operations; Oper # 100A to change the oil due every 90 days, Oper # 100B to check the belts due every 180 days, Oper # 100C to grease the machine due every 365 days. The 3rd table is the "PM History" table which includes the history of all of the PM's completed with Work Order...