Access 2010 with linked AS400 tables trying to refresh link of table that is gone

I have a Access 2010 database with linked AS400 tables. When I open the Access database, it tries to refresh the links using tdf.refreshlink.  I changed the name of the AS400 table. I no longer see that AS400 table in Access as a linked table but the refresh VBA code seems to still be looking for it.   It gives me the error 3011 - The Microsoft Access database engine could not find the object 'tablename'. I have looked in the Mysysobjects table and there is still a row for this table. How can I stop the refresh code from looking for it? I can't modify the MySysObjects table so that is not an option.
LeLeBrownAsked:
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.

Jeffrey CoachmanMIS LiasonCommented:
How are yo linking  the tables?
If the tables are linked into Access using the "External data" utility, they will be refreshed automatically...when the db opens.

I would only use tdf.refreshlink, if I had a distinct problem.

Try relinking the table (with the new name) and see what happens...

Lets see what other experts may post, in case I am not understanding something here...
;-)

JeffCoachmanm
0
LeLeBrownAuthor Commented:
I am linking using the external data ODBC connection. I removed the refresh code and it seems to be running ok now. So... if I have AS400 tables linked in Access, using the ODBC connection, these table links automatically get refreshed? These tables get updated daily with a AS400 script so I want to  be sure the Access db is getting the updated data.

Thanks
0
Jeffrey CoachmanMIS LiasonCommented:
If the tables are linked via "external data", ...the ODBC refresh rate is set to 1500 seconds by default. (I think that works out to every 25 minutes...?)

But you can adjust it to whatever you like.
There are other settings as well, they are listed in the Access options:
Client Settings-->Advanced

But remember the higher the refresh rate the more resources Access must use to keep refreshing...

But I think the tables are refreshed every time you open them.  Just do a test to be sure.
Update a record in the AS400 ,then open Access a minute later and see if it is there...
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
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.

Jeffrey CoachmanMIS LiasonCommented:
<But remember the higher the refresh rate the more resources Access must use to keep refreshing...>
I should have said:
"But remember the Lower the refresh rate the more resources Access must use to keep refreshing..."

But I am sure you knew what I meant.

;-)

Jeff
0
LeLeBrownAuthor Commented:
Thanks!!!
0
Jeffrey CoachmanMIS LiasonCommented:
;-)
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
Microsoft Development

From novice to tech pro — start learning today.