Good morning,
I'm currently having an issue where I cannot run action queries on a table where there's open recordset connections within form(s) (I'm using SQL Server 2008 R2 with linked ODBC tables) to the same table.
If I manually run a make table query, close the forms and run the action queries that dump the data from the make table query into the specific table it works. However... once I do the same in both VB and a macro, it spins and I end up with a Query Timeout error message (once the timeout reaches the 60 seconds I have specified within Access).
Originally, this is what I've had in VB for quite some time:
It has worked wondrously until late yesterday afternoon, where we began to have some bottlenecking issues within Access (reasons why, I have no idea yet). Even after both taking our Access SQL database offline and back online as well as restarting SQL Server, we still have this issue.
This is what I eventually tried doing in VB (as well as a macro) in order to fix this recordset issue. I converted "NPO Insert1 Edit" into a make-table query then added "NPO Insert1 Edit1" as the append query where it dumps the data into the table (only a handful of records too).
Sadly, it still locked up and timed out when attempted to write data to the troublesome table.
Any help with this issue would be mightily appreciated! :banghead:
I'm currently having an issue where I cannot run action queries on a table where there's open recordset connections within form(s) (I'm using SQL Server 2008 R2 with linked ODBC tables) to the same table.
If I manually run a make table query, close the forms and run the action queries that dump the data from the make table query into the specific table it works. However... once I do the same in both VB and a macro, it spins and I end up with a Query Timeout error message (once the timeout reaches the 60 seconds I have specified within Access).
Originally, this is what I've had in VB for quite some time:
Code:
DoCmd.SetWarnings False
DoCmd.OpenQuery "NPO Insert1 Edit", acViewNormal, acEdit
DoCmd.OpenQuery "NPO Insert2 Edit", acViewNormal, acEdit
DoCmd.Close acForm, "NPO Lookup Edit"
DoCmd.SelectObject acForm, "QE Custom", False
DoCmd.Requery ""
It has worked wondrously until late yesterday afternoon, where we began to have some bottlenecking issues within Access (reasons why, I have no idea yet). Even after both taking our Access SQL database offline and back online as well as restarting SQL Server, we still have this issue.
This is what I eventually tried doing in VB (as well as a macro) in order to fix this recordset issue. I converted "NPO Insert1 Edit" into a make-table query then added "NPO Insert1 Edit1" as the append query where it dumps the data into the table (only a handful of records too).
Code:
DoCmd.SetWarnings True
DoCmd.OpenQuery "NPO Insert1 Edit"
DoCmd.Close acForm, "NPO Lookup Edit", acSaveNo
DoCmd.Close acForm, "QE Custom"
DoCmd.OpenQuery "NPO Insert1 Edit1"
DoCmd.OpenQuery "NPO Insert2 Edit"
DoCmd.Requery ""
Sadly, it still locked up and timed out when attempted to write data to the troublesome table.
Any help with this issue would be mightily appreciated! :banghead: