Refresh Linked Tables Using ADOX/VBA

goss · #1 (**permalink**) 02-02-12, 22:08

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

Quote:

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

Sinndho · #2 (**permalink**) 02-03-12, 02:43

Any reason why you need to use ADO/ADOX libraries?

goss · #3 (**permalink**) 02-03-12, 07:32

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

Sinndho · #4 (**permalink**) 02-03-12, 08:33

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

goss · #5 (**permalink**) 02-03-12, 16:36

Hi Sinndho,

The code is returning this error"

Quote:

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

Sinndho · #6 (**permalink**) 02-04-12, 04:08

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.

goss · #7 (**permalink**) 02-06-12, 11:41

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

Sinndho · #8 (**permalink**) 02-07-12, 12:55

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

goss · #9 (**permalink**) 02-07-12, 13:19

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

Sinndho · #10 (**permalink**) 02-07-12, 15:02

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.

goss · #11 (**permalink**) 02-09-12, 13:26

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:

Quote:

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

Sinndho · #12 (**permalink**) 02-09-12, 15:02

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

Internet Services

Web Development

Digital Marketing

Consumer Tech