it is a first time i post question in any site , i need a fast help
i made a Application by VB6 and use ADO in a small company that has 10 computers Windows XP , my Database is Access mdb file my Application works well but in one device i receive an error
<<The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data >> it appears randomly in a computer not a specific computer
That sounds like locking and concurrency issues. I think you need to determine where it is happening and where, maybe two people trying to update the same table at the same time, and then go from there. No one can offer a solution until you determine that. I'm in a MS SQL environment and traces and logging could find that. I don't know about Access.
Thanks TysonlPrice For Replied I use ADO and my code like that
rsCustomer.Open "Customers", CN, adOpenDynamic, adLockOptimistic
ADo can manage that issue even when 2 person update the same Record , i have to receive another error
but this message prevent the user from opening the application
Are you using binding, Data Controls, Data Environment etc ?
Are you maintaining connections to the DB, or do you do what I do -
- Pure ADO code (no binding, etc)
- Connect, get data, and disconnect the RS immediately.
- Use Action updates (rather than rs) to insert, modify, delete records
The more information you give us, the quicker you will get a solution
Is there a chance that someone has opened the database with Access first?
If memory serves some versions of Access will try to open the database for exclusive access by default and if that be the case other apps can not access it while it is opened in this manner.
I seem to remember there being a setting for this in Access but it has been a long time since I encountered this so can't remember where the setting would be. I also think it may have changed on later versions but am not sure.
Aside from that is there any place in your app where you open a connection for exclusive access?
Thanks All i am very happy that persons give me a support and i do not know them, this is my first post in my life ,
When the problem appears in a device i try to open a main computer that the database found on it i get message need to username and password <just i write administator and blank password then i get access the folder,
i made workgroup by Windows XP and share the folder that contain the database and ADO path connection ,
Per DataMiser: If memory serves some versions of Access will try to open the database for exclusive access by default and if that be the case other apps can not access it while it is opened in this manner.
In the latest view versions of MS Access, one can have the database open, tables can be 'open' (that is viewable in Access), but, the table can not be id DESIGN Mode....it will hiccup every time. Like DM, I don't know if there is a setting to fix that, and like DM, I don't know if that is your issue or not....sounds more plausible that you have the table open in the VB, never closing it, and someone else tries to hit that table when their program start.
In order to solve My Problem , i think i can asked my question as ,i have a workgroup Computers nework Windows Xp how can i have a Shared Folder that Contain my database and All computers Can access it without any problem so all can connect to the Access database ,I made the folder share and Everyone have full control and all Computers Connect to the same workgroup
Need Help
Actually i have about 15 Computers i XP and Win what shall i do to have a shared Folder and everyone can access it Without any problem at the time i see this problem in a device i can not access the folder , Please Help
is there a maximum numbers of computers in workgroup network xp , or in access 2003
Sorry SamoscarBrwon , I do not Know what is DM And i close all of my RS when the user Close his Form , is ADO Support when multi users try to open the same table at the same time , What Should i do , this is the first Multi users Program
'
Thanks very much DEXWERK it is a very clear and Direct Answer ,if i use Windows 7 is that okey?, and if the number of users in Start workday is less than 10 although i received this error
10 is still too many, given the connections may need to time out. Setup a file share on a Linux machine, or use Windows Server 2003 or better.
You can't really expect MS to allow Workstation OS's to handle a file share. (Otherwise MS wouldn't be able to charge more for their Server OSs)
You could also use a generic commercial NAS box. Just avoid the low-end consumer products.
These start new around $300 USD without drives, but there are sometimes decent options in the used market as well. Expect to replace the drives in a used box if they come with it, but normally people pull those out.
These are relatively trivial to administer in a workgroup LAN compared to fussing with a Windows or Linux server. They usually offer a simple web-based administration GUI. People use them with no Linux knowledge at all.
Synology. All my clients have big file servers, but I've got a 7TB Synology NAS box at home and I absolutely love it. I believe mine was in the $600 to $700 range. Nice software RAID and it just works flawlessly, and I love the browser-based interface.
I had an old Windows-Home-Server before that, but it kept giving me problems. Finally, the motherboard fried, and I was actually sort of happy. By plugging in the hard drives to other computers, I was able to recover everything. This Synology box is about a 5th of the footprint of the old WHS too.
EDIT1: I don't even begin to use all the features of it, but I do use the cloud features. For instance, I'm in Houston right now on a job, while my NAS box is in Tennessee (my home). I've already logged into it a couple of times to grab files I have stored out there. It's nice having my own cloud.
Last edited by Elroy; Jan 24th, 2017 at 07:50 PM.
Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.
Thanks very much DEXWERK
i installed Windows Server 2003 as domain Controller With Active Directory and i do not receive this message , but i have one problem when user update record i received an error
<< Can not update Currently Locked >>
this message appears not all the times
Easiest fix - Don't open recordsets with locks, unless you're going to make an update.
And even then, just use a command to make the update.
edit: The _best_ fix would be migrate the Database to SQL Server Express 2008 R2 (SP2).
Use built in windows authentication to grant access to the database - per login, and you've got yourself a solid/reliable/industry standard solution.
Last edited by DEXWERX; Apr 14th, 2017 at 07:02 AM.
Thanks Fast Response From DEXWERK
I use ADO 2.8 (VB6) i open RS like That
rsLines.Open "Lines", CN, adOpenDynamic, adLockOptimistic
can i open Recordset without locktype ? And How ?
sorry if my question is stupid , i am not professional in this issue
Thanks DEXWERK all of your answer helps me much , thanks DataMiser
In Form Load Event i write <<rsLines.Open "Lines", CN, adOpenStatic, adLockReadOnly>>
And in my SaveButton Click Event i use a command to make the update make the update and close
1-is that You mean? And Should i close rs in start of Command Save button because rs is open Readonly as you mention
2 if i use rs.close and rs.open for alot of time will it make a problem for example when some of users responsible of Addnew alot of record in a day ?
Thanks DEXWERK all of your answer helps me much , thanks DataMiser
In Form Load Event i write <<rsLines.Open "Lines", CN, adOpenStatic, adLockReadOnly>>
And in my SaveButton Click Event i use a command to make the update make the update and close
1-is that You mean? Yes
And Should i close rs in start of Command Save button because rs is open Readonly as you mention No
2 if i use rs.close and rs.open for alot of time will it make a problem for example when some of users responsible of Addnew alot of record in a day ? No
Thanks DEXWERK and everyone helped me ,
i Still have error - Can not update Currently Locked at users ,
My Question is
if i am trying to make an update , i undserstand that ,
rshelp.Open "Select My Requested Record", CN, adOpenDynamic, adLockOptimistic and after update i make rsrshelp.close
what is the way when i make addnew what is Select Statement i have to write , i do not have a selected record in case i will addnew , and the users adds alot of data every minutes
You'll need to show some code, and error messages.
At a minimum you need to explain the sequence of events you are trying.
No one can understand what your current issue is.
(which is why no ones bothered answering)
Simple update only query.
Code:
rshelp.Open "SELECT * FROM [MyTable] WHERE 1=0", CN, adOpenDynamic, adLockOptimistic
rshelp.AddNew
rshelp!Field1 = NewValue1
rshelp!Field2 = NewValue2
rshelp.Update
Thanks For your reponse,
in case i trying to Edit a records
rshelp.Open "SELECT * FROM [MyTable] WHERE 1=0", CN, adOpenDynamic, adLockOptimistic
rshelp!Field1 = NewValue1
rshelp!Field2 = NewValue2
rshelp.Update
rshelp.close
as you told me open as command and then close rs to minimize the lifetime of the lock
in case i want to Addnew
Which record i have to select in command , i want to minimize the lifetime of the lock in case Addnew Also
So you're saying the lock is legitimate.
You just can't have 2 users editing the same record at the same time.
You can however work around it, but you have to choose how you want your app to behave.
The simplest / dumbest thing you can do is issue all updates using command objects. The person who edited the record first loses, only the latest update wins out. The record is only locked for the very short time the update is done.
A rough example of using a command object.
Code:
Public Sub InsertRecord(ByVal id As Long, ByVal Noc As Long)
Dim InsertCommand As ADODB.Command
Dim ParamID As ADODB.Parameter
Dim ParamNOC As ADODB.Parameter
Set InsertCommand = New ADODB.Command
With InsertCommand
Set .ActiveConnection = CN
.CommandText = "INSERT INTO [_NEW_IMPORT] ([_ID],[noc_id]) VALUES (?,?)"
.CommandType = adCmdText
Set ParamID = .CreateParameter(, adInteger, adParamInput): .Parameters.Append ParamID
Set ParamNOC = .CreateParameter(, adInteger, adParamInput): .Parameters.Append ParamNOC
End With
ParamID = id
ParamNOC = Noc
InsertCommand.Execute , , adExecuteNoRecords
End Sub
using this design:
Lets say I "open" a record to edit. The values are grabbed readonly without a lock.
Then you "open" a record to edit. Make your changes to the Comments Field. Save.
Then I update a quantity field. Click Save.
Your changes are now gone, like they never happened.
How would you want to resolve this? What if we edit the same field? How do you want to resolve that?
Last edited by DEXWERX; Nov 21st, 2017 at 01:31 PM.
The reason i am sad is
i have about 30 users and the error message that appears at sometime << Can not Update Currently Locked >>
, it is appears for all users Whatever the record any of user trying to Update ,
so i gusses that the problem at the user addnew my code Actually is
rsCustomer.Open "Customer", CN, adOpenDynamic, adLockOptimistic
rsCustomer.Addnew
rsCustomer("name")=txtname.text
rsCustomer("add")=txtadd.text
rsCustomer.update
and i dot not make rsCuromer.close , i am afraid to close recordset because user add a lot of record in short time
I'm shooting in the dark here, as I have not carefully read every post to this thread.
However, I do substantial programming around the DAO, and some with the ADO. And I see that you're using the ADO, which is what I have less experience.
I can tell you that, with the DAO, there is some forward-caching technology that can occasionally give you problems, and report errors similar to what you're suggesting. I use properties of the DBEngine object of the DAO to turn this forward-caching off, and then all works well. I'll post the code I use to do this, but I'm virtually certain that it won't work the same for the ADO. But here it is:
Code:
Public Sub SetDaoRegistryOverrideOptions()
' The following makes sure that the DAO.DBEngine properties are set correctly.
DAO.DBEngine.SetOption dbExclusiveAsyncDelay, 2000
DAO.DBEngine.SetOption dbSharedAsyncDelay, 0
DAO.DBEngine.SetOption dbFlushTransactionTimeout, 500
'
DAO.DBEngine.SetOption dbUserCommitSync, "yes"
DAO.DBEngine.SetOption dbImplicitCommitSync, "yes"
DAO.DBEngine.SetOption dbLockRetry, 20
DAO.DBEngine.SetOption dbPageTimeout, 5000
DAO.DBEngine.SetOption dbMaxLocksPerFile, 9500
DAO.DBEngine.SetOption dbLockDelay, 100
DAO.DBEngine.SetOption dbRecycleLVs, 0
End Sub
Maybe there's something similar in the ADO that someone else can help you with. All I can say is, using the DAO, that code solves a problem very similar to what you describe.
Good Luck,
Elroy
Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
The WHOLE point of the SQL i posted is so you don't open and lock the whole table! you must have missed it
notice the WHERE 1=0 which returns no records, but allows you to Add new records to the table.)
Jet 4.0 also supports row-level locking instead of the default page-level locking which can get a lot more collisions. I'm not sure you have access to that in DAO but ADO supports it via the OLD DB Provider. if your updates touch multiple tables you'd probably want to use transactions as well.
Thanks All For Help,
DEXWERX Your Last Post is So good , You Actually understand my Problem well - and What I need to Ask,
Your Reply is very small but it is so Great
i tried this code in my Pc it successed, i will go to Company to try this for all users
Thanksssssssssssssssssssssssssssss
THanks Everyone Helped me
Thanks DEXWEX ,
I have new Problem
Now I used SQL Server 2012 Out of the company at Datacenter
I use VB6 , And ADO 2.8
the Problem i found a record in middle of table Changed From it string Data To Null , My Application do not make this absolutely
this is take place 1 or 2 time in month not every time
---Notice
may be my Code make Problem
when i want to Update Customer Table
rsCustomer.Open "Select Customers.*,Qesmnam,Edaranam,RepUser,RepUser2 From Customers inner join AQsam on Customers.QesmID=AQsam.QesmID Where IDCust=" & CurrentIDCus, CN, adOpenDynamic, adLockOptimistic
You may care to use ACTION UPDATE instead. Which does not involve you retrieving a recordset.
I raised it in an earlier thread - http://www.vbforums.com/showthread.p...is-too-Complex
My post was number 15
And the OP (Original Poster) asked for an example, which I supplied in post 20 (I am attaching here as well)
I only use Access Data Base
You, and others, will note that I got abused in that thread for proposing that approach, so I will be interested in other members comments.
Rob
PS In case any one thinks I am the only person in the world using this, here is a link describing the UPDATE statement (that does not use a recordset) - https://www.w3schools.com/sql/sql_update.asp
I don't get a lot of hits when I google this. Perhaps my terminology is incorrect ?
What do other members call ACTION UPDATES ?
Last edited by Bobbles; Sep 5th, 2018 at 08:42 PM.
Reason: provide additional link
Thanks Everyone helped me
I hope make something
how can i make vb6 make 2 operation as the same time
like that
when i read alotof data from SQL Server my Form is hanging for some time can i make my form at this time is available to user
and do other opeartion as user want while reading data from database
Thanks Everyone helped me
I hope make something
how can i make vb6 make 2 operation as the same time
like that
when i read alotof data from SQL Server my Form is hanging for some time can i make my form at this time is available to user
and do other opeartion as user want while reading data from database
Thanks Everyone helped me
I hope make something
how can i make vb6 make 2 operation as the same time
like that
when i read alotof data from SQL Server my Form is hanging for some time can i make my form at this time is available to user
and do other opeartion as user want while reading data from database
I'm not an expert by any means but maybe what you want is a background worket. This is something a member here put together (jmcilhinny)