Access DB already opened exclusively by another user !?!??

[stephenmbell]

I am trying to write simple asp page to extract some data out of a database for users to view only. The data is stored in an access db that is typically opened on certain machines throughout our company.

While developing this page, I imported the tables i need into a temporary database and everything worked fine. I have since changed my connection string to the live db and I get the following error...

Microsoft JET Database Engine (0x80004005)
The Microsoft Jet database engine cannot open the file '\\172.17.1.42\accshare\services\shop services.mdb'. It is already opened exclusively by another user, or you need permission to view its data.
/softwarerollout/includes/udf.asp, line 12

--

i have a function that returns me a recordset -- I assume this has somethign to do with some sort of locking or exclusive rights -- should I use different constants? is doing what i want to do even possible?

Function fnGetRecordset(strSQL)
   'declare local variables
  Dim rs
  'declare constants
  Const adOpenStatic = 3
  Const adLockReadOnly = 1
  Const adCmdText = 1
  'create recordset
  Set rs = server.CreateObject("adodb.recordset")
  'open recordset
  rs.open strSQL, strServicesConnect, adOpenStatic, adLockReadOnly, adCmdText
  'set active connection
  set objConn  = rs.ActiveConnection
  'return recordset
  set fnGetRecordset = rs
  'perform cleanup
  set rs = nothing
End Function

 

[nicklieb]

as it says in the error message. if you have access open ( application ), and then try and access it via a script you will get that error message.

it's been a long time since i used access, i'm pretty sure, you can't have concurrent sessions.

really you're better off using SQL Express which is free, and is actually a proper database (albeit some functionality removed)

http://msdn2.microsoft.com/en-us/express/aa718378.aspx

 

[stephenmbell]

I agree. I am probably better off using SQL express. The problem is, the database already exists. Tables exist in this database that are linked to other access databases. This web interface will not be the only way to get access to this information. I need a simple, real time way for some users to have read-only access to this data.

I understand what the error means, what I do not know, is if what I want to do is possible. Can I connect to a database (that is located on my network) through asp, that other users will still use this database by opening the mdb file.


Thanks

 

[jshesek]

No you can't pull from the access database while it's open by another user. How about making a copy and pulling from that?
Jess

 

[onpnt]

alright, let's get the one thing straight that seems cloudy.

Access = File
Database Server = Not a file ;-)

You cannot alter a file in use. Try it on a text file or something. It's windows way of handling the file system and file access

I have my own issues with the usage of access in the workplace. Well it's not all that much an issue other than it doesn't belong there. BUT I'm not going to tell you to throw your work away. Only say it's not 100% stable.

Jess already told you that you can't. I told you why. You're choice to alter a view sql statements ad install sqlexpress or live with access and its ldb locks.


[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[stephenmbell]

Again. I share your exact feelings about MS Access in the workplace - however, I am trying to do what I can with what ive got, and at the moment - it is ms access.

I guess the question is - if i can have an mdb file sitting on a network share and users on computers 1, 2, and 3 can open this file and use it simultaneously - what is different about it being opened by asp?

thanks again.

 

[guitarzan]

Your ASP program should be able to work the access database, even if other people have the database open in Access, as long as they are just editing data. The problems start when they go into "Design Mode" or do something that requires Exclusive Access... then you will see the errors you mention.

 

[stephenmbell]

So is my code, the way I have it correct? It should work.

BTW - here is my connection string:

strServicesConnect = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source= \\172.17.1.42\accshare\services\shop services.mdb"

 

[guitarzan]

Is the live db on a different server than the one running your ASP app? If so, you have to set the permissions corrrectly. I dont know all the details of this, but check out this link, which also highlights some of Access's limitations that onpnt alluded to;

http://databases.aspfaq.com/databas...-database-or-text-file-on-another-server.html

 

[stephenmbell]

Yes. The live DB is located on a different server than the machine running IIS.

I have read the link you posted - and took the path I thought necessary.

I have created a virtual directory on the machine running IIS which points to my server/share

strServicesConnect = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source= [URL unfurl="true"]http://localhost/ShopServices/Shop[/URL] Services.mdb;"

This is the error I receive:
Microsoft JET Database Engine (0x80004005) Not a valid file name.

I have tried replacing the " " with %20 and got the same error.

 

[guitarzan]

Again, I don't have experience getting this to work, but your connection string does not match the one in the "If the file is outside your LAN" sample.

 

[stephenmbell]

The file is not outside my LAN, unless I am misunderstanding. Both machines are on the same local area network, just not the same machine.

 

[guitarzan]

Sorry, I misunderstood that.

Can you access the virtual directory directly in IE using

http://WebServer/Folder

? Just trying to narrow down that the virtual directory is set up correctly...

 

[stephenmbell]

yep.. i can browse to the directory.. see the files listed in the dir

however i am still getting invalid file name?!?

Connection string:

strServicesConnect = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source " & _
	 "= [URL unfurl="true"]http://localhost/ShopServices/Shop[/URL] Services.mdb"

Recordset code

Function fnGetRecordset(strSQL)
   'declare local variables
  Dim rs
  'declare constants
  Const adOpenStatic = 3
  Const adLockReadOnly = 1
  Const adCmdText = 1
  'create recordset
  Set rs = server.CreateObject("adodb.recordset")
  'open recordset
  rs.open strSQL, strServicesConnect, adOpenStatic, adLockReadOnly, adCmdText
  'set active connection
  set objConn  = rs.ActiveConnection
  'return recordset
  set fnGetRecordset = rs
  'perform cleanup
  set rs = nothing
End Function

 

[johnwm]

You have a space in your filename

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints