The DoCmd that doesn't end 3

[jossimon]

I have the following code:

DoCmd.RunSQL "Delete * FROM TTempOrderImp WHERE (((TTempOrderImp.RprtQty)=0 Or (TTempOrderImp.RprtQty)<0));"

This code runs fine, but when I want to run a different procedure with the table TTempOrderImp I get a bug that states, "The database engine could not lock table 'TTempOrderImp' because it is already in use by another person or process." I have to then exit the Database in order to get the procedure to work again or to "unlock" the database. Is there code I can type in after this delete so that it unlocks the table?

 

[Tranman]

jossimon,
This may not have anything to do with your problem, but if I were to write that SQL, I'd leave out the "*", as in:

DoCmd.RunSQL "Delete FROM TTempOrderImp WHERE (((TTempOrderImp.RprtQty)=0 Or (TTempOrderImp.RprtQty)<0));"

Is there a possibility that you have some sort of referential integrity/cascading deletes thing going on where another table is locked, and is causing your first table to remain locked?

Tranman

 

[PHV]

And why not simply this ?
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete FROM TTempOrderImp WHERE RprtQty<=0"
DoCmd.SetWarnings True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jossimon]

Here is the whole subroutine. For some reason the table TTempOrderImp is remaining locked after this subroutine finishes. What can I do to solve this probelm?

Private Sub Crt_ExportFile_Click()

DoCmd.SetWarnings False
DoCmd.OpenQuery "QDelTExportFile", acViewNormal, acEdit
DoCmd.OpenQuery "QMakeTempTOrderImp", acViewNormal, acEdit
DoCmd.OpenForm "F_TOrderImpFilter", acFormDS, , , acFormEdit, acHidden

Start:

Do While Forms!F_TOrderImpFilter.Shippable > 0

DoCmd.RunSQL "Insert into TExportFile (PONumber, Name, ADDR1, ADDR2, City, State, Zip, DC, WMIT, SCC14, SITM) SELECT Top 1 TTempOrderImp.CustomerOrderNumber, TTempOrderImp.Name, TTempOrderImp.Address, TTempOrderImp.None2, TTempOrderImp.City, TTempOrderImp.State, TTempOrderImp.Zip, DC_XRef.DC, TItemXRef.WMIT, TItemXRef.SCC14, TItemXRef.SITM FROM DC_XRef INNER JOIN (TTempOrderImp INNER JOIN TItemXRef ON TTempOrderImp.ItemNumber = TItemXRef.SITM) ON DC_XRef.ADDR = TTempOrderImp.ShipTo WHERE (((TTempOrderImp.Shippable)>0));"
Forms!F_TOrderImpFilter.Shippable = Forms!F_TOrderImpFilter.Shippable - 1
Loop

DoCmd.RunSQL "Delete FROM TTempOrderImp WHERE TTempOrderImp.Shippable<=0;"

If Forms!F_TOrderImpFilter.Shippable = 0 Then
DoCmd.GoToRecord acActiveDataObject, "F_TOrderImpFilter", acNext
GoTo Start
End If

DoCmd.SetWarnings True

End Sub

 

[PHV]

Why are you using a hidden form locking your table instead of a recordset ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jossimon]

Because I don't know what recordsets are or how to use them. The only VBA class I could find isn't until next month and it might be cancelled. If you could explain them to me I would be very appreciative.

 

[PHV]

What are the SQL of QDelTExportFile, QMakeTempTOrderImp and F_TOrderImpFilter.RecordSource ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jossimon]

QDelTExportFile = DELETE * FROM TExportFile;
QMakeTempTOrderImp = SELECT TOrderImpFilter.* INTO TTempOrderImp FROM TOrderImpFilter;
F_TOrderImpFilter.RecordSource = TTempOrderImp;

 

[Tranman]

jossimon,
Without looking really closely at your code, you can probably get it to let go of your table by just closing the form immediately before the "End Sub".

The bigger problem is:
"Because I don't know what recordsets are or how to use them."

They're not really that complicated--only seem that way if you don't have anyone to show you how to get started.

First, what are they. Recordsets are Microsoft's implementation of something that's called a "cursor" in the big boy databases (Oracle, SQL). What they do is they are a tool that allows you to access and manipulate data from an underlying table or query. You can always move forward through the recordset one row at a time. With some recordsets, you can jump around from row to row, or the first row, or the last row, move backward through the recordset, or whatever. If the recordset is based upon a query, the rows are ordered based upon the "order by" clause of the underlying query. If the recordset is based upon a table, you cannot really be sure what order it will be in (just like any other table).

Just like processing a flat file, you can process a whole recordset in a "Do While" loop, using <rsname>.EOF to tell when you're at the end, and <rsname>.Movenext to access the next row in the recordset.

Like a table, you can search a recordset for a given value.

I'm including a little example of a very simple recordset, which illustrates how to open the recordset, move forward through it, how to reference the values in the fields, and how to close the recordset when you're done.

This is not the know all, be all, do all of recordsets. Just a little sample of what they can do. They are an incredibly powerful tool to have in your arsenal, and enable you to do some things that just about can't do any other way. There are zillions of examples of things you can do with recordsets here in Tek-Tips. Just do a search.

You should give them a try. If you do, and have problems, write back and I'll try to give you a hand.

Tranman
(Code follows)

Private Sub RSExample()
Dim rs As New ADODB.Recordset
Dim intCUST_NBR As Integer
Dim strF_NAME As String
Dim strMI As String
Dim strL_NAME As String
Dim datDOB As Date
Dim strStatus As String

Dim mySQL As String

rs.Open "Select * from CUST order by L_NAME", CurrentProject.Connection, adOpenDynamic, adLockOptimistic

rs.MoveFirst

Do While Not rs.EOF
Select Case rs.Fields("strSTATUS")
Case "A" 'Active
intCUST_NBR = rs.Fields("CUST_NBR")
strF_NAME = rs.Fields("F_NAME") 'You can refer to fields by name
strMI = rs.Fields(2) 'You can also refer to them by ordinal column # (0 relative)
strL_NAME = rs.Fields("L_NAME")
datDOB = rs.Fields("DOB")
mySQL = "INSERT INTO CUST_OUT (L_NAME, B_DATE) VALUES('" & strL_NAME & "', #" & datDOB & "#)"
CurrentProject.Connection.Execute mySQL
Case Else
strStatus = "INACTIVE"
rs.Update
End Select

rs.MoveNext
Loop

rs.Close
Set rs = Nothing

End Sub

 

[jossimon]

THANK YOU, THANK YOU, THANK YOU!!! I got it to work. Your example really helped me out and I was able to do it. And its a lot easier this way. I am excited about my training. I hope they don't cancel it!

jossimon