Linked table ms access 2010 change connection string

by

To print all connection strings:

Dim tdf As TableDef
Dim db As Database

    Set db = CurrentDb

    For Each tdf In CurrentDb.TableDefs
        If tdf.Connect <> vbNullString Then
           Debug.Print tdf.Name; " -- "; tdf.SourceTableName; " -- "; tdf.Connect
        End If
    Next

To create a linked table:

With CurrentDb
    ''If the table does not have a unique index, you will need to create one
    ''if you wish to update.
    Set tdf = .CreateTableDef("LocalName")
    tdf.Connect = "ODBC;DRIVER=SQL Server;SERVER=10.0.0.1;" _
      & "UID=testdb;APP=Microsoft Office 2003;WSID=abc;TABLE=dbo.user"
    tdf.SourceTableName = "TABLE_NAME"
    .TableDefs.Append tdf
    .TableDefs.Refresh
End With

To change a link:

    Set db = CurrentDB
    Set tdf = db.TableDefs("MyTable")
    tdf.Connect = "ODBC;DRIVER=SQL Server;SERVER=10.0.0.1;" _
      & "UID=testdb;APP=Microsoft Office 2003;WSID=abc;TABLE=dbo.user"
    tdf.RefreshLink

More Related Contents:

  1. How do i add percentage an Access query?
  2. How does one decompile and recompile a database application?
  3. How do you use version control with Access development?
  4. Autonumber value of last inserted row – MS Access / VBA
  5. Microsoft Access condense multiple lines in a table
  6. How to get random record from MS Access database
  7. Code to loop through all records in MS Access
  8. Syntax error in insert into statement – what’s the error?
  9. Difference between Microsoft.Jet.OleDb and Microsoft.Ace.OleDb
  10. How do I force VBA/Access to require variables to be defined?
  11. Why is my query not updateable?
  12. Microsoft Access DBEngine.OpenDatabase() breaks for Network-Paths – Error 3050
  13. Is it possible to create a recursive query in Access?
  14. VBA: Difference in two ways of declaring a new object? (Trying to understand why my solution works)
  15. What is the equivalent of Select Case in Access SQL?
  16. How do I access the selected rows in Access?
  17. Access 2007 – Left Join to a query returns #Error instead of Null
  18. Pivot Query in MS Access
  19. insert query with sequential primary key
  20. how to show query results in a datasheet in the same form?
  21. Setting up an MS-Access DB for multi-user access
  22. MS Access prepared statements
  23. How can I modify a saved Microsoft Access 2007 or 2010 Import Specification?
  24. Null values for variables in VBA
  25. Custom row source for combo box in continuous form in Access
  26. MS Access query returning Chinese characters – possible table corruption?
  27. Using a centralized VBA module in multiple Access databases
  28. How to execute a query in ms-access in VBA code?
  29. How to add a menu item to the default right click context menu
  30. Force all users to disconnect from 2010 Access backend database

Leave a Comment