Table Unlinking/Re-Linking

[Bahamas]

We have an access program that unlinks a table after certain functions, and relinks it in others. Is there a need for this? For one user, the table does not link, so we have to manually go over and link it for her everyday.

I am wondering why it was built to unlink and relink - shouldn't the link be "real time" and get up-to-date data?

I am also not sure why the TableDefs.RefreshLink is necessary (though it is not in our code - what situation would warrant refreshing the link?)

 

[CJ_London]

no idea why, would have thought it could stay linked all the time but perhaps the code behind your functions will provide a reason - I could speculated that maybe it is just to ensure the table is linked before processing.

you use refreshlink when you relink to a table

 

[Bahamas]

Thanks - I'm leaning towards removing the code that unlinks the tables. Just wasn't sure if there would be any consequences.

 

[jdraw]

Do all users have a copy of the same Access FrontEnd on their individual PCs?

It appears that her front end code may be different.

I have seen set ups where the latest official front end is stored in a defined shared location. When users sign on to the database application/system a routine checks the version on their PC with the official version in the shared location. If they match, carry on. If the versions don't match,then copy the official front end to the user's PC and carry on. Carry on, here, could include a unlink and refreshlink of all tables.

I see no reason to unlink a table after use, unless there could be a security/privacy concern.

 

[Bahamas]

The FrontEnd is stored on a network drive, and the users click a batch file that copies the FrontEnd to their C: Drive.

unless there could be a security/privacy concern.

You hit it on the head - I was just told that it was due to an audit request in order to not have the data be persistent.

 

[jdraw]

So, is your issue resolved?? Why does the one user not relink automatically??

 

[Bahamas]

My issue is still not solved. The program calls the LinkTable function below. Every morning, it doesn't link for her, so I have to go over and manually (that is, through Access UI , External Data, instead of VBA) link it. I've tried running the LinkTable function separately, and it still didn't work. However, here's the strange part: after I manually linked the table, I unlinked it, reran the LinkTable function, and it worked.

I don't understand why it's not working initially every morning before I have to go over and manually link. Wanted to just stop the program from unlinking, but that is not an option due to audit requests.

I just noticed the On Error Resume Next, which I assume was inserted in case the table is already linked. Tomorrow morning I will comment that out and see if it gives me a descriptive error.

Public Sub LinkTable(strTableName As String)

On Error Resume Next:

    Dim cat         As ADOX.Catalog
    Dim tbl         As ADOX.Table
    
        Set cat = New ADOX.Catalog
        cat.ActiveConnection = CurrentProject.Connection
     
        Set tbl = New ADOX.Table
     
        tbl.Name = strTableName
        Set tbl.ParentCatalog = cat
             
        tbl.Properties("Jet OLEDB:Link DataSource") = DataDirectory
        tbl.Properties("Jet OLEDB:Remote Table Name") = strTableName
        tbl.Properties("Jet OLEDB:Create Link") = True
        tbl.Properties("Jet OLEDB:Link Provider String") = WorkSpacePW
        tbl.Properties("Jet OLEDB:Table Hidden In Access") = True
        cat.Tables.Append tbl
        Set cat = Nothing
    
End Sub

 

[CJ_London]

hadn't realised you are linking as ADOX. Never used it myself - but explains why refreshlink is not used - that is a DAO action and presumably not relevant for ADO.

Interesting that you are linking as DAO (presumably) and you are able to mix DAO and ADO tables in a query

 

[jdraw]

Further to CJ's comment.

OnError Resume Next is equivalent to no Error checking.

I always used DAO also.
There are many references to the Dev Ashish site mvps for ReLinking (via DAO).

You're probably familiar with this M$oft ADOX example

 

[GohDiamond]

You can also have a look here:
http://allenbrowne.com/func-adox.html

Cheers!
Goh

 

[Bahamas]

Ok, I have a little more information now. It is crashing at this line:

cat.Tables.Append tbl

and here is the error message:

Run-time error '-2147467259 (80004005)':​

Object invalid or no longer set​

The LinkTable function that this code is in usually works, it's just once a day that it seems to crash and give this error...