CODEWITHSUNDEEP
vbams-access
Brent
Brent

Reputation: 55

Run-Time Error 3146 - VBA Access Execute Stored Procedure - Cursor state(#0)

I am getting an error code (3146: ODBC-call failed - Invalid cursor state (#0) on my local machine using Microsoft Access 365, but it works in our remote desktop environment using 2016 Access Professional Plus.

After reading this post, I set my ODBC timeout to 2000 in the query's property sheet, but still, get the same error and it only takes about 10 seconds to throw.

I also read that there were issues with the cursor state in an earlier version of Access 2016 and not sure if that is a problem with Office 365?

This subroutine has worked for years without any issues. It looks like it is tied to Office 365.

Any ideas?

The code errors on this line

DoCmd.OpenQuery "qryImportGMSData" 'Creates tempTable with Weekly Import from GMS

Below is my subroutine

Sub GMSImport(StartDate As Date, EndDate As Date)
Dim SqlStr As String
Dim db As DAO.Database
Set db = CurrentDb()



'Builds query string for stored procedure maintained by SWDev from GMS to show Weekly Deals for report.
SqlStr = "EXEC  dbo.upRpt_WeeklyDealsReport @BeginDate = '" & Format(StartDate, "yyyy-mm-dd") & "' ," & _
  " @EndDate = '" & Format(EndDate, "yyyy-mm-dd") & "' ," & _
  " @RegionType_Short_List = 'CA~GC~MC~MW~SW~WE~NE' ," & _
  " @IncludeBulletDeals = 0 ," & _
  " @FormatType = 'spgexporttype'"

db.QueryDefs("ptqry_DataExport").sql = SqlStr 'sets the parameters for the stored procedure



DoCmd.SetWarnings False
DoCmd.OpenQuery "qryImportGMSData" 'Creates tempTable with Weekly Import from GMS

DoCmd.RunSQL "ALTER TABLE temp_GMSImport ADD COLUMN Selected BIT" 'Add Selected Column to temp table
DoCmd.RunSQL "ALTER TABLE temp_GMSImport ADD COLUMN dvsValuationDef_ID INT" 'Adds Valuation Def Column to temp table
DoCmd.RunSQL "UPDATE temp_GMSImport SET temp_GMSImport.Selected = -1;" 'Sets all Selected field to TRUE

DoCmd.SetWarnings True


End Sub

It errors out on this line:

DoCmd.OpenQuery "qryImportGMSData" 'Creates tempTable with Weekly Import from GMS

I am able to execute the stored procedure in SMSS with no issues:

"EXEC  dbo.upRpt_WeeklyDealsReport @BeginDate = '" & Format(StartDate, "yyyy-mm-dd") & "' ," & _
  " @EndDate = '" & Format(EndDate, "yyyy-mm-dd") & "' ," & _
  " @RegionType_Short_List = 'CA~GC~MC~MW~SW~WE~NE' ," & _
  " @IncludeBulletDeals = 0 ," & _
  " @FormatType = 'spgexporttype'"

When I test by 64 bit ODBC connection, it test successfully.

Update -more testing inside Access:

I was able to execute the T-SQL code inside Access without using VBA.

There are 3 queries involved in the sequence and the one that is not working is qryImportGMSData. It is a problem without using VBA, so that narrows it down a bit.

qryImportGMSData
Query: Make Table Query (Replicated) This one is causing the error

SELECT *   INTO temp_GMSImport
FROM qryImportTransform;

qryImportTransform Query: Select Query (Replicated) Works

SELECT *
FROM ptqry_DataExport;

Ptqry_DataExport
SQL Pass-Through Query (Replicated) Works

EXEC  dbo.upRpt_WeeklyDealsReport @BeginDate = '2021-02-22' , @EndDate = '2021-02-28' , @RegionType_Short_List = 'CA~GC~MC~MW~SW~WE~NE' , @IncludeBulletDeals = 0 , @FormatType = 'spgexporttype'

Upvotes: 0

Views: 1559

Answers (1)

Albert D. Kallal
Albert D. Kallal

Reputation: 48989

Ok, so we narrowed down that the PT query can work from Access.

We are likely dealing with two different connections - so there is a "wee bit" of asynchronous (threading) occurring here.

If not already, use the SAME connection object to run all 3 query.

So, use this:

db.Execute "qryImportGMSData",dbFailOnError
db.Execute "ALTER TABLE temp_GMSImport ADD COLUMN Selected BIT", dbFailOnError
db.Execute "ALTER TABLE temp_GMSImport ADD COLUMN dvsValuationDef_ID INT"
db.Execute "UPDATE temp_GMSImport SET temp_GMSImport.Selected = -1;",dbFailOnError

The other BIG advantage is above is faster, does NOT mess with the Access "UI" settings, and you don't have to change setwarnings.

And the above does NOT force access to try and use "transactions".

So, the problem is we using two different connections, you using "db", and then using the docmd which is a "separate" connection - and you can't control this much.

With two connection objects, it "almost" similar to two people running the two different parts of the code.

Give the above a try - stick to "one" connection object, so they can "behave" as a similar group of commands. And as noted, dumping docmd has several downsides, and is more attached to the Access "UI" options then it is to pure code.

Upvotes: 1

Related Questions