Link BE table to more than one FE

[AggieLJ]

Howdy,

I am working on a project that includes a BE file, and two FE files. One FE is a control panel for the admins to adjust security settings, look at audit files, etc., while the other FE serves as the data entry point for lower level users.

Right now, when a admin creates a new employee in the FE Control Panel, it creates an audit table in the BE, then links it to back the FE Control Panel.

What do I need to do to also link the new table to FE Data Entry using VBA?

Here is a code snippet of what I am using now:

     Dim db As Database
     Dim td As TableDef
     Dim fd As Field
     Dim stFile As String
     Dim TableName As String

     'Create new employee's audit file in the System Log and link.
     stFile = "C:\System Project\SystemLog.accdb"
     Set db = OpenDatabase(stFile)
     TableName = "tblEmployees_" & Me.txtUserID
     Set td = db.CreateTableDef(TableName)
     td.Fields.Append td.CreateField("dtmTimeStamp", dbDate)
     td.Fields.Append td.CreateField("chrComputer", dbText, 100)
     td.Fields.Append td.CreateField("memAction", dbMemo)
     db.TableDefs.Append td
     db.TableDefs(TableName).Fields("dtmTimeStamp").DefaultValue = "Now()"

     DoCmd.TransferDatabase acLink, "Microsoft Access", stFile, acTable, _
                            TableName, TableName
     
     DoCmd.Close acForm, Me.Name, acSaveYes
     MsgBox "New Employee Creation Successful!", vbOKOnly, "Congratulations"
     Exit Sub

 

[DCrake]

Why does the instance of adding a new employee negate the need to create a brand new table. Surely you should be adding records to an existing table. Confused.

 

[boblarson]

I agree - there should NOT be a separate table for each employee. Bad, non-normalized design and will kill you everytime.

 

[AggieLJ]

Sorry for the confusion, I guess a little more background would help...

When an admin creates a new employee, all of their information is stored in tblEmployees in the SystemLog BE.

However, there became a need to track what changes certain employees made to customer records. Therefore, individual audit tables (and tracking system) was created for all the current employees for that purpose. So I am trying to implement a procedure that will automatically create an audit table in the BE, then link them to both of the FEs for any future new employees. (So I don't have to manually establish the links.)

The code above is successful in both the table creation and linking to the Control Panel, but I have am having trouble getting it to link to the second FE (Data Entry FE).

Does that help clarify?

 

[boblarson]

The explanation is good but the design is horrible from a relational database perspective. You do not create individual tables for any entity. You should be storing the data in a single table with the employee ID to identify the employee's audit trail.

 

[DCrake]

You have to be in the host front end to manage the link. You would need to have some code on the startup of the user front end that checks for the table links and if not found attempt to link from back end.

But why the need to create an audit table for each employee. The front end should already have links to the audit table in any case be it empty or not.

Or are you as I am beginning to suspect giving each new user a unique copy of the front end.

 

[AggieLJ]

Now that you mention it... it probably would make more sense to just have one audit table and simply have a field indicating which user is doing what...

Thanks for jogging my memory on normalization... I had an Aggie moment...

 

[AggieLJ]

Nope, everyone gets to download the same copy of the front end off of the server (thanks to boblarson's FE update tool).

I really don't know what I was thinking about individual tables... Like I said... Aggie moment...

 

[The_Doc_Man]

As to the other problem, you don't link a back end to a front end. You link a front end to a back end, and the back end NEVER KNOWS (unless you store it somehow) that it is linked to anything at all.

 

[boblarson]

Like I said... Aggie moment...

Shouldn't that be "UT Moment" (because then you would be dissing them instead of your own school )

 

[AggieLJ]

Well, I was kinda anticipating some kind of "How many Aggies does it take to ...<insert some task here>? " joke that all of us Aggies are used to, but a UT (or as we say t.u.) moment probably describes it better.

Thanks again for all the help you guys provide, not only on this thread, but throughout this site! I can always rely on y'all to keep me on the right path!