Results 1 to 10 of 10
  1. #1
    Join Date
    May 2011
    Posts
    6

    Unanswered: How to Update ODBC linked tables on Access

    Hi,

    I need to know how to Update ODBC linked tables on Access . I know it might be silly Q but I really trying hard.

    Thanks

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    A Table in Access can be accessed through a TableDef object. The TableDef object ccorresponding to a linked table has a Connect property that contains the connection string to the "real" remote database and a SourceTableName property that contains the name of the table in the remote database. This name can be different from the name on the table in the current database. To change the connection of a linked table you need to modify the Connect property, here's an example for a table linked to a SQL Server:
    Code:
    Sub UpdateLinkedTable()
    
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        Dim strConnection As String
        Dim strLinkedTableName As String
        
        'Name of the linked table in the current database.
        strLinkedTableName = "dbo_Containers"
    
        ' Use a DSN data source:
        strConnection = "ODBC;DSN=Sales;Trusted_Connection=Yes;"
        
        ' Use a DSN-less data source:
        strConnection = "ODBC;driver={SQL Server};SERVER=SOLIMAN;DATABASE=Sales;Trusted_Connection=Yes"
        
        Set dbs = CurrentDb
        Set tdf = dbs.TableDefs(strLinkedTableName)
        tdf.Connect = strConnection
        dbs.TableDefs.Refresh
        Set tdf = Nothing
        Set dbs = Nothing
    
    End Sub
    
    Sub CreateLinkedTable()
    
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        Dim strConnection As String
        Dim strLinkedTableName As String
        Dim strRemoteTableName As String
        
        'Name of the linked table in the current database.
        strLinkedTableName = "Sales_Containers"
        ' Name of the table in the SQL Server database.
        strRemoteTableName = "Containers"
    
        ' Using a DSN data source:
        strConnection = "ODBC;DSN=Sales;Trusted_Connection=Yes;"
        
        ' Using a DSN-less data source:
        strConnection = "ODBC;driver={SQL Server};SERVER=SOLIMAN;DATABASE=Sales;Trusted_Connection=Yes"
        
        Set dbs = CurrentDb
        Set tdf = dbs.CreateTableDef
        tdf.Name = strLinkedTableName
        tdf.SourceTableName = strRemoteTableName
        tdf.Connect = strConnection
        dbs.TableDefs.Append tdf
        Set tdf = Nothing
        Set dbs = Nothing
    
    End Sub
    Have a nice day!

  3. #3
    Join Date
    May 2011
    Posts
    6
    Thanks but my tables already connected to the database. and how would i will apply it to my current database links?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What precisely to you want to do? If it's for changing the connection to the database you can use the function UpdateLinkedTable(). If you simply want to refresh an existing link without changing it you can use the RefreshLink method of the TableDef object:
    Code:
    Sub RerefreshLinkedTables()
    
        Dim dbs As DAO.Database
        Dim tdf As TableDef
        
        For Each tdf In dbs.TableDefs
            If Len(tdf.Connect) > 0 Then tdf.RefreshLink
        Next tdf
        Set dbs = Nothing
    
    End Sub
    Have a nice day!

  5. #5
    Join Date
    May 2011
    Posts
    6
    Thanks a lot ..

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  7. #7
    Join Date
    May 2011
    Posts
    6
    sorry to bother, but will this update the table contents?

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The tables are located in the remote database. There is nothing to update with this method. What exactly are you trying to achieve?
    Have a nice day!

  9. #9
    Join Date
    May 2011
    Posts
    6
    Im trying to update table contents?

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    From where?

    1. If you want to update the contents of a table from a form, the simplest way consist in binding the form to the table (or to a query based on the table) - property: RecordSource, then to bind the controls of that form (TextBox controls, etc.) to the columns (fields) of the bound table or query - property: ControlSource.

    2. You can also use a Recordset Object (DAO or ADO) and use the Edit and Update methods to modify the contents of a field:
    Code:
    Sub UpdateField_DAO()
    
        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset
        
        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset("Some_Table", dbOpenDynaset)
        rst.FindFirst "Some_Table_Id = 3"
        rst.Edit
        rst!Some_Data = "ABCD"
        rst.Update
        rst.Close
        Set rst = Nothing
        Set dbs = Nothing
        
    End Sub
    3. You can use the Execute method of a Database object with a SQL statement:
    Code:
    Sub UpdateField_SQL()
    
        Dim dbs As DAO.Database
        Dim strSQL As String
        
        strSQL = "UPDATE Some_Table SET Some_Data = 'ABCD' WHERE Some_Table_Id = 3"
        Set dbs = CurrentDb
        dbs.Execute strSQL, dbFailOnError
        Set dbs = Nothing
        
    End Sub
    You can also build an Update Query, etc.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •