How to link a SQL SERVER table to access?

You could use something like this

I have used a DSN as the ODBC connection string to connect to the Pubs sample database on SQL Server.

Sub CreateLinkSQL()

   dim strTargetDB As String, _
       strProviderString As String, _
       strSourceTbl As String, _
       strLinkTblName As String)

   strTargetDB = "C:\Program Files\Microsoft zffice\Office\Samples\Northwind.mdb"
   strProviderString = "ODBC;DSN=Publishers;UID=sa;PWD=;DATABASE=pubs;"
   strSourceTbl = "dbo.Authors"
   strLinkTblName = "LinkedODBC"

   Dim catDB As ADOX.Catalog
   Dim tblLink As ADOX.Table

   Set catDB = New ADOX.Catalog
   ' Open a Catalog on the database in which to create the link.
   catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
         "Data Source=" & strTargetDB

   Set tblLink = New ADOX.Table
   With tblLink
      ' Name the new Table and set its ParentCatalog property to the
      ' open Catalog to allow access to the Properties collection.
      .Name = strLinkTblAs
      Set .ParentCatalog = catDB

      ' Set the properties to create the link.
      .Properties("Jet OLEDB:Create Link") = True
      .Properties("Jet OLEDB:Link Provider String") = strProviderString
      .Properties("Jet OLEDB:Remote Table Name") = strLinkTbl
   End With

   ' Append the table to the Tables collection.
   catDB.Tables.Append tblLink

   Set catDB = Nothing
End Sub


hope this helps

Thank you expert!. While I am using 'Link provider string' property I am getting error stating 'Couldnot find installable ISAM' . I don't know the reason of this error. But if I use 'Link datasource' property I am not getting any such errors. Can you tell me why is this happening?

Thank you Expert!

Thank you Expert!

Thank you Expert!

Thank you :)