Problem Linking to a temporary table in SQL Server

[akc42]

I am attempting to port an Access front_end/Access back_end application to Access front_end/Sql Server back_end.

One small part of this application uploads a CSV file into a local table in the front end (using DoCmd.TransferText) and then proceeds to process it to merge the results into several back_end tables. This is the part I am trying to convert.

Because I can do the complex merging operation as a stored procedure on SQL Server, I want to replace the local front end table with a linked table in Access, linked to a temporary table that I create on a connection (it has to be temporary as this is a multi-user environment and I don't want the users tripping over each other).

I am linking all my permanent tables during start up using the AttachDSNLessTable routine that Microsoft have in their knowledge base for this sort of thing. This works great.

However, I try to do the same with this temporary table after I have created it and it gives an error at the point where i try and append the new TableDef to the current tabledefs. The Err.Description is "Could not find installable ISAM." Microsoft's explanation of this error doesn't parse in the context of SQL Server.

Here is the code in question (getStrConn is a function which returns the connection string - either cached, or derived from one of the already created tabledefs from the permanent tables)

         Set conn = New ADODB.Connection
        conn.Open getStrConn

SQL = CODE FOR CREATING TABLE - THIS FORUM WON'T LET ME POST IT AS IT THINKS ITS E_MAIL ADDRESSES

It is something like
SQL = CREATE TABLE dbo.#WebSiteLeads(leadID int IDENTITY(1,1) PRIMARY KEY CLUSTERED, ...
         
        conn.Execute SQL 
         
        For Each td In CurrentDb.TableDefs 
            If td.name = "WebsiteLeads" Then 
                CurrentDb.TableDefs.Delete "WebsiteLeads" 
            End If 
        Next 
        'link to the table just created 
        Set td = CurrentDb.CreateTableDef("WebsiteLeads", dbAttachSavePWD, "[dbo].[#WebSiteLeads]", conn) 
 
        CurrentDb.TableDefs.Append td 
        importProgress = 0.1    'Update Progress bar 
        DoEvents 
         
        'Import our text file 
        DoCmd.TransferText acImportDelim, "Leads Import v2", "WebsiteLeads", txtFileName 
        importProgress = 0.3    'Update Progress bar 
        DoEvents 
 
        'We are going to do the rest as a stored procedure 
 
        Set leadsRS = conn.Execute("EXEC dbo.LeadsImport;") 
        importProgress = 0.9    'Update Progress bar 
        DoEvents

The line that fails is "CurrentDb.TableDefs.Append td"

I don't know what is wrong, or how to work around the problem. Any ideas?

 

[akc42]

Just wanted to add that in Management Studio, the temporary table can be seen in the tempdb, but any attempt to display the columns gives an error message.

This seems to be true of any temporary table - I created one using a query in the Management Studio and it also failed when I attempted to display the columns