Page 1 of 2 12 LastLast
Results 1 to 40 of 58

Thread: Problem in my Application

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jan 2017
    Posts
    24

    Problem in my Application

    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

  2. #2
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Problem in my Application

    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.
    Please remember next time...elections matter!

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Jan 2017
    Posts
    24

    Re: Problem in my Application

    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

  4. #4
    Frenzied Member
    Join Date
    Dec 2008
    Location
    Melbourne Australia
    Posts
    1,487

    Re: Problem in my 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

  5. #5
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Problem in my Application

    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?

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Jan 2017
    Posts
    24

    Re: Problem in my Application

    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 ,

  7. #7
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: Problem in my Application

    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.

    Sammi

  8. #8
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Problem in my Application

    It could also be that since you are using a networked folder that the user on the pc in question does not have write access to that location.

  9. #9

    Thread Starter
    Junior Member
    Join Date
    Jan 2017
    Posts
    24

    Re: Problem in my Application

    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
    Last edited by Hares; Jan 15th, 2017 at 05:01 PM.

  10. #10

    Thread Starter
    Junior Member
    Join Date
    Jan 2017
    Posts
    24

    Re: Problem in my Application

    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

  11. #11
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: Problem in my Application

    You haven't answered the many questions posted to you...if you don't answer the questions, don't expect help simply by repeating your 'problem'..

  12. #12
    PowerPoster
    Join Date
    Jun 2015
    Posts
    2,224

    Re: Problem in my Application

    15 computers are trying to access a shared folder on an XP computer, using a simple work group.

    10 is the max. You need to use an actual server if you want to share to more than 10 people.

    http://superuser.com/questions/18129...-on-windows-xp
    Last edited by DEXWERX; Jan 23rd, 2017 at 08:04 AM.

  13. #13

    Thread Starter
    Junior Member
    Join Date
    Jan 2017
    Posts
    24

    Re: Problem in my Application

    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

  14. #14
    PowerPoster
    Join Date
    Jun 2015
    Posts
    2,224

    Re: Problem in my Application

    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)

  15. #15
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: Problem in my Application

    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.

  16. #16
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: Problem in my Application

    Quote Originally Posted by dilettante View Post
    You could also use a generic commercial NAS box.
    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.

  17. #17

    Thread Starter
    Junior Member
    Join Date
    Jan 2017
    Posts
    24

    Re: Problem in my Application

    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

  18. #18
    PowerPoster
    Join Date
    Jun 2015
    Posts
    2,224

    Re: Problem in my Application

    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.

  19. #19

    Thread Starter
    Junior Member
    Join Date
    Jan 2017
    Posts
    24

    Re: Problem in my Application

    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
    Last edited by Hares; Apr 16th, 2017 at 01:07 PM.

  20. #20
    PowerPoster
    Join Date
    Jun 2015
    Posts
    2,224

    Re: Problem in my Application

    This will open a read only recordset.
    Code:
    rsLines.Open "Lines", CN, adOpenStatic, adLockReadOnly
    Then use a command to make the update.
    Or open the specific record, make the update and close it to minimize the lifetime of the lock.

    Code:
    rsLines.Open "SELECT * FROM Lines WHERE ID=" & ID, CN, adOpenDynamic, adLockOptimistic
    If rsLines.RecordCount Then
        rsLines.Length = txtLength
        rsLines!Resistance = txtResistance
        rsLines!Reactance = txtReactance
        rsLines!Conductance = txtConductance
        rsLines!Susceptance = txtSusceptance
        rsLines.Update
        ' rsLines.Close
    End If
    rsLines.Close ' <-- DataMiser's Fix
    Last edited by DEXWERX; Apr 18th, 2017 at 08:33 AM.

  21. #21
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Problem in my Application

    I would suggest to move the rs.close down below the end if so it closes the recordset even if the id is not found.

  22. #22

    Thread Starter
    Junior Member
    Join Date
    Jan 2017
    Posts
    24

    Re: Problem in my Application

    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 ?

  23. #23
    PowerPoster
    Join Date
    Jun 2015
    Posts
    2,224

    Re: Problem in my Application

    Quote Originally Posted by Hares View Post
    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
    good luck

  24. #24

    Thread Starter
    Junior Member
    Join Date
    Jan 2017
    Posts
    24

    Re: Problem in my Application

    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

    Please Help
    Last edited by Hares; Nov 21st, 2017 at 10:59 AM.

  25. #25
    PowerPoster
    Join Date
    Jun 2015
    Posts
    2,224

    Re: Problem in my Application

    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

  26. #26

    Thread Starter
    Junior Member
    Join Date
    Jan 2017
    Posts
    24

    Re: Problem in my Application

    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

  27. #27
    PowerPoster
    Join Date
    Jun 2015
    Posts
    2,224

    Re: Problem in my Application

    go head and close the recordset then too?
    Code:
    rshelp.Open "SELECT * FROM [MyTable] WHERE 1=0", CN, adOpenDynamic, adLockOptimistic
    rshelp.AddNew
    rshelp!Field1 = NewValue1
    rshelp!Field2 = NewValue2
    rshelp.Update
    rshelp.Close
    If you want no locks on creation i said to use a Command. Is that the issue? You don't know how to setup a Command?

    Do you have have several users attempting to edit the same record at the same time?
    Last edited by DEXWERX; Nov 21st, 2017 at 12:44 PM.

  28. #28

    Thread Starter
    Junior Member
    Join Date
    Jan 2017
    Posts
    24

    Re: Problem in my Application

    Yes i have a several users to edit the same record at sometime not alot of
    ,My error at uses appears << Can not Update Currently Locked>>

    Why You Put a Condition at Case AddNew , in Case Add there is no a requested record , just i want to understand

  29. #29
    PowerPoster
    Join Date
    Jun 2015
    Posts
    2,224

    Re: Problem in my Application

    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.

  30. #30

    Thread Starter
    Junior Member
    Join Date
    Jan 2017
    Posts
    24

    Re: Problem in my Application

    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

  31. #31
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: Problem in my Application

    Hares,

    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.

  32. #32
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,045

    Re: Problem in my Application

    Hi,

    try this, see change in blue
    Code:
    rsCustomer.Open "Customer", CN, adOpenKeySet, adLockOptimistic
    also look at CursorLocation https://msdn.microsoft.com/en-us/lib...v=bts.10).aspx

    regards
    Chris
    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.

  33. #33
    PowerPoster
    Join Date
    Jun 2015
    Posts
    2,224

    Re: Problem in my Application

    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.)

    Code:
    SELECT * FROM Customer WHERE 1=0

  34. #34
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: Problem in my Application

    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.

  35. #35

    Thread Starter
    Junior Member
    Join Date
    Jan 2017
    Posts
    24

    Re: Problem in my Application

    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


    Woooooooooooooooooooooooooow

  36. #36

    Thread Starter
    Junior Member
    Join Date
    Jan 2017
    Posts
    24

    Re: Problem in my Application

    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

    rsCustomer("nam")=text1.text
    rsCustomer.update
    rsCustomer.close

    i made join with other AQsam Table Because i need some data From it to me
    Last edited by Hares; Sep 5th, 2018 at 12:41 PM.

  37. #37
    Frenzied Member
    Join Date
    Dec 2008
    Location
    Melbourne Australia
    Posts
    1,487

    Re: Problem in my Application

    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 ?
    Attached Files Attached Files
    Last edited by Bobbles; Sep 5th, 2018 at 08:42 PM. Reason: provide additional link

  38. #38

    Thread Starter
    Junior Member
    Join Date
    Jan 2017
    Posts
    24

    Re: Problem in my Application

    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

  39. #39

    Thread Starter
    Junior Member
    Join Date
    Jan 2017
    Posts
    24

    Re: Problem in my Application

    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

  40. #40
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Problem in my Application

    Quote Originally Posted by Hares View Post
    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)

    http://www.vbforums.com/showthread.p...ckgroundWorker
    Please remember next time...elections matter!

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width