MS Access table name of Clients causes errors when linking tables

It  appears that in the recent versions of MS ACCESS (later than 2007) the table name of Clients is now causing errors when trying to link or re-link tables. This happens when using VBA and by the Linked Table Manager :

To resolve the problem using the Linked Table Manager, when it reports - 'Clients' is an invalid name - I deselect the Clients table re-link the rest and then link the Clients table using the menu External Data>Import MS Access Database>Link to the Data Source by creating a Linked Table.

For VBA I do not have a solution. I am using the following relevant code:
Set Tdfs = dbs.TableDefs
'Loop through the tables collection
    For Each Tdf In Tdfs
        If Tdf.SourceTableName <> "" Then 'If the table source is other than a base table
            Tdf.Connect = ";DATABASE=" & Path 'Set the new source
            Tdf.RefreshLink 'Refresh the link
        End If
    Next 'Goto next table
The Tdf.RefreshLink causes an error no 3125 - Description: '~TMPCLP642821' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long. Note: in the Watch Expression dialogue it identifies 'Clients' as being the Tdf.SourceName. Clients is NOT the first table so clearly it is the Clients name that MS Access is objecting to.

This is OK in development but I have to create a 'self install' process that automatically links to the required source over my client's network using VBA; I cannot regularly travel to my client's location for every update. Can you please help me resolve this other than re-naming the Clients table link, which would be a massive job to change all objects; Clients just happens to be the central object of the project?

Thank you
Ray Carroll
RayshkaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
~TMPCLP642821 is one of those names Access gives to temporary objects, so that would indicate you have a temporary table hanging around. Try compacting your database (after making a backup first) and try to relink again.

"Clients" is not a reserved word, so I'd suspect something else (like corruption).

Also make sure your Windows and Office installs are fully up to date.
RayshkaAuthor Commented:
Hi Scott,

Yes you appear to be totally correct - thank you. I have opened the MySysObjects and found two entries for Clients one is correct - Foreign Name = Clients the other is ~TMPCLP642821. However, I cannot delete the erroneous one. Ideas please?
RayshkaAuthor Commented:
Hi Scott

Tried to delete erroneous entry via query but 'no permission to delete' message.

Ray
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

RayshkaAuthor Commented:
Hi Scott or Sir/Madam,

Thanks for pointing me in the right direction concerning the temp table. I have managed to resolve the problem by rebuilding the database from scratch; imported all objects into a new blank database.

Thanks once again.
Regards
Ray
RayshkaAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for Rayshka's comment #a40885990

for the following reason:

I needed to get on with the job and hadn't had a reply regarding how to resolve the existence of a temporary table. So I tried several things and finally a rebuild as per my last comment. I guess therefore I managed to resolve things finally before anyone got back to me but there still lies the issue of whether my solution is the only solution and correct one.

Regards
Ray
RayshkaAuthor Commented:
Dear Scott McDaniel,

Really sorry but it appears that I have exactly the same problem recur. I thought the problem was fixed. I had got rid of the MySysObjects entry but when I re-tried VBA re-link and the problem recurred but with the message that Clients is an invalid name; no mention of the temporary file TMPCLP642821 -  Same err = 3125.

Still no erroneous entry in the MySysObjects table relating to the name of a temporary file. This refers to the application where my investigatory efforts have been focused not the linked tables. However, I can find nothing untoward in the linked tables.
 
If you believe that there is another temp file somewhere called Clients then how should I look for it and what to do with it when I find it.

My knowledge here is exhausted.

Once again apologies for misleading you that it was fixed.

Regards
Ray
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
A rebuild is typically a good idea in cases like this, so I'm sure you're okay with it.

hadn't had a reply
Please understand that we're all volunteers and have lives outside of EE, so you may find we don't reply for several hours.
Dale FyeOwner, Developing Solutions LLCCommented:
I think Scott's recommendations helped you resolve this question and points should be awarded to him.

The other thing I would do, prior to executing your code, is refresh the tabledefs collection.  I've found that if you delete a table, Access will occasionally hold onto that table with a temp name in mSysObjects.  so try adding the following line just before the beginning of your loop:

    dbs.TableDefs.Refresh
    'Loop through the tables collection
    For Each Tdf In dbs.TableDefs
        If Tdf.SourceTableName <> "" Then 'If the table source is other than a base table
            Tdf.Connect = ";DATABASE=" & Path 'Set the new source
            Tdf.RefreshLink 'Refresh the link
        End If
    Next 'Goto next table

Open in new window

I would also add error handling to the procedure to display a message, so you can identify the table(s) causing the problem, and then resume processing the list.
RayshkaAuthor Commented:
Hi Dale,

I think I messed up the close by not understanding perhaps the process. I really wanted to award Scott points as it was his comments that gave me the most likely cause supported by MySysObjects. However, I later found out that the problem had not been wholly resolved despite my 'solution' and I tried to resurrect the issue. Perhaps you could re-open it or tell me what to do.

Anyway I tried your suggestion but the problem persists.

Have you seen my post regarding the latest on this problem?

Regards
Ray
RayshkaAuthor Commented:
Dale,

Attached is the message I get:
Screenshot-2015-07-17-14.18.04.png
Dale FyeOwner, Developing Solutions LLCCommented:
Rayshka,

Now that I "objected" to your closing the post, one of the admins will change the status so you can award points.

Unfortunately, I'm on my way out the door, but will take a look back at this when I get home from my appointment.  In the mean time, maybe Scott will chime back in.

Dale
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Are you absolutely certain you don't have any invalid characters in the table name? Try renaming it, just for kicks.

Also might try to create a new table, and then delete the Clients table and rename the new table to "Clients". To do that, make a backup of your database first, and then run this code:

CurrentProject.Connection.Execute "SELECT * INTO NewClients FROM Clients"

Make sure you now have a table named "NewClients", then delete the "Clients" table. Close/reopen your database (and Access), and then rename the "NewClients" table to "Clients"

BTW, you don't have to wait for a Moderator to change the status after objecting. Objecting to the closure immediately resets the question to the same state it was prior to the close, so you can immediately take action afterwards if desired.
RayshkaAuthor Commented:
Hi Scott,

This where you get to berate me wholly! In Clients is two multi-value fields, which prevent me running the INSERT INTO statement. Despite all criticisms the multi-value fields have worked well for me (upto now it would appear).

However, I have tried various ways to replace the Clients table albeit by the copy/paste method.  That includes copying the Clients to ClientsNew, deleting the original Clients and then re-naming ClientsNew to Clients. I have tried this from the Application and also the linked tables. So apart from your actual suggestion I have as close as I can replaced the table!

Sorry I haven't got back to you until now but personal issues have prevented it.

Best regards
Ray
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I have tried this from the Application and also the linked tables
If those are linked tables, then you would of course have to do any copy/paste actions on the "backend" database, and not in your Frontend UI application.

Multivalued fields are evil for many reasons, and this is one of them :) That said, you can get to the multi-valued tables using DAO if you want to try that. This article shows how to loop through the "Parent" records, and then further loop through the "Child" records for those MV fields. You could essentially run a loop like this, INSERT the Parent record, and then insert new Child records for each item in the MVF columns.

https://msdn.microsoft.com/en-us/library/office/Ff821054.aspx
RayshkaAuthor Commented:
Hi Scott,

Thanks for this. I have done this before but not in connection with this problem so I know and understand the process, will give it a go and let you know.

Whilst I understand your reasoning behind Multi-Value fields, this is something I have worked with many years ago before it became an Access facility and would use it in future but for carefully considered purposes.

Is it OK if I try your suggestion above and then allocate you points as I may not be able to get round to this before next week?

Regards
Ray
RayshkaAuthor Commented:
Hi Scott,

I have tried your solution and like everything else I've tried it gives me the same problem.

Lastly I copied the Clients table in the linked tables to a table named 'Customers' (copied structure and data). I then manually linked to 'Customers' in the linked tables from the application and then renamed the 'link' in the application to Clients; effectively the link is 'link name'=Clients and 'source table name'=Customers. Thus the application still sees Clients so the code, etc. is consistent but the table name in the linked tables is 'Customers'. The linking then works perfectly.

Can you see anything wrong or risky with this?

Thank you for your help. Please let me know if you are happy that this is a valid solution and I will close the problem accordingly?

Regards
Ray
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
No, I can't see anything wrong, and the only risk would be is someone deleted the linked tables and just relinked, without renaming as you did. If that happens, you'll end up with the same situation.

this is something I have worked with many years ago before it became an Access facility and would use it in future but for carefully considered purposes.
We must be referring to two different things. Access MultiValue Fields were added in Access 2007, so I'm not sure what you mean by writing "before it became an Access facility". Access added this sort of functionality by creating related tables behind the scenes, so data is still being stored correctly, but MVFs still cause quite a bit of troubles (especially when you try to upsize to SQL Server, or something along those lines).
RayshkaAuthor Commented:
Hi Scott,

MVFs were in existence on mainframe databases a while back before and at the beginning of desktop processing. This is where I first became aware of them. I am trying to recall the database provider but my memory fails me.

I acknowledge your reaction to them but seriously if this bug/error in Access had not occurred everything would have been fine and was working correctly.

I will endeavour to find a proper solution but in the meantime this gives me a workaround.

Thank you
Ray
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
MVFs in Access are fine until you run into a situation where they're not fine :)

As for me, I'll just continue storing them using standard data relationship methods.

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
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
Microsoft Access

From novice to tech pro — start learning today.