Check that if the linked table have a corresponding table in Sql Server

Amir 181 Reputation points
2021-05-10T14:55:28.297+00:00

I have an Access program that is as FE and my database is SQL as BE. When I update and send my Access program to my clients, they get the updated version and copy it on their Pc. For the first time before logging in, they press a button that relinks the tables to database. The information of the connection string is in a table in their database. My code for relinking is quite simple but it has a bug. If all the tables in FE be present in BE there is no problem. But if there was a linked table in FE that doesn't be in BE, an error occurs and the relinking process fails.
How can I check that a table have a corresponding counterpart in BE and if it hasn't a corresponding table the code escapes it and continue to other tables?
And if the process was successful show a message. 

For Each td In db.TableDefs
  If td.Connect <> "" Then
    Set td = db.TableDefs(td.Name)
    td.Connect = strConnection
    td.RefreshLink
  End If
 Next
Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
817 questions
Office Management
Office Management
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Management: The act or process of organizing, handling, directing or controlling something.
1,998 questions
0 comments
Accepted answer
  1. DBG 2,301 Reputation points
    2021-05-10T19:02:04.867+00:00

    Hi. Not sure how that happens in your case. How can you have a table in your test/dev copy that is not in the live BE?

    In any case, I think you could just simply catch the error and move on. Basically, ignore the table that is causing the error. You could also go as far as deleting it, if you really don't want it in the FE.

    However, if you are using that non-existent table in any of your forms or code, how can the user use your application, if they don't have that table in their BE?

    0 comments

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest