Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1

    Unanswered: Refresh Linked Tables Using ADOX/VBA

    Hi all,

    Trying to use ADOX/VBA in Excel to refresh Access Table linked to Oracle Table.

    I tried adoTableType "LINKED", which did not work.
    I then ran this bit of code
    Code:
    Debug.Print adoTbl.Name & "  " & adoTbl.Type
    Which told me the table type was "PASS THROUGH"

    I tried modifying my code for "PASS THROUGH",but now I receive this error
    Run-time error '-2147467259 (80004005)':
    ODBC--call failed.
    Any ideas what else I can look at or try to get this resolved?
    Full code below
    thx
    w
    Code:
    Sub RefreshLinks()
        'Comments:  1.)Refresh linked tables
        '           2.)Set Reference To Microsoft ADO Ext. 2.8 for DDL and Security
        '           3.)Microsoft ActiveX Data Objects 2.7 Library
        '
        'Date       Developer       Action
        '---------------------------------------------
        '02/01/12   ws              Created
        '02/02/12   ws              Added ADO Connection  String
        '02/02/12   ws              Added Debug.Print inside of For Loop
        '02/02/12   ws              Changed adoTblType from "LINK" to "PASS-THROUGH"
    
        Dim adoCn As ADODB.Connection
        Dim adoCat As New ADOX.Catalog
        Dim adoTbl As New ADOX.Table
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim db_file As String
        Dim strDbPath As String
        Dim strDb As String
        Dim strDbTable As String
        Dim strOraUID As String
        Dim strOraPWD As String
    
        'Initialize
            With Application
                .ScreenUpdating = False
                .Calculation = xlCalculationManual
                .DisplayAlerts = False
            End With
            Set wb = ThisWorkbook
            Set ws = wb.Worksheets("RefreshLinks")
            
            With ws
                strDbPath = .Range("B4")
                strDb = .Range("B5")
                strDbTable = .Range("B6")
                strOraUID = .Range("B7")
                strOraPWD = .Range("B8")
            End With
            
            If Right$(strDbPath, 1) <> "\" Then strDbPath = strDbPath & _
                "\"
                
            'ADO Connection object
            Set adoCn = New ADODB.Connection
            
            'Connection String
            adoCn.ConnectionString = _
            "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=" & strDbPath & strDb & ";" & _
            "Persist Security Info=False"
            
            'Open the connection
            adoCn.Open
            
            'ADOX Objects
            Set adoCat = New ADOX.Catalog
            Set adoCat.ActiveConnection = adoCn
            Set adoTbl.ParentCatalog = adoCat
    
            'Refresh Link...
            For Each adoTbl In adoCat.Tables
                Debug.Print adoTbl.Name & "  " & adoTbl.Type
                If adoTbl.Type = "PASS-THROUGH" And (adoTbl.Name = strDbTable) Then
                    adoTbl.Properties("Jet OLEDB:Link Provider String") = "MS Access;Pwd=" & strOraPWD
                    adoTbl.Properties("Jet OLEDB:Link Datasource") = strDbPath & strDb
                End If
            Next
    
        'Tidy up
            Set adoCn = Nothing
            Set adoCat = Nothing
            Set adoTbl = Nothing
            Set wb = Nothing
            Set ws = Nothing
            
            With Application
                .ScreenUpdating = False
                .Calculation = xlCalculationManual
                .DisplayAlerts = False
            End With
            
    End Sub

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Any reason why you need to use ADO/ADOX libraries?
    Have a nice day!

  3. #3
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    If there is another way I am open to suggestions.
    My only criteria is that it the refresh must be performed from Excel.

    thx
    w

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could use:
    Code:
    Sub RefreshLinks()
    '
    ' Note: A reference to the Microsoft DAO 3.x (usually 3.6) Object Library is needed.
    ' ----  Menu "Tools" --> "References" --> "Microsoft DAO 3.6 Object Library"
    '
        Dim dbe As DAO.DBEngine
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        Dim strDBName As String
    
        Set dbe = New DAO.DBEngine
        Set dbs = dbe.OpenDatabase("<The path to the Access database (.mdb) goes here>")
        For Each tdf In dbs.TableDefs
            If tdf.Connect <> "" Then    ' Something in the Connect property means it's an attached (linked) table.
                tdf.Connect = "<The Connection string goes here>"
                tdf.RefreshLink
            End If
        Next tdf
        dbs.Close
        Set dbs = Nothing
        Set dbe = Nothing
        
    End Sub
    Have a nice day!

  5. #5
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    Hi Sinndho,

    The code is returning this error"
    Run-time error '3443
    Unrecognized database format strDBPath & strDB
    The database is .accdb format

    Full code below
    thx
    w
    Code:
    Option Explicit
    
    Sub RefreshLinksDAO()
        'Comments:  1.)Refresh linked tables
        '           2.)Set Reference To Microsoft DAO 3.6 Object Library
        
        
        'Resources
        '====================
        'DAO Refresh Linked tables ->   http://bytes.com/topic/access/answers/635203-how-programmatically-refresh-linked-tables
        '
        'Date       Developer       Action
        '---------------------------------------------
        '02/01/12   ws              Created
        '02/02/12   ws              Added ADO Connection  String
        '02/02/12   ws              Added Debug.Print inside of For Loop
        '02/02/12   ws              Changed adoTblType from "LINK" to "PASS-THROUGH"
        '02/03/2012 ws              Modified to use DAO instead of ADOX
    
        Dim dbe As DAO.DBEngine
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        Dim db As Database
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim strConnect As String
        Dim strDbPath As String
        Dim strDb As String
        Dim strDBFile As String
        Dim strDbTable As String
        Dim strOraUID As String
        Dim strOraPWD As String
    
        'Initialize
            With Application
                .ScreenUpdating = False
                .Calculation = xlCalculationManual
                .DisplayAlerts = False
            End With
            Set wb = ThisWorkbook
            Set ws = wb.Worksheets("RefreshLinks")
            
            With ws
                strDbPath = .Range("B4")
                strDb = .Range("B5")
                strDbTable = .Range("B6")
                strOraUID = .Range("B7")
                strOraPWD = .Range("B8")
            End With
            
            If Right$(strDbPath, 1) <> "\" Then strDbPath = strDbPath & _
                "\"
            strDBFile = strDbPath & strDb
                
            'DAO Connection object
            Set dbe = New DAO.DBEngine
            Set dbs = dbe.OpenDatabase(strDbPath & strDb)
    
            'Connection String
            strConnect = _
            "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=" & strDbPath & strDb & ";" & _
            "UID=" & strOraUID & ";" & _
            "PWD=" & strOraPWD
    
            For Each tdf In dbs.TableDefs
                If InStr(1, tdf.Connect, strDb) > 0 Then
                    With tdf
                        .Connect = strConnect
                        .RefreshLink
                    End With
                End If
            Next
    
        'Tidy up
            dbs.Close
            Set dbs = Nothing
            Set dbe = Nothing
            Set wb = Nothing
            Set ws = Nothing
            
            With Application
                .ScreenUpdating = False
                .Calculation = xlCalculationManual
                .DisplayAlerts = False
            End With
            
    End Sub

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Contrarily to what's mentioned in the comment (which is true for Office 2003), the reference that should be used for Office 2010 is: "Microsoft Office 14 access database engine object library".
    Sorry about that.
    Have a nice day!

  7. #7
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    Thanks Sinndho,

    I tried updating
    Code:
    "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    to
    Code:
    "Provider=Microsoft.ACE.OLEDB.14.0;" & _
    I also pointed to a different Access database in 2003 format (.mdb), should be OK, everything I've read ACE is backward compatible (accdb as well as mdb)

    I ran the macro and received no errors, but It also does not appear to be updating the tables.

    I added this line to for some possible troubleshooting
    Code:
    Debug.Print tdf.Name & " " & tdf.Attributes & " " & tdf.LastUpdated
    This line tells me the last update of the 2 tables I am trying to refresh were on 01/24/2012 and 1/23/2012 respectively. It also tells me the 2 tables have an attribute in common that none of the other tables in the db have. Attribute = 536870912. I confess I do not know what this attribute means.

    Revised code below
    Thx
    w
    Code:
    Option Explicit
    
    Sub RefreshLinksDAO()
        'Comments:  1.)Refresh linked tables
        '           2.)Set Reference To Microsoft DAO 3.6 Object Library
        
        
        'Resources
        '====================
        'DAO Refresh Linked tables ->   http://bytes.com/topic/access/answers/635203-how-programmatically-refresh-linked-tables
        '
        'Date       Developer       Action
        '---------------------------------------------
        '02/01/12   ws              Created
        '02/02/12   ws              Added ADO Connection  String
        '02/02/12   ws              Added Debug.Print inside of For Loop
        '02/02/12   ws              Changed adoTblType from "LINK" to "PASS-THROUGH"
        '02/03/12   ws              Modified to use DAO instead of ADOX
    
        Dim dbe As DAO.DBEngine
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        Dim db As Database
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim strConnect As String
        Dim strDbPath As String
        Dim strDb As String
        Dim strDBFile As String
        Dim strOraUID As String
        Dim strOraPWD As String
    
        'Initialize
            With Application
                .ScreenUpdating = False
                .Calculation = xlCalculationManual
                .DisplayAlerts = False
            End With
            Set wb = ThisWorkbook
            Set ws = wb.Worksheets("Dash")
            
            With ws
                strDbPath = .Range("B4")
                strDb = .Range("B5")
                strOraUID = .Range("B9")
                strOraPWD = .Range("B10")
            End With
            
            If Right$(strDbPath, 1) <> "\" Then strDbPath = strDbPath & _
                "\"
            strDBFile = strDbPath & strDb
                
            'DAO Connection object
            Set dbe = New DAO.DBEngine
            Set dbs = dbe.OpenDatabase(strDbPath & strDb)
    
            'Connection String
            strConnect = _
            "Provider=Microsoft.ACE.OLEDB.14.0;" & _
            "Data Source=" & strDbPath & strDb & ";" & _
            "UID=" & strOraUID & ";" & _
            "PWD=" & strOraPWD
    
            For Each tdf In dbs.TableDefs
                Debug.Print tdf.Name & " " & tdf.Attributes & " " & tdf.LastUpdated
                If InStr(1, tdf.Connect, strDb) > 0 Then
                    With tdf
                        .Connect = strConnect
                        .RefreshLink
                    End With
                End If
            Next
    
        'Tidy up
            dbs.Close
            Set dbs = Nothing
            Set dbe = Nothing
            Set tdf = Nothing
            Set wb = Nothing
            Set ws = Nothing
            
            With Application
                .ScreenUpdating = False
                .Calculation = xlCalculationManual
                .DisplayAlerts = False
            End With
            
    End Sub

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The attibutes of a DAO Tabledef are constants defined in an enumeration: DAO.TableDefAttributeEnum, as follows:

    Const dbAttachedODBC = 536870912 (&H20000000)
    Const dbAttachedTable = 1073741824 (&H40000000)
    Const dbAttachExclusive = 65536 (&H10000)
    Const dbAttachSavePWD = 131072 (&H20000)
    Const dbHiddenObject = 1
    Const dbSystemObject = -2147483646 (&H80000002)

    What makes you say that the links are not refreshed?

    Note: See also: TableDef Object
    Have a nice day!

  9. #9
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    Hi Sinndho,

    After running the code, I checked the properties of the 2 Linked ODBC Tables. The modified date is 1/24/2012.

    I then ran the Linked Table Manager in Access, ticked my 2 tables, and clicked ok.

    Then I right-clicked on the table again and selected "Table Properties"
    Now both the Created and Modified date read 2/7/2012.

    So if the code was refreshing the tables I would expect at least the Modified date to update, apparently the Created date as well?

    thx
    w

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    That's an interesting point. However, refreshing the link of an attached table does not seem to change neither the Modified nor the Created date of such a table. I tested with a table linked to a SQL Server in my case, like this:

    1. The original table was created using the Access interface for linking external tables (through a defined ODBC Data Source -DSN-). The original Connect property was:
    "ODBC;DSN=Sales;Trusted_Connection=Yes;APP=Microsof t Office 2003;WSID=SOLIMAN;DATABASE=Sales;"

    2. With the code I posted previously, I re-linked the table, using another connection string with no DSN defined, like this:
    "ODBC;driver={SQL Server};SERVER=SOLIMAN;DATABASE=Sales;Trusted_Conn ection=Yes;"

    When I checked afterwards, the Created and Modified dates of the attached table were not changed, although its Connect property actually was.
    Have a nice day!

  11. #11
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    Hi Sinndho,

    I revised my code somewhat and created a few more challenges.
    I was trying to get a warm feeling that the refresh is definitely working by checking the record count after the refresh.

    When I run the code, first I get a prompt to "Select Data Source". Should not happen, the connection string should take care of this.

    I clicked on Machine Data Source and chose my Oracle Data Source.
    I eventually received a "Oracle ODBC Driver Connect" prompt which I enter user name and password. Click "OK"

    Chugs for awhile and eventually I receive:
    Run-time error '3146':
    ODBC--call failed.
    I tried changing my connection string from
    Code:
    strConnect = _
            "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=" & strDbPath & strDb & ";" & _
            "UID=" & strOraUID & ";" & _
            "PWD=" & strOraPWD
    Code:
    strConnect = "Provider=OraOLEDB.Oracle;" & _
                "Data Source=(DESCRIPTION=(CID=myCID)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=myHost)(PORT=1521)))(CONNECT_DATA=(SID=mySID)(SERVER=SHARED)));" & _
                "User Id=" & strOraUID & ";" & _
                "Password=" & strOraPWD
    But I still receive the DSN and Oracle ODBC Driver Connect prompts and eventually the ODBC--call faled

    Full code below,
    Thx
    w

    Code:
    Option Explicit
    
    Sub RefreshLinksDAO()
        'Comments:  1.)Refresh linked tables
        '           2.)Set Reference To Microsoft DAO 3.6 Object Library
        
        
        'Resources
        '====================
        'DAO Refresh Linked tables ->   http://bytes.com/topic/access/answers/635203-how-programmatically-refresh-linked-tables
        '
        'Date       Developer       Action
        '---------------------------------------------
        '02/01/12   ws              Created
        '02/02/12   ws              Added ADO Connection  String
        '02/02/12   ws              Added Debug.Print inside of For Loop
        '02/02/12   ws              Changed adoTblType from "LINK" to "PASS-THROUGH"
        '02/03/12   ws              Modified to use DAO instead of ADOX
        '02/06/12   ws              Added debug.print line
        '02/06/12   ws              Changed provider from ACE 12 to ACE 14
        '02/09/12   ws              Added recordcount
        '02/09/12   ws              Moved debug.print so only linked tables would print
        
    
        Dim dbe As DAO.DBEngine
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        Dim rst As DAO.Recordset
        Dim db As Database
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim strConnect As String
        Dim strDbPath As String
        Dim strDb As String
        Dim strDBFile As String
        Dim strOraUID As String
        Dim strOraPWD As String
        Dim strSQL As String
        Dim lngRecCount As Long
        
        'Initialize
            With Application
                .ScreenUpdating = False
                .Calculation = xlCalculationManual
                .DisplayAlerts = False
            End With
            Set wb = ThisWorkbook
            Set ws = wb.Worksheets("Dash")
            
            With ws
                strDbPath = .Range("B4")
                strDb = .Range("B5")
                strOraUID = .Range("B9")
                strOraPWD = .Range("B10")
            End With
            
            If Right$(strDbPath, 1) <> "\" Then strDbPath = strDbPath & _
                "\"
            strDBFile = strDbPath & strDb
                
            'DAO Connection object
            Set dbe = New DAO.DBEngine
            Set dbs = dbe.OpenDatabase(strDbPath & strDb)
    
            'Connection String
    '        strConnect = _
    '        "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    '        "Data Source=" & strDbPath & strDb & ";" & _
    '        "UID=" & strOraUID & ";" & _
    '        "PWD=" & strOraPWD
            
            strConnect = "Provider=OraOLEDB.Oracle;" & _
                "Data Source=(DESCRIPTION=(CID=myCID)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=myHost)(PORT=1521)))(CONNECT_DATA=(SID=mySID)(SERVER=SHARED)));" & _
                "User Id=" & strOraUID & ";" & _
                "Password=" & strOraPWD
            
            For Each tdf In dbs.TableDefs
                Debug.Print tdf.Name & " " & tdf.Attributes & " " & tdf.LastUpdated
                If tdf.Attributes = "536870912" Then
    '            If InStr(1, tdf.Connect, strDb) > 0 Then
                    With tdf
                        .Connect = strConnect
                        .RefreshLink
                        strSQL = "SELECT * FROM " & tdf.Name
                        Set rst = tdf.OpenRecordset(strSQL)
                        lngRecCount = rst.RecordCount
                        Debug.Print .Name & " " & .Attributes & " " & .LastUpdated & " Records:" & lngRecCount
                    End With
                End If
            Next
    
        'Tidy up
            On Error Resume Next
                dbs.Close
                rst.Close
                Set dbs = Nothing
                Set dbe = Nothing
                Set tdf = Nothing
                Set rst = Nothing
                Set wb = Nothing
                Set ws = Nothing
            
            With Application
                .ScreenUpdating = False
                .Calculation = xlCalculationManual
                .DisplayAlerts = False
            End With
            
    End Sub
    Last edited by goss; 02-09-12 at 14:27. Reason: typos

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If the ODBC layer prompts for something, it means that an element in the connection string is missing or is incorrect. Unfortunately I have no experience related to an Oracle server. You should probably have a look at: Oracle Connection String Samples - ConnectionStrings.com
    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
  •