VBA Module "crashing" in DELETE and UPDATE SQL when Database randomly turns READ-ONLY

[jamesdeuce]

I have a module in VBA that opens a spreadsheet, takes a list of numbers in the spreadsheet, puts that list into an array then my module opens Access and via an SQL statement it DELETEs each of the rows that contain a number on that list and it also UPDATEs (changes) from one number to another. It has to iterate through yearly databases. So, the module would take a number from the list in Excel...open a file from 2013 and delete every row that that number is in, then open 2012, ... , through 2005, then take the next number in the list and open 2013, then 2012, ..., 2005 etc through the list. Then it does the same thing takes a number from the list and UPDATEs each row. The databases have ~10,000*365 rows. They are each about 1 GB in size.

Here's my problem...sometimes the module works perfectly and have I no reason to post anything on this forum. And that other times the module decides to crash during the SQL statement. I get runtime Automation Erros. I get DELETE statement problems etc. And I have no idea why it decides to run some time...or crash the next time. What I have learned is that for whatever reason something is causing a database to randomly become READ-ONLY...and since it's Read-Only the module can't DELETE or UPDATE anything.

I am new to programming, most of this was written by someone else. They aren't available to troubleshoot this.

My folder isn't read-only. The "confirm do you want to delete" message isn't stopping the module from working perfectly when it does work so doubtful it's causing it to crash. Perhaps it's how I am interacting between Excel and Access...maybe there is a better way. This to me is a very strange odd problem and I have been banging my head against the wall trying to troubleshoot it. Any recommendations or ideas are greatly appreciated. Thank you. Here is my code...

The DELETE module in Excel...

Dim appAccess As Object
Set appAccess = CreateObject("Access.Application")
                                
For n = 1 To UBound(Year)
               
For k = 1 To UBound(arrDelete)
                        
strDBName = Year(n) & "databasefile.accdb"
                        
appAccess.OpenCurrentDatabase (strPathDB & strDBName)
appAccess.Visible = True
                    
DeleteStatement = "DELETE " & Table & ".*, " & Table & ".numberfromlist FROM" & Table & " WHERE ((" & Table & ".numberfromlist  =" & arrDelete(k) & ")"
                    
appAccess.DoCmd.OpenModule "DoCmdSQL", "PerformSQL"
appAccess.Run "PerformSQL", DeleteStatement
                    
appAccess.CloseCurrentDatabase
'appAccess.Quit
                        
Next k
                    
Next n
                
appAccess.Quit
Workbooks(BusModelFilename).Close

The UPDATE module in Excel...

For n = 1 To UBound(Year)
               
For k = 1 To UBound(arrDelete)
                        
strDBName = Year(n) & "databasefile.accdb"
                        
appAccess.OpenCurrentDatabase (strPathDB & strDBName)
appAccess.Visible = True
                    
ChangeStatement = "UPDATE " & Table & " SET " & Table & ".Pnumberfromlist  " & arrChangeTo(k) & " WHERE ((" & Table & ".numberfromlist =" & arrChangeFrom(k) & ")"
                    
appAccess.DoCmd.OpenModule "DoCmdSQL", "PerformSQL"
appAccess.Run "PerformSQL", DeleteStatement
                    
appAccess.CloseCurrentDatabase
'appAccess.Quit
                        
Next k
                    
Next n
                
appAccess.Quit
Workbooks(BusModelFilename).Close

Then I have a module in SQL called "DoCmdSQL" that the Excel module calls...

Option Compare Database
Option Explicit
Sub PerformSQL(SQL As String)
    DoCmd.RunSQL SQL
End Sub

Perhaps there are much better ways for me to call Access. What is causing Access to be Read-Only in the middle of the process during some runs and not during some runs? Thank you.

[jamesdeuce]

The UPDATE module has a slight error...it's code should perform the CHANGESTATEMENT not DELETE...here is the accurate statement below.

For n = 1 To UBound(Year)
               
For k = 1 To UBound(arrDelete)
                        
strDBName = Year(n) & "databasefile.accdb"
                        
appAccess.OpenCurrentDatabase (strPathDB & strDBName)
appAccess.Visible = True
                    
ChangeStatement = "UPDATE " & Table & " SET " & Table & ".Pnumberfromlist  " & arrChangeTo(k) & " WHERE ((" & Table & ".numberfromlist =" & arrChangeFrom(k) & ")"
                    
appAccess.DoCmd.OpenModule "DoCmdSQL", "PerformSQL"
appAccess.Run "PerformSQL", ChangeStatement
                    
appAccess.CloseCurrentDatabase
'appAccess.Quit
                        
Next k
                    
Next n
                
appAccess.Quit
Workbooks(BusModelFilename).Close

[June7]

Where is the = operator in the SET clause of the UPDATE action?

I assume Pnumberfromlist and numberfromlist fields are number type.

DELETE action doesn't need to select field names because records are deleted, not fields. Example:

DELETE FROM tablename WHERE somefield=somevalue;

Why is code behind Excel and not in Access? You open Access which opens Excel and somehow trigger code in Excel to open other Access files? Why is Excel even involved?

[jamesdeuce]

I am sorry...I don't understand what you are asking? The SQL DELETE and UPDATE statements don't have problems. They run perfectly. Unless you think the DELETE and UPDATE statements could randomly cause a database to be read-only.

Here is my UPDATE statement

ChangeStatement = "UPDATE " & Table & " SET " & Table & ".NumberFromList = " & arrChangeTo(k) & " WHERE ((" & Table & ".NumberFromList)=" & arrChangeFrom(k) & ")"

My previous post was missing the equal sign. I think that's what you were asking.

[June7]

Yes, I was confused by lack of equal sign.

The DELETE action won't fail if field is selected, it's just unnecessary to do so.

I can't really see anything wrong with code so don't know why you experience this behavior.

Have you step debugged?

Can you eliminate Excel from the procedure or instead of running code behind Excel run it in Access? Access could link to the Excel if you must use Excel as source of the numbers.

[jamesdeuce]

The problem could be in some Access setting. Or how I open and close databases. Or maybe each giant database needs time before you open it again or needs time before you ask it to delete or update again. Or maybe there are problems in my appAccess statements. Maybe my way of using Excel to call Access via the "doCmd.OpenModule" and the "Run" statements are wrong or have errors. Perhaps there is just a much better way for me to connect Excel and Access that I am completely unaware of. Why would a database run fine and then decide it wants to be read-only? This is running at home on my own computer. I am the only user. The only process hitting these databases are these DELETE and UPDATE statements. Since I posted on this forum I got it to work perfectly (without crashing and without none of the databases turning read only on me) for 5 times in a row (of course...ask a professional for help and it always decides to work when they show up)...but it's bound to fail again. So why does it work great 5 times in a row...then maybe on my 6th run Access may decide to turn a database read only on me and then the module will crash?

[June7]

I was posting at same time you posted. Did you see my previous post?

[jamesdeuce]

"Can you eliminate Excel from the procedure or instead of running code behind Excel run it in Access? Access could link to the Excel if you must use Excel as source of the numbers."?

I built it in Excel as a "tool" as its own spreadsheet. There is no data in any of the worksheets. Initially I was going to feed in the dates of my DELETE and UPDATE lists from a cell but instead I just hardcoded all of these dates of the lists (not every day in history - there are only about 25 lists that contain all of the historic numbers - so I hardcoded 25 dates in history) into an array in the module behind the scenes. So when I run it once, it opens a list in history and then DELETEs and UPDATEs all of the databases and then it's done. I am basically just opening VBA Editor and running it from there. Are you saying move all of my modules and run them from Access? I wouldn't want to have to run this from every database. If it's a matter of opening ONE generic database as I am doing in Excel right now and running it from there I guess I could try that. If it's running from VBA does it really matter if it's running from Excel's VBA versus Access's VBA?

[June7]

It just confuses me that you have Access already open and from there open Excel and run code from Excel. I am not suggesting you run this from 'every' database.

I use Excel to pull data from Access and manipulate data in Excel. I have used code in Excel to write data to Access but not familiar with OpenModule and Run methods.

I have used code in Access to edit tables in other Access file.

[jamesdeuce]

Ok so it was flying through...working great...it had already Deleted the rows it needed to delete and it was about half way through the Updating portion...and I get this error..."Run-time error 3073" Operation must use an updateable query." When you hit debug on the error message it goes to the "DoCmd.RunSQL SQL" line in Access. And my database is now Read-Only. This is so annoying. And on top of that once it's read only I have no idea how to make it not read only. So my databases that I took forever to build are now fuct. I have been using copies of the databases so as not to corrupt my original data but you get the point. I have to make new copies again etc. And when I ultimately run this on my original databases this thing cannot crash on me!

[jamesdeuce]

I don't have Access open. The files I get from the internet that have the lists are Excel files and in ugly format where I can't make them Access databases easily. And I have Access databases built with tons of historical data that just sit in the background. The tool I built I built in Excel. It walks down each Excel list and looks for the numbers (they aren't A1 down...they are randomly placed somewhere down column A)...my Excel tool finds them, places them into a range. Then from Excel it opens the historical yearly Access databases and DELETEs for one number on the list (365 deletes), jumps to next number (365 deletes)...then after all numbers are done...it does the same for the UPDATE statements. It does this for one year, then another year, etc. Then it closes Access and we are good. During that process, when Access decides it wants to upset me, it turns one of the yearly historic databases read-only and then my DELETE module or UPDATE module can't work anymore and it breaks.

[jamesdeuce]

I also get a Run-time error 440 - Automation error. Perhaps all of these error messages are all related.

[jamesdeuce]

That database that was once randomly converted to read-only is no longer read-only. Almost as if it's temporarily read-only. Like the module is running, Access gets tired of running and decides it needs a break from working, so it changes a database to read-only and causes my module to crash. Then I leave it alone for a bit and now it's not read-only. And the same run that failed 15-30 minutes ago now worked perfectly. I didn't reboot the computer during any of this. I tried to compact the database just trying anything to help but I couldn't because it was read-only. I just let it sit there doing nothing...and none of the databases are read only now. Weird, I know!

[June7]

Your initial post stated "I have a module in VBA that opens a spreadsheet" and made me think Access file was already open because otherwise where is the code that 'opens a spreadsheet'?

I am not sure what is taking place when the OpenModule and Run lines are executed but if code is trying to process an UPDATE action that wants to edit data in a non-updatable query then I would expect serious consequences.

Does the crash occur for the same db file?

This may be too complex for us to analyse remotely.

[ssanfu]

There seems to be a lot of code missing.
But, from what is posted, this is what I see:

You have two variables, "Year" and "Table" that are reserved words.
"Year" is a function in VBA.
"Table" is a property: it indicates the name of a Relation object's primary table.

It is a really bad idea to use reserved words as object names.

Maybe use "arrYear" and "tblTable" or "tblName" or "TableName"
----------------------------------------------------


You say:

in Excel...open a file from 2013 and delete every row that that number is in, then open 2012, ... , through 2005, then take the next number in the list and open 2013, then 2012, ..., 2005 etc through the list.

But the code is deleting all of the numbers from one database before moving to the next database.


Look at this snippet of code for the Delete routine (I've done some formatting):

    For n = 1 To UBound(Year)
        For k = 1 To UBound(arrDelete)

            strDBName = Year(n) & "databasefile.accdb"
            appAccess.OpenCurrentDatabase (strPathDB & strDBName)
 

            appAccess.CloseCurrentDatabase
 
        Next k
    Next n

What you say (want) and what the code does is different.
If the array "arrDelete" has 100 elements, how many times is one database opened and closed before it moves to the next database?
(A: 100 times. That can really slow down the code)

This is how I would have the loops:

    For n = 1 To UBound(Year)
        strDBName = Year(n) & "databasefile.accdb"
        appAccess.OpenCurrentDatabase (strPathDB & strDBName)

        For k = 1 To UBound(arrDelete)


        Next k

        appAccess.CloseCurrentDatabase

    Next n

This is the string I would use:

            DeleteStatement = "DELETE * FROM " & Table & " WHERE numberfromlist  = " & arrDelete(k)

I don't know where the variable "Table" is declared or filled.
If the same table is in every database, why not use the table name instead of a variable?

-------------------------------------------------------

In the update code, the same logic would/should apply.

For the change string, I would try:

ChangeStatement = "UPDATE " & Table & " SET Pnumberfromlist = " & arrChangeTo(k) & " WHERE NumberFromList = " & arrChangeFrom(k)

Found an error in the update code
You have
For k = 1 To UBound(arrDelete)

I think it should be
For k = 1 To UBound(arrChangeFrom)

-------------------------------------------------------

All this line

 appAccess.DoCmd.OpenModule "DoCmdSQL", "PerformSQL"

does is open the IDE, opens the module in design view and locate the subroutine. The line can be removed/commented out.

-------------------------------------------------------

I would try changing

appAccess.Run "PerformSQL", DeleteStatement

to

appAccess.Execute DeleteStatement, dbFailOnError

(Don't have to have another sub......)

-------------------------------------------------------

My $0.02 .....