Refreshing Links for Linked Tables [RESOLVED]

[erickwidya]

can anyone have an idea how to do this?
i used Access

thanks

[Ecniv]

What exactly do you want to do...?
And with what? Access forms to tables or vb to access? or vb to access to access?
Dao or AdoX?


Vince

[erickwidya]

sorry for incomplete info

as u see..i can make a link tables using Access..and with Access too i can make the link table to set new link location if the backend dbase is remove to other location..

what i want is to do it in vb via ADOX i think :0
first it will see if the link location is not moved..if it move..it'll prompt the user to set new location..and use that new location to refresh the link..

i got the code from MSDN..but it's not working..

Sub RefreshLinks(strDBLinkFrom As String, _ 
     strDBLinkSource As String)
Dim catDB As ADOX.Catalog     
Dim tblLink As ADOX.Table       
Set catDB = New ADOX.Catalog     ' Open a catalog on the database in which to refresh links.     
catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _        
"Data Source=" & strDBLinkFrom       
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") = strDBLinkSource
    [b]tblLink.Properties("Jet OLEDB:Create Link") = True[/b]
  End If     
Next       
 
set catDB = Nothing  
End Sub

the error said..'multistep line error.. '' at the bold part
thanks

[Ecniv]

Public Sub ADOConnectTablesFromMDB(ByVal strMDBPath As String, Optional strPassword, Optional blnReplaceTables, Optional strMDWPath)
'---- Relinks tables from another db to this one (not system tables)
 
'---- Requires
'----   MDB Path
'----   Password (Optional) if mdb is passworded
'----   Replace Table (Optional) replaces the table if it exists already
'----   - otherwise, add table with an incrementing number appended to title
'----   MDW Path (Optional) if mdb is using a different system file
    
'---- information from :
' from  : [url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnofftalk/html/office10052000.asp[/url]
    
    Dim adoxCatDB As ADOX.Catalog, adoxCatDBOut As ADOX.Catalog
    Dim adoxTbl As ADOX.Table, adoxNewTable As ADOX.Table
    
    Dim strConnectionString As String, strCDBTblName As String
    Dim lngLoop As Long
    
    Dim blnDebug As Boolean, blnLeave As Boolean
    
    On Error Resume Next
        
    Set adoxCatDB = New ADOX.Catalog
    Set adoxCatDBOut = New ADOX.Catalog
    
    adoxCatDB.ActiveConnection = CurrentProject.Connection
    If blnDebug Then Debug.Print "Connecting... " & CurrentProject.Connection
    
'---- -- Provider string -- ----
'Provider=Microsoft.Jet.OLEDB.4.0;
'User ID=Admin;Data Source=" & strFilePath & ";
'Mode=Share Deny None;"
 
'Extended Properties="";
'Jet OLEDB:System database=C:\PROGRA~1\COMMON~1\System\SYSTEM.MDW;
'Jet OLEDB:Registry Path="";
'Jet OLEDB:Database Password="";
'Jet OLEDB:Engine Type=5;
'Jet OLEDB:Database Locking Mode=1;
'Jet OLEDB:Global Partial Bulk Ops=2;
'Jet OLEDB:Global Bulk Transactions=1;
'Jet OLEDB:New Database Password="";
'Jet OLEDB:Create System Database=False;
'Jet OLEDB:Encrypt Database=False;
'Jet OLEDB:Don't Copy Locale on Compact=False;
'Jet OLEDB:Compact Without Replica Repair=False;
'Jet OLEDB:SFP=False
    
    strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strMDBPath & ";Mode=Share Deny None"
    If Not IsMissing(strMDWPath) Then
        strConnectionString = strConnectionString & "Jet OLEDB:System database=" & strMDWPath & ";"
    End If
    
    If Not IsMissing(strPassword) Then
        strConnectionString = strConnectionString & "Jet OLEDB:Database Password=" & Chr$(34) & strPassword & Chr$(34) & ";"
    End If
    
    adoxCatDBOut.ActiveConnection = strConnectionString
    Debug.Print "Connecting... " & adoxCatDBOut.ActiveConnection
    
    For Each adoxTbl In adoxCatDBOut.Tables
    
        Debug.Print adoxTbl.Type, adoxTbl.Name
        
        If adoxTbl.Type = "TABLE" And Not adoxTbl.Name Like "MSYS*" Then
            Set adoxNewTable = New ADOX.Table
            With adoxNewTable
                .Name = adoxTbl.Name
                
                strCDBTblName = adoxCatDB.Tables(.Name).Name
                If Err.Number = 0 Then
                    If IIf(IsMissing(blnReplaceTables), False, blnReplaceTables) Then
                        adoxCatDB.Tables.Delete adoxCatDB.Tables(.Name)
                    Else
                        lngLoop = 1
                        blnLeave = False
                        Do Until blnLeave
                            .Name = adoxTbl.Name & Format(lngLoop, "00")
                            strCDBTblName = adoxCatDB.Tables(.Name).Name
                            
                            lngLoop = lngLoop + 1
                            If Err.Number = 0 Then
                                blnLeave = False
                            Else
                                Err.Clear
                                blnLeave = True
                            End If
                        Loop
                    End If
                Else
                    Err.Clear
                End If
                
                
                Set .ParentCatalog = adoxCatDB
                .Properties("Jet OLEDB:Create Link") = True
                .Properties("Jet OLEDB:Link Datasource") = strMDBPath
                .Properties("Jet OLEDB:Remote Table Name") = adoxTbl.Name
            End With
            adoxCatDB.Tables.Append adoxNewTable
        End If
    Next
    
    adoxCatDB.Tables.Refresh
    Application.RefreshDatabaseWindow
    
    Set adoxCatDBOut = Nothing
    Set adoxCatDB = Nothing
    Set adoxTbl = Nothing
    
End Sub

I don't think I updated the relink code to Ado yet, the above is just adding the tables as links (I think) to the currently open db.

I had this in an Access db when it was Dao and had to convert to Ado. I think this works... but well um... Try n see and grab the bits you need to make yours work.

I went for the following:
Access FE - copied to users C: drive - holds a pointer to the shared BE
Access BE - shared BE - holds pointers to mdbs required for sections of db plus any shared data from the sections
Access BEs - the mdbs that hold data only for the section required

Then had to get the tables deleted (those that were linked) and relink to the main shared db, and as you connect to the different sections, these relinked the tables to the data mdbs. Confusing, but it was an option I was exploring at the time.


Vince

[erickwidya]

thanks Vince for the code..yes it is use to create link

why u use access BE and access BEs? not quite understand from ur explanation..
can u give axn example of what table in those two access? so i can figure it out why u do that..?

i personally think just using two..
one as a backend at network server..
one is each client drive..that is link to network server..

i can get the link part works..but i seem can't manage the refresh link method that i posted..
it need to works coz..it must see if the link table is 'still' related to table at network server..so the app won't crash..

the only method i could think of is to delete all the link table in client computer..then make a new link table each time..(it's not a good method..)--> seems like the one u use?

[Ecniv]

Attached is a pic for the BEs... Its only a way that apealled at the time. Might not be the best.

The way you are describing (2 Tier) sounds fine, as to the deleting and relinking everytime for each client - should be fine, doesn't take long and you are guarenteed that the BE mdb will be attached correctly.

Alternatively you need to check the table to see if its there (the BE) by opening a connection/recordset. If no recordset is returned, and an error is, then you'd need to delete and relink... otherwise everything would be fine.

Your choice... You could implement both or either, then see which takes longer, or if you know that the BE folder may move, do the relink each time.

I had to do a relink each time because the BE mdb was going to move in the future (when I wrote it) and I knew it would be moving in a month or two. So I wrote it so an admin user could reset the links without me.


Vince

[erickwidya]

well it just i'm confuse coz the code i post is from MSDN (guess..M$ miss this ) and it seems the method at the code is the same like i'm doing manually at Access..so it should be pratically the same

from MSDN..

If the data source for a linked table is renamed or moved, you need to refresh the connection information used to establish the link. To refresh the link, update the connection string for the table by using the provider-specific Jet OLEDB:Link Datasource property, and then reestablish the link by setting the provider-specific Jet OLEDB:Create Link property to True. The following code example shows how to refresh the links for tables linked to another Access database.

well i guess i have to stick with what i mentioned before :P until i figure it out what to do

thanks again for the assistant Vince

[erickwidya]

just commented the bold part..and that part is works fine now.. (for three days trying..waalaaaaa )

...
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") = strDBLinkSource      
'    [b]tblLink.Properties("Jet OLEDB:Create Link") = True[/b]
   End If       
Next
...