0

I get error 3035: 'System Resource Exceeded' when executing the following sub:

Private Sub delete_result_staging()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs("qryWQPassthrough")
qdf.Connect = Me.con_str.Value
qdf.ReturnsRecords = False

qdf.SQL = "TRUNCATE TABLE tblWQResultStaging;"

qdf.Execute
Set qdf = Nothing
Set db = Nothing

End Sub

I am using MS Access 2013 and SQL Server 2012.

This code has worked previously; it stopped working when I refactored another sub per How to increase performance for bulk INSERTs to ODBC linked tables in Access?. I have changed the MaxLocksPerFile to 1,000,000 in the registry per other suggestions on the internet. I restarted Access and my computer, and I still get the error every time the sub fires. In the SQL Server Profiler I see no activity on the server when the code is executed. Forms and reports that point to linked tables on the same server but are connected via the GUI, rather than through VBA, still work as expected.

Nat
  • 325
  • 4
  • 14
  • 3
    Since you are deleting all rows in the table you should change this to truncate instead. – Sean Lange Aug 23 '18 at 18:26
  • So decrease MaxLocksPerFile to default. Not necessary, normally. If you need it somewhere use `SetOption`for temporary increase. What is `Me.con_str.Value`? – ComputerVersteher Aug 23 '18 at 18:29
  • Changed SQL to a truncate statement - no change. To be clear, I've been getting this error consistently before & after changing MaxLocksPerFile. Nevertheless, tried changing it back - no change. – Nat Aug 23 '18 at 18:33
  • 2
    Personally I would prefer to do everything with stored procedures and only call them from the front end. I don't like having queries inside the application code. That may lead itself to behaving nicer for you here. – Sean Lange Aug 23 '18 at 18:43

1 Answers1

0

I figured out the issue was that I was reusing a single passthrough query multiple times in VBA. It didn't like the SQL statement that was in the query from the last time I used it in code, so it threw the error. Looks like I need to either be more careful with my earlier VBA code that uses that query, or use separate queries for every VBA sub, or both.

Nat
  • 325
  • 4
  • 14
  • @ComputerVersteher I like your suggestion, but that code didn't quite work. This did, though: `Set qdf = db.CreateQueryDef("")` – Nat Aug 23 '18 at 18:51
  • Thanks for correction. Create a temporary QueryDef, just name it a vbNullString.like Set qdf = db.CreateQueryDef(vbNullString)., but don't replace vbNullString or Mathieu (or Error-Devil) will punish you ;) Put this to your solution! – ComputerVersteher Aug 23 '18 at 18:58
  • Or avoid pass through queries completely and use stored procedures. :D – Sean Lange Aug 23 '18 at 19:36
  • @SeanLange they don't exclude each other, especially if you are not familiar with ADO, but I agree that SP's should be used as you can reuse them on almost every frontend. – ComputerVersteher Aug 23 '18 at 19:41
  • I never hinted they would exclude each other, I was suggesting a better overall approach to managing an application. It is all about creating layers. Far too often I see people put together a front end using Access with all the queries in the code. Then they realize that Access is not really a great front end and want something better. They spend SO much time unravelling the embedded sql when they could have avoided it. – Sean Lange Aug 23 '18 at 19:45
  • @SeanLange what means avoid to you ;) I'm not sure if you are aware (as I suggest you using ADO only) that passthrough-queries are the only way to use SP's with DAO, but of course you are missing params. And binding forms to an editable recprdset with ADO is not easy, but with DAO it is. – ComputerVersteher Aug 23 '18 at 19:59
  • "Realize that Access is not really a great front end." Already there. Was there ten years ago. Sadly inherited this when I started my new job. Which seems to have happened with every job over the past ten years. Makes your point about using stored procedures that much more compelling. – Nat Aug 23 '18 at 20:30
  • What front end is more RAD? Plz tell us ;) Of course there are limits, but you can avoid them. Main issue: it is too easy, so noobs from EXCEL do bad things. But blame them not Access! – ComputerVersteher Aug 23 '18 at 20:54