[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 208
  • Last Modified:

How to link a SQL SERVER table to access?

On my project I linked an access table from one access DB to another using ADOX. Now I wanted to link a table from SQL SERVER to ACCESS DB. Can anybody help me on how to handle this?

Thanks
0
revathi
Asked:
revathi
  • 4
  • 3
1 Solution
 
dekeldateCommented:
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
0
 
revathiAuthor Commented:
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?
0
 
dekeldateCommented:
hi
This is probably because the path to the ISAM driver in your Windows Registry is invalid, or the ISAM driver does not exist.

check this kb article
http://support.microsoft.com/support/kb/articles/Q209/8/05.ASP
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
revathiAuthor Commented:
Thank you Expert!
0
 
revathiAuthor Commented:
Thank you Expert!
0
 
revathiAuthor Commented:
Thank you Expert!
0
 
dekeldateCommented:
Thank you :)
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now