Dear all,
I just spent quite a lot of time investigating one problem, which seems to be a bug (which I wasn't able to google). So I am just posting a bug report here:
BUG REPORT:
PROBLEM DESCRIPTION:
Not possible to re-link Linked table after unsuccessful connection to a table, which has Memo field with Append Only flag, set to Y.
On the attempt to re-link linked table using Linked tables manager, an error comes:
"is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides". This is despite of the fact, that the path is correct
The below description is for manual actions, but the same is applicable for programmatically linking of tables (using VBA).
HOW TO REPRODUCE:
1) In the directory C:\Test create BackEndAccessDB.accdb, which has table TestTable. TestTable should have only one column testMemo with type Memo
2) Set the flag Append Only to Yes
3) Now in the same directory C:\Test create file FrontEnd.accdb.
4) In the FrontEnd.accdb create a linked table to the table TestTable in file BackEndAccessDB.accdb. Make sure linked table works
5) Close both *.accdb files
6) Rename directory C:\Test to C:\Test_new
7) Open FrontEnd.accdb and attempt to access linked table TestTable. At this moment you will get an expected error:
“’C:\Test\BackEndAccessDB.accdb’ is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides”
8) Press OK and, right click on linked table and invoke Linked Table Manager.
9) Try to re-link the table to new location (C:\Test_new\ BackEndAccessDB.accdb). Now you get an unexpected error:
“’C:\Test\BackEndAccessDB.accdb’ is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides”
Note, that even though you have specified a new location (C:\Test_new\ BackEndAccessDB.accdb), the error message still refers to old location (C:\Test\BackEndAccessDB.accdb)
HOW TO AVOID
Do not set flag Append Only on Memo fields in linked tables
HOW TO FIX, IF PROBLEM IS ALREADY PRESENT.
In the back end database sent the Append Only flag in Memo field to No.
In the front end delete the linked table and re-create again
NOTE:Problem is seen on Access 2007 (12.06.6606.1000) SP3 MSO (12.0.6662.5000)