Intermittent VBA crash: Recordset not Updatable

[Netgroover]

I wrote a macro and converted to VBA in Access/Office365, and I run this routine nightly. A few months ago it started crashing a few times a week with the error "the recordset is not updatable". If I debug and set the next statement at the line it crashed, then select continue, the routine finishes just fine.

I must stress that I'm an amateur Access user, but I would love to solve this problem so I don't have to manually intervene in what is an automatic routine most of the time. Any guidance greatly appreciated!

Here is where the line where the code sometimes crashes:

DoCmd.OpenQuery "Valid Reservations", acViewNormal, acEdit

In Debug I restart on the next line (the query table is open), and Continue here:

DoCmd.Save acQuery, "Valid Reservations"
DoCmd.RunSavedImportExport "Export-Valid Reservations"

Below is SQL view of the Query:

SELECT in_res.guestnum, in_res.level, in_res.resno, in_res.package, rate_codes.Pkg AS RATEPKG, Round([SUBTOTAL]/[NIGHTS],2) AS AVG_RATE, room_rates.RAK, [AVG_RATE]/[RAK] AS PCT_OF_RAK, in_res.arrival, in_res.depart, in_res.unit, Left([unit],1) AS PROP, in_res.anum, in_res.cnum, in_res.onum, IIf([ANUM]>2,[ANUM]-2+[ONUM],[ONUM]) AS XTRA, in_res.group, in_res.nights, in_res.booking, in_res.cancel, in_res.subtotal, in_res.rnotes, in_res.anotes, in_res.hnotes, in_res.op, in_res.name, in_res.rsource, [arrival]-[booking] AS LEAD, Month([BOOKING]) AS BOOK_MO, Year([BOOKING]) AS BOOK_YR, Month([ARRIVAL]) AS ARR_MO, Year([ARRIVAL]) AS ARR_YR, in_guest.added, Date()-[depart] AS SINCE_DEP, [noemail] & [bademadd] AS Exclude, IIf([added]<[booking],"PG","New") AS Prev_Gst, in_user_prop.homeprop, in_guest.stays, in_guest.nites, in_guest.lststay, in_guest.lstunit, in_email.email, in_guest.city, in_guest.state, in_res.rcorp

FROM ((((in_res LEFT JOIN in_guest ON in_res.guestnum = in_guest.guestnum) LEFT JOIN in_email ON in_guest.guestnum = in_email.guestnum) LEFT JOIN rate_codes ON in_res.package = rate_codes.OldPkg) LEFT JOIN room_rates ON in_res.unit = room_rates.ROOM) LEFT JOIN in_user_prop ON in_res.op = in_user_prop.USRCODE

WHERE (((in_res.level)<>"xxx" And (in_res.level)<>"OOO" And (in_res.level)<>"C/L") AND ((in_res.package)<>"COMP") AND ((Left([unit],1))<>"S") AND ((in_res.nights)>0) AND (([guar] & [level])<>"NNEW" And ([guar] & [level])<>"NCAN"))

ORDER BY in_res.booking DESC;

 

[BlueIshDan]

Do me a favor, does the query open when you launch it from the interface manually?

 

[Netgroover]

Yes, but I see "Recordset not Updatable" appear in the status bar at bottom of screen during load; after load is complete that message changes to "Datasheet View"

 

[BlueIshDan]

Show me your vba sub/function. The entire thing please

 

[BlueIshDan]

Please surround in code braces

 

[Netgroover]

I apologize, but I'm not sure what that means. Shall I read some instructions here on the forum? I really appreciate your help!

 

[BlueIshDan]

Click the Go Advanced button and look through the buttons on the top of the text window. One will say code when you over your mouse over it.

 

[vbaInet]

I'll mention two things:

1. It worries me when I see code that deletes tables
2. Your error handler is continuing to the next line if an error occurs. Why's that? Is there no interdependence between the queries below and the ones above?

And like Dan mentioned, please use code tags. Here's how:
http://www.access-programmers.co.uk/forums/showthread.php?t=200247

 

[Netgroover]

Is this correct?

Option Compare Database

'------------------------------------------------------------
' Run_Imports
'
'------------------------------------------------------------
Function Run_Imports()
On Error GoTo Run_Imports_Err

DoCmd.DeleteObject acTable, "in_guest"
DoCmd.DeleteObject acTable, "in_res"
DoCmd.DeleteObject acTable, "in_hres"
DoCmd.DeleteObject acTable, "in_email"
DoCmd.DeleteObject acTable, "in_bals2"
DoCmd.DeleteObject acTable, "in_rtcal"
DoCmd.DeleteObject acTable, "in_yield"

DoEvents

DoCmd.RunSavedImportExport "Import-in_email"
DoCmd.RunSavedImportExport "Import-in_bals2"
DoCmd.RunSavedImportExport "Import-in_rtcal"
DoCmd.RunSavedImportExport "Import-in_yield"
DoCmd.RunSavedImportExport "Import-in_res"
DoCmd.RunSavedImportExport "Import-in_hres"
DoCmd.RunSavedImportExport "Import-in_guest"

DoEvents

DoCmd.SetWarnings False
DoCmd.OpenQuery "Append HRes", acViewNormal, acEdit
DoCmd.SetWarnings True

'On Error Resume Next

DoCmd.OpenQuery "Valid Reservations", acViewNormal, acEdit
DoCmd.Save acQuery, "Valid Reservations"
DoCmd.RunSavedImportExport "Export-Valid Reservations"
DoCmd.Close acQuery, "Valid Reservations"
DoCmd.OpenQuery "Filtered RtCal", acViewNormal, acEdit
DoCmd.Save acQuery, "Filtered RtCal"
DoCmd.RunSavedImportExport "Export-Filtered RtCal"
DoCmd.Close acQuery, "Filtered RtCal"
DoCmd.OpenQuery "Filtered Yield", acViewNormal, acEdit
DoCmd.Save acQuery, "Filtered Yield"
DoCmd.RunSavedImportExport "Export-Filtered Yield"
DoCmd.Close acQuery, "Filtered Yield"
DoCmd.OpenQuery "Valid Email", acViewNormal, acEdit
DoCmd.Save acQuery, "Valid Email"
DoCmd.OpenQuery "Valid Guest NAD", acViewNormal, acEdit
DoCmd.Save acQuery, "Valid Guest NAD"
DoCmd.RunSavedImportExport "Export-Valid_Guest_NAD"
DoCmd.Close acQuery, "Valid Guest NAD"
DoCmd.Close acQuery, "Valid Email"
DoCmd.OpenQuery "Valid Res NAD", acViewNormal, acEdit
DoCmd.Save acQuery, "Valid Res NAD"
DoCmd.RunSavedImportExport "Export-Valid_Res_NAD"
DoCmd.Close acQuery, "Valid Res NAD"
DoCmd.Quit acSave


Run_Imports_Exit:
Exit Function

Run_Imports_Err:
MsgBox Error$

Stop

Resume Run_Imports_Exit

End Function

 

[Netgroover]

I'll mention two things:

1. It worries me when I see code that deletes tables
2. Your error handler is continuing to the next line if an error occurs. Why's that? Is there no interdependence between the queries below and the ones above?

1. I could not seem to import new tables that would overwrite the old ones, so I coded to delete the old ones before importing the new.
2. I was trying to redirect the code back to where it crashes, for when I manually debug and set the next statement and continue, the code executes just fine.

I'm not a sophisticated programmer, kind of self taught, just trying to learn. I appreciate your taking the time to help.

 

[vbaInet]

Ok makes sense.

One more question, why are you opening your queries in edit mode and then closing it?

 

[Netgroover]

Ok makes sense.

One more question, why are you opening your queries in edit mode and then closing it?

I created a macro for the steps I needed, then converted to VBA. I usually program in Excel which is easier because I can record many of the steps and then modify the code.

I guess the real answer to your question is that I don't know any better...

In a nutshell, I'm trying to import new tables, then update the queries and export the tables for Excel. I'm trying to use the power of Access to create large data tables for Excel to then utilize. If I new Access better (which I'm trying to learn) I would do most of my work in Access.

 

[vbaInet]

Sorry, yes you did actually mention in your first post that you converted it from a macro to VBA.

As you don't need to keep creating and deleting tables (because it can lead to bloating) you will need to restructure the structure. I'll give you the steps:

For now, do two things:

1. Create queries to delete all the data in each of your tables
2. Amend all the Import specifications (i.e. "Import-in_email" etc) to update the relevant tables instead of creating new ones.

Once you've done the above we can take it from there.

 

[Netgroover]

Sorry, yes you did actually mention in your first post that you converted it from a macro to VBA.

As you don't need to keep creating and deleting tables (because it can lead to bloating) you will need to restructure the structure. I'll give you the steps:

For now, do two things:

1. Create queries to delete all the data in each of your tables
2. Amend all the Import specifications (i.e. "Import-in_email" etc) to update the relevant tables instead of creating new ones.

Once you've done the above we can take it from there.

Thanks, I appreciate the advice, but I don't know how to do that. I'll need to do some reading, unless you can provide examples of what you are suggesting.

What does not seem logical to me is that it works fine 80% of the time, and when it bombs out it can be restarted out of the code break using Debug - and finish executing just fine. Maybe there's some memory instability in the software? Why would the same code work one day and not the next?

I'll look into attempting your suggestion.

 

[vbaInet]

I'm guessing that you're using a newer version of Access since you mentioned Office 365 so here's some information on how to do step 1:
http://www.fmsinc.com/microsoftaccess/query/snytax/delete-query.html

And for step 2:
http://www.access-diva.com/d7.html

You're doing quite a lot of data churning in one go and Access hasn't caught up that's why it errors. It works ok after the break because at that point in time Access has caught up, freed up some resources and is ready to continue its task. That's why I want to change the way you're doing things.

 

[Netgroover]

I'm guessing that you're using a newer version of Access since you mentioned Office 365 so here's some information on how to do step 1:

And for step 2:

You're doing quite a lot of data churning in one go and Access hasn't caught up that's why it errors. It works ok after the break because at that point in time Access has caught up, freed up some resources and is ready to continue its task. That's why I want to change the way you're doing things.

Ok, I follow your logic. And I read your links you provided. I rolled up my sleeves and...

It seems there is an issue in that the new tables I'm importing are Foxpro DBF files that I import via ODBC; and I don't see that Access is able to use append this data to an existing table - it appears it will always create a new table. I had forgotten that this is why I was forced to delete the old table... unless there is another way?

If not, is it possible to "pause" the statements and allow Access to "catch up" so that the VBA doesn't crash?

 

[vbaInet]

Did you try running an import routine to import from foxpro just to see if it's possible to update a table? The spec is created in Access so try creating a new import spec in Access linking to foxpro.

If you're unsuccessful with that you could export to Excel from Fox and import from Excel to Access.

The DoEvents code is the only simple piece of code that "helps" Access to catch up.

 

[Netgroover]

Did you try running an import routine to import from foxpro just to see if it's possible to update a table? The spec is created in Access so try creating a new import spec in Access linking to foxpro.

If you're unsuccessful with that you could export to Excel from Fox and import from Excel to Access.

The DoEvents code is the only simple piece of code that "helps" Access to catch up.

Here's the only import routine option that I see via ODBC:

"If the specified object does not exist, Access will create it. If the specified object already exists, Access will append a number to the name of the imported object..."

1. Would a loop of a thousand (or more) DoEvents help or not?

2. Using the ODBC import, if table TABLE_1 is the existing table, and TABLE_2 is imported, could I then append TABLE_2 to TABLE_1 within Access, and delete TABLE_2? Would only the new records be added... even so, is this helpful at all?

Again, thank you for your time and suggestions.

 

[vbaInet]

Ah yes, the ODBC options are lesser than the others.

You can definitely use code (something called ADO or DAO) to make the connection, fetch the data and update a table. I can't really give you the code for that because I don't know the connection strings to your FoxPro and you don't know enough VBA to test it.

What about ODBC'ing to Excel? If the file was an Excel file Access will allow you to update the table.

A loop won't help. VBA is single threaded meaning it has to wait for one code to finish before the next starts. DoEvents is just as good.

Don't worry, it's not the end of the world. We can look at other alternatives.