Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access/SQL Server (A2K/SQL Server 2000)

    Hello all,
    I need someone with some SQL Server expertise!

    I have an Access FE that I am using to read and write records to a SQL DB using a system DSN. I can create a record just fine but then it won't let me append the record. It gives me a write conflict error, like someone else is editing the record. I am the only one using the DB right now. I have noticed that if I open the table directly and create a record, all is fine. Looking in the table at the records I created with a form, I am unable to edit the record without the write conflict. This probably sounds confusing! Could it be that in the code to write the record, on my form, I am not closing the record properly? Here is an example of some code that creates a record.

    Dim intIncID As String
    Dim db1 As Database, db2 As Database
    Dim rstNewInc As Recordset, strCust As String, rstEquipStatus As Recordset
    Dim strNewIncQry As String, strEquipment As String
    Dim strNotes As String, strNewNotes As String, strTimeLog As String

    Set db1 = CurrentDb
    Set rstNewInc = db1.OpenRecordset("Incidents")
    rstNewInc.AddNew
    rstNewInc!Incident_Customer_ID = Me.cboCustName.Value
    '..........(This is where I have a bunch more field data being added, I excluded it for the sake of limiting
    'the size of this post!)
    Update:
    Me.Refresh
    rstNewInc.Update
    rstNewInc.MoveLast

    Any ideas?

    Thanks,
    Mark Santos

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access/SQL Server (A2K/SQL Server 2000)

    You should probably do the following after your Update and MoveLast:
    <font color=blue>
    rstNewInc.Close
    set rstNewInc=Nothing
    </font color=blue>
    It also probably isn't necessary to do the MoveLast as the cursor should be on the last record after the Update.
    Wendell

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access/SQL Server (A2K/SQL Server 2000)

    Thanks Wendell,
    the set rstNewInc = Nothing comes a few lines later. I tried the close method earlier but that didn't seem to help. Any other ideas?

    Thanks,
    Mark

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Access/SQL Server (A2K/SQL Server 2000)

    Just curious, but why do you have the Me.Requery command just prior to the .Update command?
    Or for that matter, why do you have it there at all?
    Pat

  5. #5
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access/SQL Server (A2K/SQL Server 2000)

    Hi Pat,
    Its a refresh not a requery.

    Thanks,
    Mark

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Access/SQL Server (A2K/SQL Server 2000)

    Mark,
    Back to the question.
    Why is the refresh there? Could this be causing a problem?
    Pat

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access/SQL Server (A2K/SQL Server 2000)

    I think Pat may have spotted the problem however. The Refresh should come after you have saved the record (which is what the Update does) - in fact, this form is presumably unbound, so the Refresh would seem unnecessary. As an aside, I presume you do not have a reference set to ADO in your front-end. If you do you may well see all sorts of bizarre behavior unless you explicitly define this as DAO code.
    Wendell

  8. #8
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access/SQL Server (A2K/SQL Server 2000)

    Hi Wendell and Pat,
    Thanks for the help. I will remove the refresh. There are no references to ADO in this FE file. It has been DAO since it was created. A question: Do you need to declare the connection as DAO? I can use the FE file to open the table, create a manual entry and that one works fine. However, the record created with my form still remains in this locked state even after closing and reopening the DB.

    Thanks,
    Mark Santos

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access/SQL Server (A2K/SQL Server 2000)

    Hi Mark,
    As long as you don't have a reference set to ADO and you do have a reference set to DAO (3.6 presumably) then you don't need to declare code explicity as DAO. I am assuming that you have an ODBC connection set to the SQL Server database and that the table involved is a linked table - if not then we need to know more.

    The fact that the record created by your form suggests that the Update is not being completed properly. It almost sounds like you have a thread of Access still running if you can close and then reopen the database and the record is still locked. If you actually exit Access, restart your computer and then try to access the record, do you still get the same behavior?
    Wendell

  10. #10
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access/SQL Server (A2K/SQL Server 2000)

    Hi Wendell,
    It is an ODBC (System DSN) linked table. I am actually running the app from a citrix server right now. The ldb file disappears when I close the app so I would think that it is braking the link properly. I will have the IT dept. re-boot the server.

    Thanks,
    Mark

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Access/SQL Server (A2K/SQL Server 2000)

    Do you need to set db1 to nothing?

  12. #12
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access/SQL Server (A2K/SQL Server 2000)

    Hi Pat,
    I do that a few lines later in the code. This whole thing seems to be that the form (and Code) is not releasing the lock on the record. The puzzling part is that I can close the DB and re-open it. Those records that I created still seem to be locked. I can open the table with the front end and create a record and it will be unlocked after completion. Any ideas?

    Thanks,
    Mark

  13. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Access/SQL Server (A2K/SQL Server 2000)

    I'm no expert when it comes to Sql Server, in fact I've had little experience with it.
    Maybe Wendell will come back and help you solve it.

  14. #14
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access/SQL Server (A2K/SQL Server 2000)

    Well, this seems to be a puzzle. The Citrix server may present some complications as you have a layer between you and SQL Server that you can't really see. The .ldb file disappearing may not be a reliable measure of what's happening however, as it really only controls locking for jet-based (.mdb) files. SQL Server does its own thing in that regard, based on ODBC commands from the Jet engine. I would be inclined to suspect a problem with the MDAC on the Citrix server, or suggest you upgrade to the latest SP6 for Jet 4.0, but the fact you can add a record manually at the table level really suggests your code is probably the culprit. Why don't you try simplifying your code to the point where all you do is set the fields that are required by SQL Server and try saving the record. If that doesn't work, then we have a real mystery.

    BTW, I meant to ask what you meant by locking earlier. Are you not able to read the new record, or is the table completely locked so that you can't read other records? SQL normally does record level locking, so you should only be locked out of the new record, and you should be able to add a new record manually.
    Wendell

  15. #15
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access/SQL Server (A2K/SQL Server 2000)

    Actually, it's a good idea to *avoid* bit datatypes in SQL Server. They do seem to cause weird problems.
    Charlotte

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
  •