By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,777 Members | 1,311 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,777 IT Pros & Developers. It's quick & easy.

MS Access on a web farm

P: 9
Is there any way to set up connecting to an MS Access file on a 3rd server (which is on a domain) from a Web Farm (which is not on the domain)?

Hardware/OS:
Windows Server 2003
Web Farm (two web servers which are network load balanced, or NLBs)
ASP.NET v1.1
Microsoft Access 2003

We're seeking to set up an NLB webfarm. One of the web-apps uses a MS Access database.
Given the NLB (2 boxes), the MS Access database can't reside on either (to remove "single point of failure" scenario).
The NLBs are not part of the domain.
We've put the MS Access file on a 3rd server (which is part of the domain).
We can see files on the 3rd server (such as serving up images and other pages. In the Web.Config, we put this connection string:
\\Server3\MSAccess\DatabaseFile.mdb

The folder \\Server3\MSAccess| is shared for full control for everyone; guests included (yeah, security hole...)

We keep getting this following error, to which we've not been successful in resolving:

The Microsoft Jet database engine cannot open the file '\\Server3\MSAccess\DatabaseFile.mdb'. It is already opened exclusively by another user, or you need permission to view its data.

I did visit Microsoft's website and read through several KB articles, but no success; #253580, #315276 (NTRights Utlitity)


Is there any way to "mount" \\Server3\MSAccess\ shared folder as a specific lettered drive when the sever starts up (vs having to log on)?

Any insights appreciated.

Gary Noter
Feb 20 '07 #1
Share this Question
Share on Google+
11 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Hi Gary

I've frequently used Access databases though a citrix farm but unfortunately had nothing to do with setting them up there. I will have a word with some experts who may be able to help and see what can be done.

Mary
Feb 21 '07 #2

Motoma
Expert 2.5K+
P: 3,235
I am entirely unfamiliar with Access; however, I do have a bit of knowledge with databases. What it sounds like you have here, is a permissions conflict. I don't know how you are trying to access the Access database, because you did not post any code to accompany your question, but the error alludes to the idea that your user does not have permissions on the database (not to the file mind you, the database itself). Either that, or someone else has an exclusive lock on the database. Is the file in use by anyone else?
Feb 21 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Good suggestion Motoma. I assumed the database was already working correctly in a multi user environment.

In the database go to Tools - options - advanced and check that the default open mode is not set to Exclusive. It should be set to Shared. Also the Default Record Locking should be set to Edited record although this would not be causing the error.

Mary
Feb 21 '07 #4

Expert 5K+
P: 8,434
In the database go to Tools - options - advanced and check that the default open mode is not set to Exclusive. It should be set to Shared. ...
Quick question, mostly to satisfy my curiosity. Does this apply to that specific database, or to Access as a whole? (And does it need to be set per user?)
Feb 21 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Quick question, mostly to satisfy my curiosity. Does this apply to that specific database, or to Access as a whole? (And does it need to be set per user?)
Applies to specific database rather than Access or a user. If the database is set to Exclusive then the default open mode of that database will be in exclusive mode and no one else will be able to open it except in read only mode.

Mary
Feb 21 '07 #6

P: 9
Motoma, all,

I did post "some" code, per se.

I'm using ASP.NET v1.1, using a standard OLEDb connection (referencing the database by path [see code below]).

From the ASP.NET v1.1 "web.config" file (I'll refer to this as ConnectStringA)
Expand|Select|Wrap|Line Numbers
  1.  <appSettings>
  2.     <add key="strConnectAccess" value="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\Server3\MSAccess\DatabaseFile.mdb"/>
  3. </appSettings>
  4.  
actual "connect" code:
... I'm pausing here.
I went to go look at the connection code and there is a direct inline connection string to another database which ap[ears to not be failing:
\\Server3\C$\MSAccess\LogError.mdb
--> I'll refer to this as ConnectStringB,

The difference between ConnectStringA and ConnectStringB is that ConnectStringA assumes a shared folder, whereas ConnectStringB goes direct, as it includes the "\C$" element.

Well, I added the "\C$" to ConnectStringA, (now "\Server3\C$\MSAccess\DatabaseFile.mdb") and it still fails.


Yes, I would agree there is some sort of permissions error. The database is set for Shared.

HEre is my "connect" code (modified to protect a client's database)
Expand|Select|Wrap|Line Numbers
  1.     Sub btnlogin_click(ByVal sender As Object, ByVal e As EventArgs)
  2.         Dim conpw As OleDb.OleDbConnection
  3.         Dim cmdpw As OleDb.OleDbCommand
  4.         Dim strsql As String
  5.  
  6.         Dim dappw As OleDb.OleDbDataAdapter
  7.         Dim dstpw As DataSet
  8.         Dim rowpw As DataRow
  9.         Dim bldpw As OleDb.OleDbCommandBuilder
  10.         Dim inti As Integer
  11.         Dim rdrpw As OleDb.OleDbDataReader
  12.  
  13.         Dim xx As String
  14.         Dim yy As String
  15.         Dim strmsg As String
  16.         Dim strcounties As String
  17.         Dim lngposition As Long
  18.         Dim strIsCSR As String
  19.  
  20.         If txtIsCSR.Checked = "True" Then
  21.             Session.Contents("IsUser2") = "True"
  22.             Session.Contents("userName2") = txtUserName.Value
  23.             Session.Contents("userPass2") = txtPassword.Value
  24.             Response.Redirect("user2Logon.aspx")
  25.         End If
  26.  
  27.         lngposition = InStr(txtPassword.Value, "'")
  28.         If lngposition > 0 Then
  29.             Response.Redirect("loginError.aspx")
  30.         End If
  31.  
  32.  
  33.         conpw = New OleDb.OleDbConnection(ConfigurationSettings.AppSettings("strConnectAccess"))
  34.         conpw.Open()
  35.  
  36.  
  37.         strsql = "SELECT * " & _
  38.         "FROM [ User] " & _
  39.         " WHERE ( ([ User]].UserName = '" & txtUserName.Value & "') AND ([ User].password = '" & txtPassword.Value & "') )  "
  40.  
  41.         cmdpw = New OleDb.OleDbCommand(strsql, conpw)
  42.         rdrpw = cmdpw.executereader
  43.  
  44.         xx = 0
  45.         While rdrpw.read
  46.             xx = xx + 1
  47.             strmsg = rdrpw.item("userID") + rdrpw.item("userName")
  48.             strcounties = strcounties + strmsg
  49.         End While
  50.  
  51.         conpw.close()
  52.  
  53.         If xx > 0 Then
  54.             Session.Contents("counties") = txtMemberid.Value
  55.             Response.Redirect("Here.aspx")
  56.         Else
  57.             If Len(Trim(Session.Contents("logintrys"))) = 0 Then
  58.                 Session.Contents("logintrys") = 0
  59.             End If
  60.             ctry = (Session.Contents("logintrys"))
  61.             ltrys = CInt(ctry) + 1
  62.             If ltrys > 6 Then
  63.                 Response.Redirect("lockout.aspx")
  64.             End If
  65.             ctry = CStr(ltrys)
  66.             Session.Contents("logintrys") = ctry
  67.             Response.Redirect("loginError.aspx")
  68.         End If
  69.  
  70.     End Sub
  71.  
So, in all, this error [below] is occuring, it does point to "permissions" of some kind. Thing is, how to I "connect" from a webfarm not on a domain to a server (Server3) which is on a domain?
The Microsoft Jet database engine cannot open the file '\\Server3\C$\MSAccessDBs\Database.mdb'. It is already opened exclusively by another user, or you need permission to view its data.
Feb 21 '07 #7

Motoma
Expert 2.5K+
P: 3,235
Try running the app from the machine the database is on (\\Server3\C$\MSAccessDBs\Database.mdb should still work from the local machine). Do you still get the error?
Feb 21 '07 #8

P: 9
Motoma,

Yes, the database access works just fine when running the application on the same server (no access errors) and using the UCN name
( \\Server3\MSAccessDB\DatabaseFile.mdb )
Feb 21 '07 #9

Motoma
Expert 2.5K+
P: 3,235
Motoma,

Yes, the database access works just fine when running the application on the same server (no access errors) and using the UCN name
( \\Server3\MSAccessDB\DatabaseFile.mdb )
And have you been able to open the database in Access, using the filename \Server3\MSAccessDB\DatabaseFile.mdb?
Feb 21 '07 #10

Expert 5K+
P: 8,434
So, in all, this error [below] is occuring, it does point to "permissions" of some kind. Thing is, how to I "connect" from a webfarm not on a domain to a server (Server3) which is on a domain?
Perhaps this is more to do with the security settings in use on the connection between the webfarm and server, rather than the specific folder permissions.
Feb 21 '07 #11

Expert 5K+
P: 8,434
Applies to specific database rather than Access or a user. If the database is set to Exclusive then the default open mode of that database will be in exclusive mode and no one else will be able to open it except in read only mode.
Thanks for that, Mary. However, after browsing around the settings, I would question this response.

Other options nearby include "open databases using record-level locking" and "Default file format". Neither of these would make sense for a single database, only for Access as a whole.
Feb 21 '07 #12

Post your reply

Sign in to post your reply or Sign up for a free account.