ODBC Error 2146 : Make Table Query - Office 365 vs Professional Plus 2016

[mduplantis]

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. We are using the same drivers in both environments: SQL Server Native Client 11.0

Sub GMSImport(StartDate As Date, EndDate As Date)Dim SqlStr As StringDim db As DAO.DatabaseSet db = CurrentDb()

'Builds query string for stored procedureSqlStr = "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 FalseDoCmd.OpenQuery "qryImportGMSData" 'Creates tempTable from import
DoCmd.RunSQL "ALTER TABLE temp_GMSImport ADD COLUMN Selected BIT" 'Add Selected Column to temp tableDoCmd.RunSQL "ALTER TABLE temp_GMSImport ADD COLUMN dvsValuationDef_ID INT" 'Adds Valuation Def Column to temp tableDoCmd.RunSQL "UPDATE temp_GMSImport SET temp_GMSImport.Selected = -1;" 'Sets all Selected field to TRUE

DoCmd.SetWarnings True

End Sub

The error occurs on this line of code:

DoCmd.OpenQuery "qryImportGMSData" 'Creates tempTable from import

I am using a passthrough query to execute a stored procedure, and use the results in a make table query. I can manually execute the passthrough query against the SQL Server db, but when the code runs qryImportGMSData (make table query) I get the ODBC call error.

Any idea why there is a difference between Office 365 and Professional Plus 2016?

Thanks,
Brent

[Minty]

Let me check your debugging to make sure I haven't misinterpreted it.

Your saved Pass through query (ptqry_DataExport) works if you simply open it in the normal navigation window?
But it doesn't work if you call it from code?

Or it still works, but the query you run against it then fails?

[mduplantis]

Let me check your debugging to make sure I haven't misinterpreted it.

Your saved Pass through query (ptqry_DataExport) works if you simply open it in the normal navigation window?
But it doesn't work if you call it from code?

Or it still works, but the query you run against it then fails?

Hello,

There are three queries involved in the process. I can execute two of the queries using the normal navigation window, but the make table query (qryImportGMSData) errors in the navigation window and using the code to call it. It gives me an ODBC error, but I know the ptqry is able to communicate with the SQL server db as it will return the correct values if I execute from the navigation window.


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

SELECT *   INTO temp_GMSImport
FROM qryImportTransform;

qryImportTransform Query: Select Query (Replicated) Works via the navigation window

SELECT *
FROM ptqry_DataExport;

Ptqry_DataExport (executes a stored procedure against SQL server db)
SQL Pass-Through Query (Replicated) Works via the navigation window

EXEC  dbo.upRpt_WeeklyDealsReport @BeginDate

Thank you!

[Minty]

Have you considered missing out the middleman?

SELECT * INTO temp_GMSImport
FROM ptqry_DataExport;

And I assume you have deleted temp_GMSImport before making the new version?

[Minty]

But of a late edit:

I'm pretty sure (Replicated) means it's not a local query?
Is this running from a web app or something else similar?

[mduplantis]

Have you considered missing out the middleman?

SELECT * INTO temp_GMSImport
FROM ptqry_DataExport;

And I assume you have deleted temp_GMSImport before making the new version?

It’s existing code that I did not create, but I believe there was a reason it was set up this way. Everything works perfect until I use office 365 for the application. It has me thinking it’s some type of driver or application issue that’s interfering with the execution.

[mduplantis]

It might be replicated because were using access as a form with the SQL Server hosting the majority of the data besides a few local tables. I have to check into that, but I see it and other queries too. Thank you

[Minty]

It could be a missing reference, but seems unlikely.

In the VBA editor click on Tools, and then references, if any are listed as Missing or Broken that will possibly be your issue.
The other option could be default record locking or similar. Check that the recordsets are opened in exactly the same way and that the saved query connection properties are identical.

[mduplantis]

Hi,

I checked the references earlier in the process and they are the same. The connection strings and records sets are also the same, but I agree it seems like there is a lock or something blocking the execution. My local machine is using SQL Server Native Client 11.0 for the driver, but I just noticed the remote desktop version (that works) is using ODBC Driver 11 for SQL Server. No idea of the difference between the two, but I have asked the IT department to install the different driver to test.

Thanks

[Minty]

That will probably be the cause. I believe the Native Client is now depreciated.
We switched over to using ODBC Driver 17 For SQL Server quite some time ago. It has stability and performance improvements over the older drivers.

Personally I would update to the latest version and ensure it is used by all end users client machines.

[mduplantis]

You are probably correct, but I tried that driver before and had conflicts with Tableau. I believe that is why we were still using it, but I'll have to try again.

Microsoft ODBC Driver for SQL Server 17.2.0.1 is not compatible with Tableau.


Thank you for all the ideas! Brent

[mduplantis]

I did figure out that this guy is the culprit:

SELECT *   INTO temp_GMSImport
FROM qryImportTransform;

It does not want to insert into the Access table. No idea if the driver impacts.

[Minty]

Out of interest, What are the data types of the original SP results?

I still think it's the driver. It appears to be the only difference.
And check for incomplete office updates.

[mduplantis]

They are mixture of Short Text, Date, Long Text, number and date/time.

Still waiting on the IT department for the drivers. That's my focus now. I'll update the thread once I hear from them.

Thanks

[mduplantis]

Installed the same drivers on the remote server and still get the error. About the only thing left is to try and find a stand alone version of Access that is on the remote server. Professional Plus 2016.

ugh.