VBA Module running DELETE and UPDATE in Access via SQL causing DATABASE to be READ ONLY

**jamesdeuce** · 08-17-2013, 08:23 AM

I have an Excel Module that goes into multiple giant Access databases and deletes rows or changes a field from a specific number to a different number...Excel is feeding those specific numbers from a range in a sheet. I am the only one using these databases and excel files. There are no permission problems. The folder is not read only. Sometimes the module runs flawlessly and sometimes it doesn't. I get random automation errors and I think it's because somewhere along the way one of the Databases is randomly changing to "Read-Only" and I have no idea why. I have even tried compacting the databases after each deletion or update so that the database starts "fresh" each time. And compacting adds so much time to the task and yet it still crashes randomly. Any ideas of why this is happening? I am a relatively new programmer. Thank you very much.

**Norie** · 08-17-2013, 10:55 AM

How are you connecting to the database(s)?

Can we see the code?

**jamesdeuce** · 08-17-2013, 12:02 PM

I have a module in Access called "DoCmdSQL" that looks like this...
[code]
Sub PerformSQL(SQL As String)

DoCmd.RunSQL SQL

End Sub
[code]

My vba code of interest in Excel looks like this...
[code]
Dim appAccess As Object

Set appAccess = CreateObject("Access.Application")

appAccess.OpenCurrentDatabase (strPathDB & strDBName)

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

appAccess.DoCmd.OpenModule "DoCmdSQL", "PerformSQL"
appAccess.Run "PerformSQL", ChangeStatement
[code]

My SQL statement should be fine...I think. If anything I am not sure what the appAccess statements are supposed to look like. Once again this works sometimes and sometimes it fails. I get Automation Errors and random runtime errors. Each database has ~10,000 rows * 365 days in a year so that change statement is changing looking for a specific number in the past (one for each day of the year) and changing it to a new number. My VBA code loops through a few years. Sometimes it makes it through one year and then crashes on the next...and then I have to start all over again. Once the database becomes read only...I'm fuct. What is making the databases randomly becoming read-only in the middle of updating (and sometimes the deleting) process? I obviously only posted the UPDATE statement but the DELETE code follows all of the same appAccess lines with the only difference is the DELETE statement. Thank you very much for any assistance.

**Norie** · 08-17-2013, 12:06 PM

Have you considered not automating Access to do this?

ADO or DAO could be used to connect to the database(s) and execute the SQL.

By the way, why are you opening a module in Access?

Also, where's the code to quit Access and set the reference to it to Nothing?

**jamesdeuce** · 08-17-2013, 12:22 PM

I don't know how to automate Access to do it. I am new to programming and the way I did it is the only way I know. I have heard of ADO and DAO but no idea how to use them. The only way I saw someone else do it was to call that "doCmd" SQL module in Access that they built into Access and I copied what they did. I close the database at the end...

appAccess.DoCmd.OpenModule "DoCmdSQL", "PerformSQL"
appAccess.Run "PerformSQL", ChangeStatement
appAccess.CloseCurrentDatabase

but I don't quit Access as I didn't know I should, not sure I know how to do that and I didn't know you had to set its reference to nothing and don't know how to do that either. The only thing I tried was to "Compact" it (via a change in Access options) after every iteration and that takes forever. All your ideas might be great I just don't know how to do any of that. Perhaps one of them will prevent the random change to read-only. Hopefully you can teach me some new techniques. It's probably something easy I just haven't seen it before. Thank you.

**jamesdeuce** · 08-17-2013, 12:23 PM

I have a module in Access called "DoCmdSQL" that looks like this...

 Sub PerformSQL(SQL As String)

 DoCmd.RunSQL SQL

 End Sub

My vba code of interest in Excel looks like this...

 Dim appAccess As Object

 Set appAccess = CreateObject("Access.Application")

 appAccess.OpenCurrentDatabase (strPathDB & strDBName)

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

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

Sorry I did the tags wrong before...forgot how to do them.

**Norie** · 08-17-2013, 02:23 PM

James

That code is automating Access and I think that might be at the root of the problems you are having.

One thing I notice is that you don't disable warnings in your code.

Whenever you run an action query like APPEND or DELETE Access pops up a message saying 'You are about to update/delete X records. Do you want to continue?', or something like that.

Since you aren't disable warnings there's a chance the database isn't actually being closed.

Try adding this to your code to see what I mean, put it just after you've used CreateObject.

appAccess.Visible = True

**jamesdeuce** · 08-17-2013, 04:46 PM

To be more specific also I am getting run-time error '3086'. "Could not delete from specified tables." Although the funny thing is I ran it again right after my previous post and it worked flawlessly. Something is causing it to work sometimes and not work others. Very strange.

**jamesdeuce** · 08-17-2013, 04:41 PM

I get a run-time error -2147417848. Automation error. The object invoked has disconnected from its clients. Although that may have been caused by appAccess.Quit in a "bad" location.

I also get a run time error 440 automation error. Although it ran flawlessly the first time after I added in the appAccess.visible = true. Now one of the databases is stuck in read only again when I tried to run it a second time and it's failing on the appAccess.Run "PerformSQL", DeleteStatement this time as if it's dying in the Access SQL module because the file that was open once before is now read only.

**Norie** · 08-17-2013, 05:55 PM

James

After you've run the code is Access still running?

You can check for that in the Task Manager.

**jamesdeuce** · 08-17-2013, 10:09 PM

It's been a few hours since I last ran it and it ran flawlessly (the same databases that were read only earlier). There has always been a "appAccess.quit" line in my code at the end so when it runs to completion it quits. I ran it again immediately after that and I got errors like I got in the past and of course before you "stop" the module Access is still running, but after I manually "stop" it isn't running. I don't think Access still running when I am done is a problem. I saw another box that said "Microsoft is waiting for another application to complete an OLE action". Perhaps that is a related problem. Do you have any other suggestions?

**Norie** · 08-18-2013, 07:35 AM

Sounds to me as though Access is waiting for the warning message I mentioned earlier to be dismissed.

**jamesdeuce** · 08-18-2013, 02:08 PM

Let me try to re-explain what I am doing. Maybe my explanation wasn't good. I have historical data from the last 7 years. I have databases with approximately 10,000*365 rows from 2005 to today. My situation is similar to something like this...imagine if throughout time, the US Postal Service changed zip codes for a given area...so let's say a zip code from the beginning of time is "54321" and in 2010 the USPS decides to change it to "90210". So in the middle of 2010 to today 2013 the demographic data is listed as under "90210" but in history for that same data it's "54321". To compare apples to apples I need to change every row with "54321" to "90210". Every 3-6 months or so a list is sent out with theses zip code deletions and changes. If a list comes out in 2011...I need to open the 2011, 2010, ... , 2005 databases and DELETE certain zip codes from history...and I need to open 2011, 2010, ... , 2005 and UPDATE (CHANGE) zip codes from one number to another. My module sometimes works perfectly so warning messages aren't blocking it (although per your suggestions I unchecked the Action Query Confirm under Access Options/Client Setting but that wasn't causing a problem) and sometimes it crashes in the middle of the list when the database randomly becomes read only. I don't know if the module is moving too fast for Access to open and close. I don't know why in the middle it would randomly become read-only. It's not like it doesn't start...it starts and crashes in the middle of the process. Should I post my problem to an Access Forum instead?

**Norie** · 08-18-2013, 04:58 PM

John

I've got a pretty good idea of what you are doing, but I think you are going about it the wrong way.

Using ADO/DAO is the way I would do it, and it's the way I've done this sort of thing in the past.

Since you already have the SQL for your queries all you really need is the code to connect to the database(s).

That's actually quite straightforward, here's some sample code for connecting to an accdb databse.

Sub UpdateTbl()
Dim con As ADODB.Connection
Dim rst As ADODB.Recordset
Dim dbPath As String

    dbPath = "C:\TestData\World.accdb"

    Set con = New ADODB.Connection
    con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath

    con.Open

Once you have the connection you can execute your UPDATE/DELETE queries.


    strSQL= strSQL  & " UPDATE " & Table 
    strSQL = strSQL & " SET " & Table & ".specificnumber = " & arrChangeTo(k) 
    strSQL = strSQL & " WHERE ((" & Table & ".specificnumber)=" & arrChangeFrom(k) & ")"

    Set rst = New ADODB.RecordSet

    rst.Open strSQL, con, adOpenDynamic, adLockOptimistic

While you have the connection open you can execute all the queries you want and once you are finished you close the recordset and connection.


     rst.Close
    
     Set rst = Nothign

     con.Close
  
     Set con = Nothing

VBA Module running DELETE and UPDATE in Access via SQL causing DATABASE to be READ ONLY

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

VBA Module running DELETE and UPDATE in Access via SQL causing DATABASE to be READ ONLY

Re: VBA Module running DELETE and UPDATE in Access via SQL causing DATABASE to be READ ONL

Re: VBA Module running DELETE and UPDATE in Access via SQL causing DATABASE to be READ ONL

Re: VBA Module running DELETE and UPDATE in Access via SQL causing DATABASE to be READ ONL

Re: VBA Module running DELETE and UPDATE in Access via SQL causing DATABASE to be READ ONL

Re: VBA Module running DELETE and UPDATE in Access via SQL causing DATABASE to be READ ONL

Re: VBA Module running DELETE and UPDATE in Access via SQL causing DATABASE to be READ ONL

Re: VBA Module running DELETE and UPDATE in Access via SQL causing DATABASE to be READ ONL

Re: VBA Module running DELETE and UPDATE in Access via SQL causing DATABASE to be READ ONL

Re: VBA Module running DELETE and UPDATE in Access via SQL causing DATABASE to be READ ONL

Re: VBA Module running DELETE and UPDATE in Access via SQL causing DATABASE to be READ ONL

Thread Information

Users Browsing this Thread

Similar Threads

Read MS Access Database to Get Last Record

Update/Delete a Record in an Access 2010 Database using Excel VBA

how to access Sheet module, normal module, Worbook module to type code

[SOLVED] Running Excel module within Access

Query a Access database that has a module from Excel

Bookmarks

Bookmarks

Posting Permissions