?
Solved

Running multiple append queries in succession returns runtime error 3014: "Cannot open any more tables"

Posted on 2006-04-03
10
Medium Priority
?
1,184 Views
Last Modified: 2011-09-20
Greetings:
OK, this might be a little complicated.

I have a command button that operates visual basic that first deletes all the data in 6 separate tables, one at a time, closing each in turn.  Then the code opens/runs multiple append queries that repopulates those tables.  This used to work just fine when I first develope my database.  Over time the database grew and became more complicated.  At some point I began getting the runtime error 3014 "Cannot open any more tables" when it hits the second append query.  To my knowledge, there are not other tables open.  There are linked tables in my database, but are not open on the other end.  And my database is now a Design Master.  What is also wierd is that all my tables seem to disappear.  I have to close and reopen database to get tables back.  When I open my database again, I find that all the original data still exists even though it seems that the proceedure runs through that part just fine.  If I run the code in segments, just after the deletion section, all apears good.  If then I move the stop just after the end of the first repopulate (as shown below), all is good.  If I continue to move the stop from each repopulate to the next, I can get through the entire proceedure without a snag.  How can that be???  Please help me.

The partial code below carries the operation through repopulating the first table.

Private Sub Extract_Data_Click()
msg1 = "You are about to overwrite previously extracted RCS tables. Do you wish to continue?"
result = MsgBox(msg1, vbYesNo + vbExclamation)

If result = vbYes Then

DoCmd.SetWarnings False
'setting table variables
    Dim stDocName100 As String
    Dim stDocName200 As String
    Dim stdocName300 As String
    Dim stDocName400 As String
    Dim stDocName500 As String
   
    stDocName100 = "1_Xings Diverted Now/Potential"
    stDocName200 = "2_Landslide Sites"
    stdocName300 = "3_Potential Landslide Sites"
    stDocName400 = "4_High Plug Potential"
    stDocName500 = "5_High Urgency"
    stDocName600 = "6_Drivable Sites"
   
'form message, deleting table records
Me.Label24.Visible = True
Me.Repaint
'deleting all 1_Xings Diverted Now/Potential records for update
DoCmd.OpenTable stDocName100, acNormal, acEdit
    DoCmd.DoMenuItem acFormBar, acEditMenu, acSelectAllRecords, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, acDelete, , acMenuVer70
    DoCmd.close
'deleting all 2_Landslide Sites records for update
DoCmd.OpenTable stDocName200, acNormal, acEdit
    DoCmd.DoMenuItem acFormBar, acEditMenu, acSelectAllRecords, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, acDelete, , acMenuVer70
    DoCmd.close
'deleting all 3_Potential Landslide Sites records for update
DoCmd.OpenTable stdocName300, acNormal, acEdit
    DoCmd.DoMenuItem acFormBar, acEditMenu, acSelectAllRecords, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, acDelete, , acMenuVer70
    DoCmd.close
'deleting all 4_High Plug Potential records for update
DoCmd.OpenTable stDocName400, acNormal, acEdit
    DoCmd.DoMenuItem acFormBar, acEditMenu, acSelectAllRecords, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, acDelete, , acMenuVer70
    DoCmd.close
'deleting all 5_High Urgency records for update
DoCmd.OpenTable stDocName500, acNormal, acEdit
    DoCmd.DoMenuItem acFormBar, acEditMenu, acSelectAllRecords, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, acDelete, , acMenuVer70
    DoCmd.close
'deleting all 6_Drivable Sites records for update
DoCmd.OpenTable stDocName600, acNormal, acEdit
    DoCmd.DoMenuItem acFormBar, acEditMenu, acSelectAllRecords, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, acDelete, , acMenuVer70
    DoCmd.close
   
'Diversion Potential Repopulate

    Dim stDocName1 As String
    Dim stDocName2 As String
    Dim stDocName3 As String
    Dim stDocName4 As String
    Dim stDocName5 As String

    stDocName1 = "1_Xing Diversion Potential1"
    stDocName2 = "1_Xing Diversion Potential2"
    stDocName3 = "1_Xing Diversion Potential3"
    stDocName4 = "1_Xing Diversion Potential4"
    stDocName5 = "1_Xing Diversion Potential5"

Me.Label25.Visible = True
Me.Repaint
'Do Events
'Label25.Visible = True

    DoCmd.OpenQuery stDocName1, acNormal, acEdit
    DoCmd.OpenQuery stDocName2, acNormal, acEdit   'runtime error 3014 occures here
    DoCmd.OpenQuery stDocName3, acNormal, acEdit
    DoCmd.OpenQuery stDocName4, acNormal, acEdit
    DoCmd.OpenQuery stDocName5, acNormal, acEdit
0
Comment
Question by:Tetsinger
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 800 total points
ID: 16366114
Good Lord.

If these are all append queries, then instead of actually opening them using DoCmd.OpenQuery, just execute them like this (using ADO)...

Dim cn as adodb.connection
Set cn = CurrentProject.Connection

With cn
    .Execute "1_Xings Diverted Now/Potential"
    .Execute "2_Landslide Sites"
    .Execute "3_Potential Landslide Sites"
    .Execute "4_High Plug Potential"
    .Execute "5_High Urgency"
    .Execute "6_Drivable Sites"
    .Execute "yada yada yada"
End With
0
 
LVL 11

Assisted Solution

by:pootle_flump
pootle_flump earned 1000 total points
ID: 16366139
Hi

Just got through the first bit of your code. Much easier (and efficient) to use SQL to clear the tables:

Application.CurrentDB.Execute "DELETE * FROM 1_Xings Diverted Now/Potential", dbFailObError
Application.CurrentDB.Execute "DELETE * FROM 2_Landslide Sites", dbFailObError
.....

Six lines of code instead of 30-40. To be even more efficient you could declare and instaniate a DAO.Database object and use that isntead of Application.CurrentDB if you wish. You could also use ADO if you have an aversion to DAO.

Anyhoo...your current procedure looks like a memory gobbler (ALL the records are copied to memory before deletion). Try using the above and see if it helps. If you continue to have problems, please post the SQL of your update queries.

HTH
0
 

Author Comment

by:Tetsinger
ID: 16366612
I inserted code above and got the following errors.  Below is the code as it exists now.

Clearing Table Data section:

runtime error 3131
syntax error in FROM clause


Running Append Queries:

Run-time error '-2147217900(80040e14)':
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

Private Sub Extract_Data_Click()
Dim cn As ADODB.Connection
Set cn = CurrentProject.Connection

msg1 = "You are about to overwrite previously extracted RCS tables. Do you wish to continue?"
result = MsgBox(msg1, vbYesNo + vbExclamation)

If result = vbYes Then

DoCmd.SetWarnings False
 
'form message, deleting table records
Me.Label24.Visible = True
Me.Repaint
'deleting all 1_Xings Diverted Now/Potential records for update
Application.CurrentDb.Execute "DELETE * FROM 1_Xings Diverted Now/Potential", dbFailObError
'deleting all 2_Landslide Sites records for update
Application.CurrentDb.Execute "DELETE * FROM 2_Landslide Sites", dbFailObError
'deleting all 3_Potential Landslide Sites records for update
Application.CurrentDb.Execute "DELETE * FROM 3_Potential Landslide Sites", dbFailObError
'deleting all 4_High Plug Potential records for update
Application.CurrentDb.Execute "DELETE * FROM 4_High Plug Potential", dbFailObError
'deleting all 5_High Urgency records for update
Application.CurrentDb.Execute "DELETE * FROM 5_High Urgency", dbFailObError
'deleting all 6_Drivable Sites records for update
Application.CurrentDb.Execute "DELETE * FROM 6_Drivable Sites", dbFailObError

With cn

'Diversion Potential
.Execute "1_Xings Diverted Now/Potential1"
.Execute "1_Xings Diverted Now/Potential2"
.Execute "1_Xings Diverted Now/Potential3"
.Execute "1_Xings Diverted Now/Potential4"
.Execute "1_Xings Diverted Now/Potential5"

Me.Label25.Visible = True
Me.Repaint

'Landslide Sites
.Execute "2_Landslide1"
.Execute "2_Landslide2"
.Execute "2_Landslide3"

Me.Label26.Visible = True
Me.Repaint

'Potential Landslide Sites
.Execute "3_landslide potential1"
.Execute "3_landslide potential2"
.Execute "3_landslide potential3"

Me.Label27.Visible = True
Me.Repaint
   
'High Plug Potential
.Execute "4_hpp1"
.Execute "4_hpp2"
.Execute "4_hpp3"

Me.Label28.Visible = True
Me.Repaint
   
'High Urgency
.Execute "5_h-urgency1"
.Execute "5_h-urgency2"
.Execute "5_h-urgency3"
.Execute "5_h-urgency4"
.Execute "5_h-urgency5"

Me.Label29.Visible = True
Me.Repaint

'Drivability
.Execute "6_Drivable1"
End With
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 200 total points
ID: 16367492
You'll want to surround your object names with square brackets - but especially
"DELETE * FROM [1_Xings Diverted Now/Potential]"

As for the append failures - that would depend on the SQL.
Are they action queries?
The error message seems to be saying that one of them isn't - as you try to execute it.

Can you temporarily comment out your error handling to determine which line it drops out on - and inspect that query's SQL?
0
 
LVL 11

Assisted Solution

by:pootle_flump
pootle_flump earned 1000 total points
ID: 16368356
Apols about missing the name of the first table. Have a check of this article http://support.microsoft.com/?scid=kb;en-us;826763&spid=2509&sid=50 . note - the forward slash isn't in this list but it should be ;-)

>>DoCmd.SetWarnings False
please please get rid of this line. The advantage of .Execute is it is no longer necessary. SetWarnings False can cause chaos in a db. At the very minimum put the SetWarnings True in the exit part of your error trp so it *always* gets executed.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16372440
The logic as described by others is the best way to go i.e. run sql to delete first, then run sql to append

Assuming you have one query to run per one table, create an array holding this info

Im not one for duplicating code, so this is how I would do it



Private Const IDX_TBL = 0
Private Const IDX_QRY = 1

Public Sub AppendData()

'Define
    Dim sObjects(5, 2) As String
    Dim i as integer


    sObjects(0, IDX_TBL) = "1_Xings Diverted Now/Potential"
    sObjects(1, IDX_TBL) = "2_Landslide Sites"
    sObjects(2, IDX_TBL) = "3_Potential Landslide Sites"
    sObjects(3, IDX_TBL) = "4_High Plug Potential"
    sObjects(4, IDX_TBL) = "5_High Urgency"
    sObjects(5, IDX_TBL) = "6_Drivable Sites"
   
    'Assumes all queries prefixed with 1_Xing Diversion Potential, if not, then set like u do the tables above
    For i = 0 to ubound(sObjects)
          sObjects(i, IDX_QRY) = "1_Xing Diversion Potential" & i
    Next i    

    For i = 0 To UBound(sObjects)
        Debug.Print "Clearing down table", sObjects(i, IDX_TBL)
        DoCmd.RunSQL "DELETE FROM " & sObjects(i, IDX_TBL)
       
        Debug.Print "Running Query", sObjects(i, IDX_QRY)
        DoCmd.RunSQL sObjects(i, IDX_QRY)
    Next i
End Sub

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16372594
drawback with this is you cannot run SELECT statements using RunSQL
hence the error

Run-time error '-2147217900(80040e14)':
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.


So a workaround is to get the SQL from the query, and open a recordset to run that
i.e.

Dim qdf as QueryDef


    For i = 0 To UBound(sObjects)
        Debug.Print "Clearing down table", sObjects(i, IDX_TBL)
        DoCmd.RunSQL "DELETE FROM " & sObjects(i, IDX_TBL)
       
        Debug.Print "Running Query", sObjects(i, IDX_QRY)
        set qdf = Application.CurrentDb.QueryDefs(sObjects(i, IDX_QRY))

        debug.print "SQL TO RUN", qdf.SQL  

0
 

Author Comment

by:Tetsinger
ID: 16374631
This is a lot of help and have already made light years of progress.  There is a lot to consider.   I need to digest a little for a day or two.
Thanks, I'll be back.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16375461
Good luck. In 2 days time, I will be off travelling to the states
0
 

Author Comment

by:Tetsinger
ID: 16394280
Thank you all.  All your answers were a big help.  I know just enough to be dagerous.  Bit by bit, I'm getting better and more sophisticated with your advise.  
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question