Invalid Object Name error when referencing a temporary T-SQL table in query (1 Viewer)

iCyrius

New member
Local time
Today, 12:28
Joined
Apr 5, 2016
Messages
18
Hello,

I will try to do my best explaining this error, please be patient with me.

I have a VBA code that loads values from an Access table into a temporary T-SQL table. The purpose is to do the required math using the server's processor. The logic works perfect if I debug.print the SQL commands from VBA and paste them into a SQL server front end application.

However, if I run the code in access I get a Invalid Object Name pointing to my '#tmpTable' ( this is T-SQL temporary table where I stored the values). There seems to be something about the hashtag or something from that name that Access does not like it. However, it is a requirement in T-SQL in order for me to create the temporary table in that environment. The exact error says Run-time error '3146' ODBC--call failed. [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name '#tmpTable'. (#208)

The code is long and I will have to remove a lot of sensitive information in order to post it here. That is why I have not posted the code. Has any of you experienced something similar? Is the hashtag the issue here?

Thanks for your help!
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:28
Joined
Aug 30, 2003
Messages
36,118
I don't think you can refer to the temp table from Access. I assume you're using a stored procedure? If so, at the end have it return the results via:

SELECT * FROM #tmpTable
 

sonic8

AWF VIP
Local time
Today, 20:28
Joined
Oct 27, 2015
Messages
998
Is the hashtag the issue here?
NO. The issue is the nature of local temporary tables in SQL Server. Most likely the table was already disposed by SQL Server when you run the line of code causing the error and thus the error message (generated by SQL Server) is absolutely correct.
There are many limitations with using SQL Server temp tables from Access. It's better to not do it.
 

Minty

AWF VIP
Local time
Today, 19:28
Joined
Jul 26, 2013
Messages
10,355
There are lot's of issues with refering to a #temp table. Just don't bother.

If you need a table regularly created from a Stored Procedure then either
1)as @CJ_London suggested return the results to a recordset to use in access,
2) create the results to a permanent table in the SQL server and link and query that.
3) Send the results to a local table in access for further process.

The second approach assumes that only one person would be using the results at any given time, so not always best option, unless it's something like a monthly report that only one person runs.
 

sonic8

AWF VIP
Local time
Today, 20:28
Joined
Oct 27, 2015
Messages
998
The second approach assumes that only one person would be using the results at any given time, so not always best option, unless it's something like a monthly report that only one person runs.
This can be solved by adding the username, computername, or any arbitrary identifier to the records and then filter by it.
 

Minty

AWF VIP
Local time
Today, 19:28
Joined
Jul 26, 2013
Messages
10,355
@sonic8 That's a good point and a sound technique - tbh we only use this technique for reporting outputs, which are generally only used by one or two people within an organisation.

More often than not we use a generic SP call to return the data we need if the processing is quicker on the server.
 

iCyrius

New member
Local time
Today, 12:28
Joined
Apr 5, 2016
Messages
18
Good morning,

I appreciate all your feedback even though I do not understand most of them. My organization would grant any permission to save the values I am using into a permanent table. And the only reason I am uploading my values into a #tmpTable is to prevent my Access database from bloating and turning it slow.

My current Access file has multiple permanent tables with values and I only download the necessary SQL weekly data to perform the math in Access. However, this application keeps growing in size. The new calculation I have been asked to do will probably cause this application to double in size since it would require multiple years of data. Thus, I was trying to do perform the math on the server and only bring back the needed results for charting and dashboard reporting.

I am not using any stored procedure. My queries are fairly simple but require a series of multiplication factors that I pre-calculate in Access. These factors are the ones I need to upload.

But based on all your comments, it will not be possible to perform this.

Thanks again for your inputs!
 

iCyrius

New member
Local time
Today, 12:28
Joined
Apr 5, 2016
Messages
18
There are lot's of issues with refering to a #temp table. Just don't bother.

If you need a table regularly created from a Stored Procedure then either
1)as @CJ_London suggested return the results to a recordset to use in access,
2) create the results to a permanent table in the SQL server and link and query that.
3) Send the results to a local table in access for further process.

The second approach assumes that only one person would be using the results at any given time, so not always best option, unless it's something like a monthly report that only one person runs.


Yes. Steps 2 and 3 are my current process.
 

Isaac

Lifelong Learner
Local time
Today, 12:28
Joined
Mar 14, 2017
Messages
8,738
Yeah, you can technically end a stored procedure with a select statement and then exec it in an access pass through query
exec schema.procname 'param', 'param'

and as long as the pass through query's ReturnsRecords property is set to True, it seems to work OK

or, just edit your stored procedure to use a real table. it doesn't have to permanently exist, you can start your proc by dropping it if it exists and then select....into...from to create it
 

Users who are viewing this thread

Top Bottom