title: How to link Access table to SQL Server with VBA
body: <p>I am trying to create a linked (<em>or imported</em>) table in Access with a SQL Server backend. Basically the business users periodically need a copy of the table <strong>[SQL Rulesnew]</strong> (yes, with the space, sigh) so we want to give them a little Access 2003 tool that will do the job on demand.</p>
  
  <p>I did try using <code>Docmd.TransferDataBase acTable</code> but no luck</p>
  
  <p>here is the code I am using:</p>
  
  <pre><code>Sub getData()
  
  Dim sConnStr As String
  Dim oTable As TableDef
  Dim sDestinationTable As String
  Dim dbs As Database
  Dim tbl As DAO.TableDef
  Dim tblLinked As DAO.TableDef       
  
      sDestinationTable = "SQL Rulesnew"
      Set dbs = CurrentDb
  
      ' source table name has a SPACE (rolleyes!)
      CurrentDb.CreateTableDef sDestinationTable  
  
      ' got the below from a Data Link File (UDL)
      sConnStr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MYDBNAME;Data Source=MYSERVERNAME"
  
      ' the below also failed!
      'DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;Driver={SQL Server};Server=Fos;Database=Hermes_Rep;Trusted_Connection=Yes", acTable, "[Report SQLRulesnew]", "SQLRules" &amp; VBA.Format(Now, "ddmmyyyy")
  
      'If DCount("*", "MSysObjects", "[Name]='[SQL Rulesnew]' AND [Type] In (1, 4, 6)") &gt; 0 Then
      If IsTable(sDestinationTable) Then
         DoCmd.DeleteObject acTable, sDestinationTable
      End If
  
      Set tblLinked = dbs.CreateTableDef(sDestinationTable)
      Debug.Print "Linking the " &amp; sDestinationTable
      tblLinked.Connect = sConnStr
      tblLinked.SourceTableName = sDestinationTable
      dbs.TableDefs.Append tblLinked
      tblLinked.RefreshLink
  
  End Sub
  
  Function IsTable(sTblName As String) As Boolean
      'does table exists and work ?
      'note: finding the name in the TableDefs collection is not enough,
      '      since the backend might be invalid or missing
  Dim x
      On Error GoTo Coventry
      x = DCount("*", sTblName)
      IsTable = True
      Exit Function
  
  Coventry:
      Debug.Print Now, sTblName, Err.Number, Err.Description
      IsTable = False
  End Function
  </code></pre>
  
  <p>unfortunately I get an error <em>could not find installable ISAM</em> on the line <code>dbs.TableDefs.Append tblLinked</code></p>
  
  <p>what should I do?</p>
  
  <p>thanks
  Philip</p>