Linking a table

[crowegreg]

I'm having difficulty with linking a table.

I've setup Bob Larsen's excellent feature of updating the FE DB. Both the FE DB and the BE DB are located on a server. A user has their own version of the FE DB on their PC. Within a form, the user creates an archived table. This table gets added to the BE DB.

What I'm needing to do is link the newly created archive table to the FE DB that is on the server. I've viewed a lot of code to assist with linking, but nothing with what I'm trying to do.


1. How do I set the db as being the FE DB on the server. I'm able to retrieve the path and name of the FE DB, but I can't figure out the correct syntax.

[Dal Jeanis]

I'm missing something in the question.

You have a user FE on user's machine C. You have a BE on machine S. The user FE is already connected to the BE, and creates a table on the BE.

The FE on the server is your current production "base" FE. It is already able to link to the BE, no matter where the production FE gets copied. The BE is in a single place.

Every single FE copy is able to link to the BE that has the backup table. So, what is it that you are looking to do?

[ItsMe]

What Dal said....

The syntax would like
\\ServerName\FolderName\DBName.extensiontype
enclosed in quotes and assigned to a String variable

[crowegreg]

When the user creates the table, I need the FE on the server, production "base" FE to have the link for the new table within the BE.

[Dal Jeanis]

You really do NOT want to be messing with the user FEs updating your production FE master. That's a design disaster in the making. Wow. Contention, corruption. Any number of headaches and surprises.

I probably haven't said it strongly enough, so hopefully someone will chime in with a single N and a few hundred capital O's. Really, the FE master should only be updated, by professionals (you), when a version upgrade is occurring.

A better strategy is to have each FE automatically, on opening, or when a particular form is opened/refreshed, look to see what backup tables might be present on the BE, and create the links. Much safer, clearer, and easier to code as well!

[ItsMe]

Your archive table should be running programmatically. Its structure should not change. Because the structure does not change, you only need one archive table. Imagining a need to create multiple tables causes me to believe you are not referring to an "Archive" action.

Whatever action or task you are attempting to accomplish by creating additional tables sounds like a bad idea to me regardless. It is very rare that I encounter a need to create a new table. It almost ALWAYS happens in the development stage of an application or a module (expansion of function) of an application.

[crowegreg]

OK. So maybe the better idea is to have 1 archived table. Rather than create additional archived tables, append the current records within the current table into the single archived table.

[Dal Jeanis]

crowegreg - That is generally true. There are some types of data (for instance, auditing or accounting data) that may require another solution, but even those applications can usually be handled with adding a date field and one or two "archive type" fields to the backup record layout.

This methodology has the advantage that every time you change the layout of your primary table, you will remember that you need to make the exact same change to your single archive table. The more archive tables there were, and the more variants of table names, the more likely that you'll accidentally (or intentionally) let the historical data rot.

This kind of discussion is how you find out in advance that you might be aiming a blunderbuss at sensitive portions of your own anatomy. Better to find that out before the thing is loaded, right?

[crowegreg]

I'm building this DB at a client's location. I won't be here much longer.

I was creating the archived tables to help produce audit reports. I thought it would be easier to have the data in separate tables. It's a good thing I raised this question now, so I can create my reports correctly.

Thanks for the assistance!!

[ItsMe]

crowegreg,

Maybe you can start with the task itself. What is the problem you are encountering? Are the users complaining about a specific thing they can not do? Do you have so many records in a specific table it is slowing down the data base? You may be able to adjust how you query the table and what columns are indexed.

Just putting some ideas out there.

EDIT: I see you are focused on the task

[crowegreg]

No problems. This data is imported in from other legacy systems. I'm creating audit reports to recognize the changes. The user needs to be able to select between the different imports to see the changes in the data. I thought it would be easier to have a separate audit file for each time the import is done. Then, when the report is needed, the user selects the different import dates, then I query the different tables to determine the changes.

I'll go ahead and only have 1 audit file. Then when the user needs to create the report, they will select the import dates, and I'll create 2 recordsets based on their selection, then compare the 2.

[ItsMe]

If dates along with the prime key are not enough to distinguish the different imported data files, you could add Booleans and another unique identifier. Maybe you can grab the file name or use another way to identify the import session. Still believe it is easier than a table. Another option may be to link to the file and run your query while it is outside the DB.

Once it is all in a single recordset you may discover a way to automate much of the comparison between old and new, which it seems you are doing now anyway.