refreshing linked table in Access 2000

The attached code is what I am using to refresh my linked tables.  
My question:
Should catDB.ActiveConnection = the connection string to the database containing the linked table or should it be the database connection object?

Also tblLink.Properties("Jet OLEDB:Link Datasource").Value I have seen different examples setting this to different things.  What should it be set for in my case?
Sub RefreshClientLinkedTables()
 
        Dim catDB As ADOX.Catalog
        Dim tblLink As ADOX.Table
 
        ' Open a Catalog object on the database in which to refresh links.
        catDB = New ADOX.Catalog
        catDB.ActiveConnection = OLEConn  'connectionString
 
        For Each tblLink In catDB.Tables
            ' Check to make sure table is a linked table.
            If tblLink.Type = "LINK" Then
                tblLink.Properties("Jet OLEDB:Link Datasource").Value = True       'SQLConn.ConnectionString
            End If
        Next
 
        catDB = Nothing
        tblLink = Nothing
    End Sub

Open in new window

NevSoFlyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

vb_jonasCommented:
Hi, yes active connection is the mdb wich contains the link:
 catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\my_mdb.mdb"

You will need to set the Link DataSource to the proper string:

the provider string depends on your datasource, could be
 tblLink.Properties("Jet OLEDB:Link Datasource")= "ODBC;Description=myDB;DRIVER=SQL Server;SERVER=MyServer;APP=Microsoft Access;WSID=MyWorkStationID;DATABASE=MyDB;TABLE=MyTable"

MSDN:
"An easy way to determine the Jet connection string required to link an external data source is to create a linked table by using the Access user interface (File menu, Get External Data submenu, Link Tables command). Then open the linked table in Design view and display the Table Properties dialog box (View menu, Properties command). The Description property of a linked table shows the connection string used to link the table. You can display a long connection string by clicking in the Description property and then pressing SHIFT+F2."

(http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_DB/Q_20700664.html)

Hope this helps
regards
Jonas
0
vb_jonasCommented:
You might want to run the code from within Access first, to get it running and see the datasource strings. Note that if your linking to another mdb, its enough with the physical path.

' This code needs a reference to Microsoft ADO Ext.

Sub CheckLinks

Dim ax As New ADOX.Catalog
Set ax.ActiveConnection = CurrentProject.Connection

Dim td As ADOX.Table

For Each td In ax.Tables
    If td.Type = "Link" Then
        Debug.Print td.Properties("Jet OLEDB:Link Datasource")
    End If
Next

End Sub
0
NevSoFlyAuthor Commented:
I'm guessing that the workstation ID is the ID of the system that the client app is on.or server?

The client app is using an Access DB.  How would I find the workstation ID for the client system?  

Also the server is SQL Server 2000 so I would need a password.  Could you shoe me the syntax for that?

Lastly the string that I copied included the name of the linked table in the Access DB.  Do I need to name every table?  By the looks of the original code it seems like the code is stepping thru all the tables.

Here is the string that I got from the properties command:
ODBC;Description=connects to COFSE.REVs;DRIVER=SQL Server;SERVER=FREEDOM-TEST;APP=Microsoft Office XP;WSID=X2100-2864;DATABASE=COFSE;Trusted_Connection=Yes;TABLE=dbo.tblASSEMBLY
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

vb_jonasCommented:
Since the table names already are linked, the only thing you are supposed to do is giving the connectionstring:

Sub RefreshClientLinkedTables()

    Dim catDB As ADOX.Catalog
    Dim tblLink As ADOX.Table

    ' Open a Catalog object on the database in which to refresh links.
    Set catDB = New ADOX.Catalog
   
    Dim OLEConn As String
    OLEConn = "ODBC;" & _
           "Driver={SQL Server};" & _
           "Server=FREEDOM-TEST;" & _
           "Database=COFSE;" & _
           "Uid=sa_user;" & _
           "Pwd=sa_password;"
   
    catDB.ActiveConnection = OLEConn  'connectionString

    For Each tblLink In catDB.Tables
        ' Check to make sure table is a linked table.
        If tblLink.Type = "LINK" Then
            tblLink.Properties("Jet OLEDB:Link Datasource") = OLEConn
        End If
    Next

    Set catDB = Nothing
    Set tblLink = Nothing

End Sub


Hope it works this time!
0
NevSoFlyAuthor Commented:
I can believe that this is so difficult.  The line
catDB.ActiveConnection = connectionString
gives an error:
"Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another."

I also had to change:
tblLink.Properties("Jet OLEDB:Link Datasource") = OLEConn
to:
tblLink.Properties("Jet OLEDB:Link Datasource").value = OLEConn
because it gave an readonly error.
0
vb_jonasCommented:
Oh, no, I missed - catdb is the mdb, so change catDb.ActiveConnection to this:
catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\my_mdb.mdb"
0
NevSoFlyAuthor Commented:
I did as you suggested and changed it to:
catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & My.Application.Info.DirectoryPath & "\Data2.mdb"

This gives me the COMException was unhandled Error:
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

Could this be because I also need to add the DB password to catDB?
0
vb_jonasCommented:
Hi, yes if you use a password you'll have to add that to your connectionstring,
http://www.connectionstrings.com/?carrier=access
0
NevSoFlyAuthor Commented:
I still get the same error.  Have you gotten this to work on any of your apps?  Is there another way of doing this?
0
vb_jonasCommented:
Yes I have, more than once. But there are always small differences. One current project I am working on use an mdb without security and a SQL Server 2005 with trusted security. There I run refresh from within VBA, but to help you out I tried activating it from vb.net also.

Did you try to run the refresh-routine from within Access VBA? Set the catDb.Activeconnetion = CurrentProject.Connection.
0
vb_jonasCommented:
Here's a code I use in another project (inside the mdb), this one works agains DAO instead ADOX, and odbc instead of ole db, give it a try:
function refreshtable(strTableName as string)
 
dim strConnectionString as string
strConnectionString ="ODBC; Driver={SQL Server};SERVER=" & strServer _
& ";DATABASE=" & strDatabase _
& ";UID=" & strUid _
& ";PWD=" & strPwd & ";"
 
dim db as dao.database
dim tdf as dao.tabledef
dim strCnonect as string
set db=currentdb()
 
on error resume next
db.tabledefs.delete strTablename
on error goto 0
 
set tdf=db.createtabledef(strtabledef)
tdf.sourcetablename=strTableName
tdf.connect=sqlConnectionString
db.tabledefs.append tdf
db.tabledefs.refresh
 
set tdf=nothing
set db=nothing
 
End function
 
Sub RefreshLinks
 
dim tdf as dao.tabledef
for each tdf in currentdb.tabledefs
 ' insert your sql server name here
 if instr(tdf.connect,"SQLSERVERNAME")<>0 then
  refreshtable tdf.name
 end if
next tdf
set tdf = nothing
End sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vb_jonasCommented:
in vb.net it would be something like this:
    Function refreshtable(ByVal db As dao.Database, ByVal strTableName As String)
 
        Dim strConnectionString As String
        Dim strServer As String
        Dim strDatabase As String
        Dim strUid As String
        Dim strPwd As String
        strConnectionString = "ODBC; Driver={SQL Server};SERVER=" & strServer _
        & ";DATABASE=" & strDatabase _
        & ";UID=" & strUid _
        & ";PWD=" & strPwd & ";"
 
        Dim tdf As dao.TableDef
 
        On Error Resume Next
        db.TableDefs.Delete(strTableName)
        On Error GoTo 0
 
        tdf = db.CreateTableDef(strTableName)
        tdf.SourceTableName = strTableName
        tdf.Connect = strConnectionString
        db.TableDefs.Append(tdf)
        db.TableDefs.Refresh()
 
        tdf = Nothing
        db = Nothing
 
    End Function
 
    Sub RefreshLinks()
        Dim dbe As New dao.DBEngine
        Dim ws As dao.Workspace
        ws = dbe.Workspaces(0)
        Dim db As dao.Database
        db = ws.OpenDatabase("mymdb.mdb", False, False, "MS Access;PWD=mypwd")
 
        Dim tdf As dao.TableDef
        For Each tdf In db.TableDefs
 
            If InStr(tdf.Connect, "SQLSERVERNAME") <> 0 Then
                refreshtable(db, tdf.Name)
            End If
        Next tdf
        tdf = Nothing
    End Sub

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.