How to deal with separate frontend and backend

[HughWarrington]

I'm working on a project with separate frontend and backend. The frontend has linked tables which point to the backend. At runtime, the frontend is designed so that the user can select one of many backends, and uses CurrentDb().Connect and .RefreshLink to link the tables at startup before any queries are made.

When I use Build From Source in the plugin, I get this error for each linked table:

-------------------------------------
ERROR: Could not link table 'Foo'
Source: clsDbTableDef.ImportLinkedTable
-------------------------------------
Linked table object not found in C:\Users\...
Connection String: ;DATABASE=C:\Users\...

What's the recommended approach here? The tables should not to be linked to anything until runtime.

[joyfullservice]

Is the back end available during the development of the front end? It appears that the linked table target could not be found during the build. A linked table needs to be linked to "something" during creation, or the link will fail. (As you noted above.)

There are a few approaches you could take with this. One approach would be to include the front and back end tables in the same repository, and build the back end database before building the front end database. This works well when the front and back end have more of a tight coupling where changes need to be made together.

Another approach would be to tie into the BeforeExport hook and relink the back end tables to a known set that will always be available during build. These could be empty tables in a database on a network share (or SQL server) but will allow the database to be successfully built. This way any build from source would always use the set of known tables, regardless of whether the production tables were available to the build environment.

While a linked table can still exist in a database even if the target cannot be accessed, I don't think you can create a linked table without actually having a valid target. I suppose you could even create a temporary target for the build, tying into the BeforeBuild hook and delete it afterwards, but that seems like a lot of extra work. 😄

[HughWarrington]

That sounds sensible to me. If there is anyone out there relying on the current behaviour, you could always make exporting linked tables optional behaviour.

[hecon5]

So, this will have pretty significant (and ... unexpected) consequences for people not realizing that linked tables won't export.

I actually think this is the right way, but this seems actually like a breaking change, being that the exports will no longer be provided.

While you do have a tool to export the SQL, IMHO there's going to be folks out there who do not realize this change means their linked table (and views) won't be exported any more. We've actually noticed backend changes that were not reviewed this way before...

IMHO: if we do stop exporting the linked tables, we need to point users to the backend export tooling as a recourse, or they're going to see all the deleted export files and freak.

[joyfullservice]

@hecon5 - Just to clarify, nothing is changing with the export of linked tables. You can still export and build your project with linked tables. They are still just as supported by the add-in. What is changing is that the optional one-way helper export of a SQL representation of tables will no longer apply to linked tables. The SQL view is purely for visual reference purposes, and does not affect the actual build of the database. (The main reason we have it, IMHO, is because it is easier to compare changes in this representation than in the more comprehensive XML files that are used to build the tables.)

[hecon5]

Correct; I gleaned that after I did my initial export and realizing that "oh hey, there's a LOT of deletions here, what'd I break!?".

I still think users should be apprised to this...that said...I do think the approach of not exporting linked tables is the correct approach.

[HughWarrington]

We could make the old behaviour the default, and just add an option to opt-out of the exports of linked table .SQL files.