Runtime Crash on DELETE * FROM

[CharlesWhiteman]

I'm using the following critical piece of code but on the deployed version which uses Access Runtime (Access 2007) the application halts.

Any advise on why or alternative method to achieve what the code does? Thanks.

Private Sub cmdUpdateIPAddress_Click()
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM TblRadAcctLocal;"
DoCmd.OpenQuery "QryUpdateRadAcctTORadAcctLocal"
Me.CurrentIPAddress = DLookup("FramedIPAddress", "QryRadAcctCurrentIPAddress", "PrimaryDataID = Forms.FrmPrimaryData.PrimaryDataID")
Me.txtCurrentIPAddress.Requery
DoCmd.SetWarnings True
'Current IP Address Test

 

[DCrake]

Is it actually crashing or just not responding?

As an alternative you could use the Recordset approach to delete the contents.

Dim Rs As DAO.Recordset
Set Rs = CurrentDb.Openrecordset("Table")

Do until Rs.EOF
  Rs.Delete
  Rs.MoveNext
Loop
Rs.Close
Set Rs = Nothing

Also how many records are being deleted from the table?

 

[JamesMcS]

Is there anything glaring in the event log?

 

[CharlesWhiteman]

Hi James and many thanks for the reply. It's deleting about 20 records but that will increase over time.

It is stopping on a HALT which is basically the runtime being incapable of handling a delete confirmation.

I'll certainly look at the recordset approach. We live and learn!

 

[CharlesWhiteman]

Hi again, well that worked fine but now i think runtime is halting due to a runtime error when the code executes a query (it's a append query - again critical as it updates my local table with the latest data required from a linked table.

'Delete The Existing Records From TblRadAcctLocal
Dim Rs As DAO.Recordset
Set Rs = CurrentDb.OpenRecordset("TblRadAcctLocal")
Do Until Rs.EOF
Rs.Delete
Rs.MoveNext
Loop
Rs.Close
Set Rs = Nothing
'UpdateTblRadAcctLocal With Latest Data From The Billing System
DoCmd.OpenQuery "QryUpdateRadAcctTORadAcctLocal"
Me.CurrentIPAddress = DLookup("FramedIPAddress", "QryRadAcctCurrentIPAddress", "PrimaryDataID = Forms.FrmPrimaryData.PrimaryDataID")
Me.txtCurrentIPAddress.Requery

 

[CharlesWhiteman]

In answer to your previous post, there's nothing in the windows event logs. The code owrks perfectly in the full MS Access, I have come across these halts before where Access Runtime cant handle runtime errors.

 

[CharlesWhiteman]

I've just tested the code on a button in the runtime version just on the docmd.openquery and it is that thats causing the error.

 

[JamesMcS]

Does the query work when you run it on its own?

 

[JamesMcS]

Oh, runtime only - you might find there's a missing library or something. The RTE number might point us in the right direction.....

 

[CharlesWhiteman]

No the query doesnt work on its own. I copies that line into a test cmd button and tested that and it gave a append warning and then the runtime version couldnt handle it.

Can the same thing be done with dao code?

 

[JamesMcS]

Good question - probably but I couldn't say how!

I'm thinking that, if it works in full Access but not runtime, there's a library missing - but on the other hand it's just an append query, not brain surgery.....

 

[CharlesWhiteman]

Right, I'm testing the following code but getting a runtime error 3625 item not found in this collection. Debugging highlights rs2!UserName = rs1!MACAddress

Dim db As DAO.Database
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Set db = CurrentDb
    Set rs1 = db.OpenRecordset("RadAcct")
    Set rs2 = db.OpenRecordset("TblRadAcctLocal")
    
    rs2.AddNew                     '<-----add a record structure
       rs2!RadAcctID = rs1!RadAcctID
       [COLOR=red][B]rs2!UserName = rs1!MACAddress[/B][/COLOR]
       rs2!FramedIPAddress = rs1!FramedIPAddress
       rs2.Update                      '<----save/commit the record to the table
       
rs1.Close
rs2.Close

 

[CharlesWhiteman]

i found the answer! the names were the wrong way round but its not a full solution as its not doing the same as my QBE query - On the right track though... I i think

this is the aql of my QBE query..

INSERT INTO TblRadAcctLocal ( RadAcctId, MacAddress, FramedIPAddress )
SELECT Max(radacct.RadAcctId) AS MaxOfRadAcctId, TblRads.MacAddress, radacct.FramedIPAddress
FROM TblRads INNER JOIN radacct ON TblRads.MacAddress = radacct.UserName
GROUP BY TblRads.MacAddress, radacct.FramedIPAddress;