CODEWITHSUNDEEP
sql-serverms-access
akc42
akc42

Reputation: 5001

How can I link an Access tabledef to temporary table in SQLServer

I am porting a MS Access front end/backend application to be MS Access front end/sql server backend.

One part of this application (in the original form) uses VB to import a CSV file into a table in the front end, and then run various SQL statements to merge the contents of this table into some backend tables.

I figure in the new environment, because it more efficient to perform the merge process in a stored procedure, that my strategy should be

  1. Open a connection to the backend
  2. Create a temporary table on this connection
  3. Create a tabledef in VB and attempt to connect it to this temporary table
  4. Use DoCmd.TransferText (as I do in the original) to import the CSV file into the table linked to by the table def
  5. Execute a stored procedure on the open connection which merges the temporary table data into the permanent tables on the backend.

I think I am successfully performing steps 1 and 2, but I am not sure. I can see the temp table in SQL Server Management Studio, but attempting to open the columns fails with an error (it also fails with the same error when I use Management Studio to create a temp table, so I think this a Management Studio issue).

I am using the same code to perform step 3 as I use to link in all the permanent tables when I start up the application, but its this bit that is not working. Finally, I have proven step 5 works by running it against a non temporary table.

The code I use for for this is as follows

    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]", getStrConn)

    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 Access stored procedure

    Set leadsRS = conn.Execute("EXEC dbo.LeadsImport;")
    importProgress = 0.9    'Update Progress bar
    DoEvents

But it fails at 

        CurrentDb.TableDefs.Append td

with an error message "Could not find installable ISAM."

What is this error message, and why am I getting it

Upvotes: 0

Views: 2418

Answers (1)

David-W-Fenton
David-W-Fenton

Reputation: 23067

Another alternative would be to not use a linked table at all, but instead have a passthrough query that returns the temp table. You'd likely have to write it on the fly, but once it's returning the correct temp table, it can be used as the Recordsource of a form or report.

I've not infrequently created saved queries that replace linked tables, though I've never done it with a passthrough connecting to a temp table.

Upvotes: 1

Related Questions