A family of Microsoft relational database management systems designed for ease of use.
Access crashing/auto quit
With my knowledge,Access will stop and highlight error line rather than crashing/quit if some codes occur error.
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
I am using the below code to append query by VBA method.
It appends one record and crash/quits the access.
Dont understand, if my code is wrong, then it will not work, BUT how come it appends one records properly and then quits.
Please check my code and advise what correction can be done.
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim sqlstr As String
sqlstr = "SELECT TableMasterLocal.* FROM TableMasterLocal LEFT JOIN TableMasterDV ON TableMasterLocal.SerialNo = TableMasterDV.SerialNo WHERE TableMasterDV.SerialNo Is Null;"
Set db = CurrentDb()
Set rs1 = db.OpenRecordset("TableMasterDV")
Set rs2 = db.OpenRecordset(sqlstr)
rs2.MoveFirst
Do Until rs2.EOF
rs1.AddNew
rs1!RecoNo = Nz(DMax("[RecoNo]", "[TableMasterDV]"), 0) + 1
rs1!Model = rs2!Model
rs1!SerialNo = rs2!SerialNo
rs1!SMR = rs2!SMR
rs1!WorkingHrs = rs2!WorkingHrs
rs1!ActWorkHrs = rs2!ActWorkHrs
rs1!TotalFuel = rs2!TotalFuel
rs1!FuelPerHr = rs2!FuelPerHr
rs1!Cautions = rs2!Cautions
rs1.Update
rs2.MoveNext
Loop
rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing
A family of Microsoft relational database management systems designed for ease of use.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Access crashing/auto quit
With my knowledge,Access will stop and highlight error line rather than crashing/quit if some codes occur error.
It is virtually impossible to write VBA code to make Access crash.
So when a crash occurs, we have to look elsewhere:
Thanking you and Mr. Tom for your advise.
Sorry, I could have mentioned more details about the situation.
Was trying to append data from Ms Access to MS Team Table Dataverse.
During Test, It was working fine as I was testing with one row upload only.
But when the actual routine work came up to append the data where 23 rows, then noticed that after upload one row, it crashed, Got confused, thought my VBA process can be an issue, so was looking for an alternative.
After investing and google found the situation can happen if the previous record is not updated/refreshed withing 30 second, the side effect can be crash (auto quit the access without proper closing) or of hang/freeze of access.
Solution / An idea coming to my mind - to complete this, need your advise :
Code to check the status - I mean if the record is created successfully then only the loop should go to add another record, till then it should wait.
Option can be that before starting the loop, it should count the records available,
Then if the old count+1 is obtained then only it should go for another loop else to wait for 10 sec
And try again.
(I mean use of DoEvent or Sleep or Wait - how to join the codes together is challenging for me)
BUT getting confuse, how to code it.
This may take time, no issue, but job to be done is more important.
User can be informed that It will take time need to wait, As this is monthly routine work and maximum is around 150 records getting added.
Please advise
I am trying to write the below code, to confirm that the record is added.
Then only to go for next record.
But confused, what function is the correct in my situation
Sleep OR Wait OR DoEvent
Please check by below code and advise, if I ok going or need to modify my code to suit better
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim sqlstr As String
Dim CountMyReco As Integer
Dim HoldCountReco As Integer
Dim LCounter As Integer
CountMyReco = DCount("RecoNo", TableMasterDV
HoldCountReco = DCount("RecoNo", TableMasterDV)
sqlstr = "SELECT TableMasterLocal.* FROM TableMasterLocal LEFT JOIN TableMasterDV ON TableMasterLocal.SerialNo = TableMasterDV.SerialNo WHERE TableMasterDV.SerialNo Is Null;"
Set db = CurrentDb()
Set rs1 = db.OpenRecordset("TableMasterDV")
Set rs2 = db.OpenRecordset(sqlstr)
rs2.MoveFirst
Do Until rs2.EOF
rs1.AddNew
rs1!RecoNo = Nz(DMax("[RecoNo]", "[TableMasterDV]"), 0) + 1
rs1!Model = rs2!Model
rs1!SerialNo = rs2!SerialNo
rs1!SMR = rs2!SMR
rs1!WorkingHrs = rs2!WorkingHrs
rs1!ActWorkHrs = rs2!ActWorkHrs
rs1!TotalFuel = rs2!TotalFuel
rs1!FuelPerHr = rs2!FuelPerHr
rs1!Cautions = rs2!Cautions
rs1.Update
For LCounter = 1 To 8
If CountMyReco = HoldCountReco Then
Exit For
Else
Sleep (10000) 'confused what will be correct - Sleep OR Wait OR DoEvent
End If
Next LCounter
rs2.MoveNext
Loop
rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing
This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Comments have been turned off. Learn more