Refresh linked tables to SQL Server using ADOX 1

[glgcag1991]

I need to use ADOX to refresh links to my SQL Server tables as I am using a DSN-less connection. When the user logs on, I want to refresh the links to the SQL Server as well as when I open forms that have queries as the rowsource for objects like comboboxes, etc. just in case the connection to the SQL Server has timed out. I have created a function to refresh the links in DAO but I need this DB to be completely ADO. (I'm converting a DAO db front-end to no longer connect to MDB but SQL Server and DAO won't work so don't ask why- trust me, it's too big of a job to worry if DAO is my problem.) vbajock suggested late-binding DAO, which is an option, but I'm wondering if someone can help me out. Here's some code I found for refreshing links to an Access mdb, but I can't find anything that points to what I should put in the ("Jet OLEDB:Link Datasource") section and how to actually refresh the link. Any help is appreciated. Here's the code:

 'Open the catalog
    objCat.ActiveConnection = CurrentProject.Connection
    
    'Loop through the table collection and refresh the linked tables.
    For Each objTbl In objCat.Tables
        
        ' Check to make sure the table is a linked table.
        If objTbl.Type = "LINK" Then
            strFullName = objTbl.Properties("Jet OLEDB:Link Datasource")
            strFilename = Mid(strFullName, InStrRev(strFullName, "\", _
                            Len(strFullName)) + 1, Len(strFullName))
            strSearchFolder = CurrentProject.Path
            'The following line of code attempts to refresh the link.
            'If the source cannot be found an error is generated.
            'Please note that this code only checks one table to determine
            'whether or not the links are valid.
            objTbl.Properties("Jet OLEDB:Link Datasource") = strFullName
            
            If blnTablesNotLinked = False Then
                Exit Function
            Else
                'Set the search path to the path of the current project.
                'The assumption is that the linked tables are located in subfolders.
                strSearchFile = SearchFile(strFilename, strSearchFolder)
                objTbl.Properties("Jet OLEDB:Link Datasource") = strSearchFile
            End If
        End If
    Next
    
    MsgBox "The links were successfully refreshed!!! "

Also, when I loop through my tables, I don't get tbl.Type = LINK, I get PASS-THROUGH, so that is what I check for. Not sure if that's because I'm using SQLOLEDB instead of ODBC . . .

Any help is appreciated!

 

[vbajock]

I found this, give it a try:

http://www.xtremevbtalk.com/archive/index.php/t-170155.html