Problem linking to temporary tables from Access

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

Problem linking to temporary tables from Access

Author

Topic

akc42
Starting Member

6 Posts

Posted - 2011-04-28 : 03:40:34

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

        'First create a temporary table on the server for the web site leads

        SQL = "CREATE TABLE [dbo].[#WebSiteLeads]("

        SQL = SQL & "[leadID] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED,[Title] [nvarchar](255) NULL,[Firstname] [nvarchar](50) NULL,"

        SQL = SQL & "[Lastname] [nvarchar](50) NULL,[Sex] [nvarchar](10) NULL,[House] [nvarchar](50) NULL,"

        SQL = SQL & "[Address1] [nvarchar](50) NULL,[Address2] [nvarchar](50) NULL,[Street] [nvarchar](50) NULL,"

        SQL = SQL & "[Town_City] [nvarchar](50) NULL,[County] [nvarchar](50) NULL,[Postcode] [nvarchar](10) NULL,"

        SQL = SQL & "[Email] [nvarchar](50) NULL,[Allow_email] [nvarchar](5) NULL,[Telephone] [nvarchar](20) NULL,"

        SQL = SQL & "[Allow_tel] [nvarchar](5) NULL,[Cons_period] [nvarchar](20) NULL,[When] [nvarchar](20) NULL,"

        SQL = SQL & "[Procedure] [nvarchar](20) NULL,[Consultation] [nvarchar](10) NULL,[Info_pack] [nvarchar](10) NULL,"

        SQL = SQL & "[Source] [nvarchar](20) NULL,[Further_info] [nvarchar](255) NULL,[Callback] [nvarchar](50) NULL,"

        SQL = SQL & "[Date_added] [nvarchar](30) NULL,[Date_added_dt] [datetime] NULL,[Callback_range] [tinyint] NULL,"

        SQL = SQL & "[UcFname] [nvarchar](1) NULL,[UcLname] [nvarchar](50) NULL,[UcPcode] [nvarchar](10) NULL);"

        
        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?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-28 : 04:18:12

Have you checked that the table is there? I suspect it may be drooped after the create statement as it goes out of scope.
You could do a similar thing with a permanent table by including the spid as part of the name if you are keeping the same connection open (if not your temp table can't work either).

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources