Solved

Microsoft Access Table Locked error

Posted on 2014-09-25
7
334 Views
Last Modified: 2014-09-25
I just came back to the experts as I cannot find a solution to this problem. It should be simple but not for me.
I have a fairly simple database and my vbs code which worked in the past is locked out of this table.
I am just trying to edit two fields in the table, a date field and a distance field which is long integer.
In frustration I made a completely new database with just this one table and created the table as new and it still cannot work. I tried two different methods, as can be seen in the code and get the error which I have also shown in the code. Nothing is changed in the table.

Private Sub NewService_MouseDown(Button As Integer, Shift As Integer, x As Single, Y As Single)
Dim b As Integer, d As Integer, DummyDate As Integer
Dim NewData As String, bilum As String
Dim db As Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Dim myBestShot As Date
Set rs = db.OpenRecordset("BReq")

b = 16
    NewData = "Check" & CStr(b)
    If Form_Maintenance.Controls(NewData).Value = -1 Then
        bilum = "Label" & CStr(b)
        rs.Edit
        DummyDate = DLookup("TimeReq", "BReq", "ReqID =" & b)
        myBestShot = DateAdd("d", DummyDate, Me.DateReq)
       
        On Error GoTo handler
        CurrentDb.Execute ("UPDATE BReq SET DueDate = " & myBestShot _
        & " WHERE ReqID =" & b), dbFailOnError
        Exit Sub
handler:
        MsgBox Err & " " & Err.Description
        ' Error here is 3218 Could not update; currently locked.
       
        rs.Fields("DueDate") = DateAdd("d", DummyDate, Me.DateReq)
        rs!DueDate = DateAdd("d", DummyDate, Me.DateReq)
        'processes these lines but changes nothing in the table
        d = DLookup("DistanceReq", "BReq", "ReqID =" & b)
        rs.Fields("DueOdom") = Me.CurrOdo + d
        rs.Update
    End If
rs.Close
db.Close
End Sub
Corolla-4.accdb
0
Comment
Question by:bravotango
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 84
ID: 40343499
Why are you doing this with unbound forms? It looks like a very simple matter, and therefore I can't see any reason to use unbound forms for this.

That said:

You can't issue an UPDATE statement unless you've set the Recordset to an actual Record ... you're just using BReq as your recordset, which means there is no current record ...

If you really, really must do this, then when you need to add a new record you use INSERT syntax:

Currentdb.Execute "INSERT INTO BReq(DueDate) VALUES(#" & myBestShow & "#)"

In fact, you don't need all that code, for the most part. I'd think you could do it with this:

Dim DummyDate As Date
Dim myBestShot As Date
Dim b As Integer
b = 16
DummyDate = DLookup("TimeReq", "BReq", "ReqID =" & b)
myBestShot = DateAdd("d", DummyDate, Me.DateReq)
Currentdb.Execute "INSERT INTO BReq(DueDate) VALUES(#" & myBestShow & "#)"
       
There are several other things wrong with your code, but I'm assuming you've copied it over from the other db and made changes to accommodate this structure.
0
 

Author Comment

by:bravotango
ID: 40343629
Hi Scott and thanks for your suggestions. Firstly, the reason I am using unbound form is that the user has to interact with two tables and as a result it seemed simpler to do all the coding in vba to accommodate this.

I did have this completed and working once before and then lost these files when I had to upgrade to a new hard drive (no backups!!!).

There is only 24 records in the database and I only need to edit selected fields of random records.

I thought the rs.Edit and corresponding lines might have done the job but it proceeds without altering anything.

Adding new records is not really a solution.
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 350 total points
ID: 40343654
I didn't say you needed to ADD records, but your question was unclear, and I was just covering all bases ...


If you want to update the record, then all you need is this:

Dim DummyDate As Date
Dim myBestShot As Date
Dim b As Integer
b = 16
DummyDate = DLookup("TimeReq", "BReq", "ReqID =" & b)
myBestShot = DateAdd("d", DummyDate, Me.DateReq)
CurrentDb.Execute ("UPDATE BReq SET DueDate = " & myBestShot _
        & " WHERE ReqID =" & b), dbFailOnError

You do NOT need to open a Recordset to do this - the EXECUTE statement will update the data directly.

However, given the code structure you've got, I cannot determine exactly what you want to do. Looks like you try to run code AFTER your error, which is generally not a good idea. In most cases, your "rs" variable would be out of scope or no longer available, so you'd have other failures as well.

If you could give a clear idea of what you're trying to do, perhaps we could restructure the code to perform correctly.
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:bravotango
ID: 40343759
You are correct Scott. I was adding Recordset to see if that might work but cutting all that extra baggage out and now it works and there is no lock out error. However the result is strange, I get a new date like 30/12/1899 instead of expected 15/03/2015 so that is a new problem.

I like the idea of using a bound form better now and using queries to update the table seems a better option as this is just a fairly simple vehicle maintenance table.
0
 
LVL 84
ID: 40343851
I like the idea of using a bound form better now and using queries to update the table seems a better option as this is just a fairly simple vehicle maintenance table.
If you are going to switch to using BOUND forms, then you should not use queries to update the tables. Let Access handle that - it does that much better than you can :)

If you're going to continue using UNBOUND forms, then of course you'll have to handle the data using code and SQL.
0
 
LVL 37

Assisted Solution

by:PatHartman
PatHartman earned 150 total points
ID: 40343934
user has to interact with two tables
Then you have two choices.  Use which ever one makes the most sense for the data:
1. Join the two tables in a query and bind the form to the query rather than to a table.
2. Use a form for the "parent" table/query and a subform for the "child" table/query.
0
 

Author Closing Comment

by:bravotango
ID: 40345181
Thanks for all your help. Will definitely return here for all my problems.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS Access query 16 61
Queries: Select, then Append, then Delete 8 41
Combo box with two columns question 7 40
Using a combo box to search a form. 3 36
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question