Basic backend table modification questions ...

[Marshall Brooks]

I should know the answers to these, but I wanted to confirm them.

• I want to add two new tables to the back end of my database. Usually, I would get exclusive access to the BE in order to do this. Is this a firm requirement, or can other users have the back end open, since there is no way for the new tables to be accessed since they aren't linked to the FE yet.
• I know if I want to add or modify fields of a linked BE table, I need exclusive access to the BE - or at least I've always done it that way.
• Some of my tables are stored in the backend and then the data in the table is copied into local tables in the FE on startup. I'm assuming that if I ever wanted to add/modify fields to these tables, that is when I would have to have exclusive access to the BE, update the tables in the BE, update the FE, and then distribute and have everyone update to the new FE at the same time the updated BE was available, correct?
• Adding tables to the FE is simple, except nobody else can use them until the new FE is released?

Thanks in advance!

 

[MarkK]

Things I have done that have worked while the BE had connected users...
- Export a table from the FE to the BE using the Export popup option from the nav pane

With a DAO.Database reference to the BE...
- Execute a SQL CREATE TABLE command.
- Execute a SQL ALTER TABLE command for a table that has no locks (no one is using it at the time).

I have successfully run this code numerous times where dbs is an object reference to the BE. It raises an error if a user has a lock on the table.

Public Sub AddColumn(TableName As String, ColumnName As String, DataType As String)
    If Me.TableExists(TableName) Then
        On Error GoTo handler
            dbs.Execute "ALTER TABLE " & TableName & " ADD COLUMN " & ColumnName & " " & DataType
        On Error GoTo 0
    Else
        Err.Raise 5, "cTableDef.AddColumn()", "Table not found"
    End If
    Exit Sub
handler:
    Err.Raise Err, "cTableDef.AddColumn()"
End Sub

 

[ebs17]

Actually you want local tables. You know that you can link multiple backends into one frontend. This means that local tables are best stored in an additional local backend. This means that neither the backend with the master data nor the frontend is disturbed.

Such an additional backend can be distributed with the frontend or created completely using code.

 

[Marshall Brooks]

Wow - Great answers!!!

@Pat Hartman - Thanks for confirming and explaining. Contrary to previous impressions, I very rarely interrupt the normal work flow. There are ways (for us, at least) to not do so without working odd shifts - for example:

• As you confirmed, adding a table can be done without kicking everyone out of the back end, so can be done during working hours.
• Modifying a linked table - Two scenarios:
  • If I am adding a NEW field: This doesn't take long. I'll need maybe 5 to 10 minutes exclusive access to the back end. Typically, the rest of the team leaves for the day earlier than I do. If necessary, the database typically isn't critical enough that someone can't spare 5 minutes for me to work on it, or I can do it at a different time. Adding the new field doesn't affect the deployed front end - it doesn't know the field is there, so I can modify the development front end without impacting anyone else.
  • If I am deleting a field from the table or modifying a field in the table and those fields are used in the front end, I have to release simultaneous as I would for a duplicated table below:
• Adding/Deleting/Modifying a field in a duplicated table does require coordination on the release. What I typically do here is:
  • Make a COPY of the backend and make my changes to the COPY. (And probably make a log in Word or similar of exactly what in the backend was changed to what.
  • Change my development FE to link to the COPY of the BE. Modify the development FE as needed.
  • Tricky part: I need exclusive access to the BE (5 to 10 minutes) to make my changes, Then I need to change the development FE to point to the REAL BE, Then I need to release the development FE as the production FE. Needs maybe 30 minutes when the database will be down for everyone. Typically Friday afternoons work well. Everyone else is getting ready to leave and the new version will be in place Monday. (And if I find anything minor that needs to be changed, I can work on it over the weekend, if needed.)

@MarkK - I wasn't aware of the Export pop-up. Seems useful. If nothing else, I could develop my tables as local tables in the development FE. No impact on anyone else if I want to add fields or adjust structure. Then, when I'm satisfied everything works properly, I would export the table to the BE and link to it in the FE.

@ebs17 - I hadn't considered local tables. I think this is sometimes referred to as a side-end. Sounds promising, but I'm unsure about the logistics. For information - we have three types of tables in the database:

• Strictly linked tables: These are in the backend and would stay in the backend. This the day-to-day real-time data in the database.
• Tables only in the FE: There are not many of these. There is one table that contains the version date of the FE, and one table for monitor (display screen) information.
• Tables in the backend that get the data loaded into the FE on startup. These are things like network paths, E-mail addresses, etc. We load them locally for faster access. They aren't updated often. The structure rarely changes. The data is fluid enough that I would want everyone to have it updated on startup, but not volatile enough that real-time updates are required (although that would be nice). These seem like candidates for side-end (local) tables - SE. With them as a side-end, I could update my local version of the SE and my development FE without disturbing operations and just release together when needed.

Questions:

• Where would the SE (local tables) be located. The BE is in a central location on the network. The FE is either on the users desktop or (for Citrix) on the mapped U:\ Drive. I'm assuming I would place it in the same folder as the FE.
• If the files are local - how are updates managed? Not necessarily structure changes, but just updates to any of the records? Would I need to have a version number for the SE and update it every time I changed a record? (And have a SE Version number field in the BE that gets compared at startup?). In a perfect world, I could just send out an E-mail that I updated the SE and go grab the latest version and copy it, but that didn't work well with the FE, so now we have a similar process that stores a version number in the FE and compares it to the version number in the backend. (Or I guess I could have a script that downloads the SE to the local folder - similar to how I update the duplicated table data, but it would be done outside of the FE.)

 

[ebs17]

With them as a side-end, I could update my local version of the SE and my development FE without disturbing operations and just release together when needed.

You described one of the advantages well.

I'm assuming I would place it in the same folder as the FE.

Yes, in the same folder or in a subfolder, but with a fixed relative path to FE for easy table relinking.

how are updates managed?

You don't need version numbers just for data. Simply use timestamp fields in the records for new creation and changes.

 

[ebs17]

When developing I use a copy of the backend. Data does not have to be complete and up-to-date, but it does have to be sufficient to show typical problems. You can also play around wildly with a copy.
Since development takes time, users can now continue working with the original backend without being affected.

To transfer the update:
The front end is simply replaced.

Structural changes to the backend are made during development using a compiled code script, not by hand.
Structural changes to the database
The script is cumulative, in the form “first check, then act per measure”. This means it can be executed as often as you like.
For me, the script is a documentation of exactly what is being carried out, also to prevent problems caused by others from being assigned.
The script can then be executed in a very short time and without errors (because it has been checked) when actually updating the working environment. After all, you need exclusive access here, the users have to be logged out and take a break.
Nothing else happens to the data from the original backend. If in doubt, I don't even need to know this user data (trade secrets, data protection, etc.).

 

[Marshall Brooks]

I was following along and I got lost in the details somewhere. Here's a (hypothetical, obfuscated) example:
*** Current Method ****
Tammy and Tom work in my group and use the database.

In the BE, I have tbl_Email with two fields: fldGroup and fldEAdress

Record1 of tbl_Email contains QA and joe.smith@company.com

Usually, we will still want to send to joe.smith, but tbl_Email is copied locally from the BE to the FE on startup to provide faster acccess.

Joe Smith retires and Suzy Brown takes over for him.

I update Record1 to change joe.smith@company.com to suzy.brown@company.com. (And typically E_mail Tammy and Tom to let them know).

Tom does not have the database open, so he needs to send a report to QA, so he opens the database, it downloads the updated tbl_Email and he sends the report to Suzy.

Tammy does have the database open and she needs to send a report and she will try to send it to Joe and will get "Undeliverable" and will either let me know or close and re-open the database and it will work. (That part is less than ideal, but the data doesn't change often enough for it to be an issue.)
*** Side End method***
Tammy and Tom have the FE and their OWN copy of the SE on their computers. The SE has tbl_Email included. The FE no longer loads tbl_Email, but instead links to the tbl_Email in the side end.

I update Joe Smith to Suzy Brown.

How does that get communicated/distributed to Tammy and Tom? I'm not sure timestamps work ...

Typically, without version tracking, I send an E-mail to Tammy and Tom that they need to download the new copy of the SE. Tammy is diligent and does so. Tom, not so much. So I get an E-mail from Tom: Hey, my email to Joe bounced back. I have to remember that I updated the SE file. I can't ask him about file dates b/c typically Access updates the file date whenever the file is opened, whether or not the data is changed. I have to ask Tom if he updated the SE File. He says "Probably not. I vaguely remember an E-mail about that, but I was knee-deep in this other project and didn't get around to it."

How do I ensure that the SE is up-to-date? (Or does the database just download the new or current version on startup, in which case I'm not saving much (as I see it) over having it populate the local tables from the BE.

 

[ebs17]

How do I ensure that the SE is up-to-date?

There should only be data there that relates solely to this one user and his work.

The information that is important across users must be in the BE. In order to save such data locally, you could load it when you start the FE and store it here
- in TempVars
- in the text boxes of an invisible form
- in specially created properties

 

[Marshall Brooks]

@ebs17 - Thanks, I misunderstood. In that case, the duplicated tables are Not really advantageous and I'm am probably best continuing with loading the local tables on startup.

 

[gemma-the-husky]

You might be able to add a new table or edit an existing table if it's not in use. You won't be able to set RI against an existing table that's in use, and you won't be able to do a C&R. I would only do stuff on a DBS that's in use of there was absolutely no alternative.

 

[Marshall Brooks]

Thank you all. I re-thought my use of imported linked tables and dropped them back from about 10 tables to only 2. The database loads more rapidly, but interim lookups take longer. But the tables get real-time updates and I can modify the back-end structure (with exclusive access) without immediately having to release an updated FE.

 

[gemma-the-husky]

@Pat Hartman
And yet the skilled designer, with a thorough knowledge of the development platform, and a deep understanding of the project environment can create stellar applications in a fraction of the time that the much more expensive system-based design team can produce relative rubbish. It may not meet the clients real requirements, or it may force clients to have to produce a design specification. Especially when the client often doesn't really understand what they actually want/need, or maybe doesn't realise how they could get something far better than what they have asked for.

 

[Marshall Brooks]

@Pat Hartman - You would be surprised who I work for. I'm not looking to divulge it here, but we own Sikorsky now.

That said ... yes, requirements are different - even for government contractors for a large production database as opposed to an internal database that 8 employees use to track statuses.

I never said I didn't plan to do testing. In fact, one of the primary advantages of going back to linked tables, is that if I want to add a new field to a backend table, I can do so and then do my development platform testing without having to release a new FE and then find out in tested that I need to release yet another new FE.

I am fortunate that most of the errors are things that I introduced and I can work around (usually) if needed.

For example - I just recently added validation to some of the data fields, but I forgot some of the potential conditions. If needed, I just made the updates to my development FE and/or "I" have direct access to the tables and could make the changes there - which avoids the validation.

Point taken that if I'm not available someone else has to figure it out or they need to come up with a workaround - which is not good practice.

 

[Marshall Brooks]

The POINT is that you need at least some separation between your development and production environments and experience has convinced me that playing fast and loose with this concept is dangerous.

Concur - you need a reasonable solution. For me:

• For major changes, or anything where I know I will want to create new records and not have somebody asking what this record is about, I work on a COPY of the BE with the DEVELOPMENT FE. The two risks here:
  • Obviously, the major risk is if I forget to change the linked BE from the copy to the main BE and release the database, or don't realize I am using the Copy BE, users "think" they are changing the production data rather than the copy data. Then they have to go back and update anything they changed in the production BE.
  • I have to log pretty carefully what I've changed, so that I don't make changes to the COPY BE that work fine and then get errors when I link to the production BE b/c it doesn't have the same changes.
• For minor changes, I'll just update the development FE with the production BE and use the development FE normally as part of the informal testing. Two risks here:
  • If I release too quickly, I haven't tested each condition, and someone says "Hey, I get an error when I'm doing this." And I have to figure out why the error occurs and fix it and release another update.
  • If I release too slowly, I tend to forget what changes haven't been released yet. So I tend to think "Don't worry about that, the FE already checks for and corrects that!". No - the DEVELOPMENT FE checks for and corrects it, the PRODUCTION FE ignores it.

 

[Marshall Brooks]

This would NEVER happen if you used a local version of the BE.

Indeed! I hadn't considered that, but it makes perfect sense. Right now, the copy BE is on the network different subfolder. The production Database BE is in a folder named "Back End" and the development copy is in a folder called "Back End Test". I doubt the other users know where it is, but I think they DO have access to it.

Let me ask you something about the linked Table Manager:

I'm hiding the network path, but you can see that the box is unchecked, but the database still reads from the backend. I used the folder names above, b/c I can just click Edit and Add or Remove Test and be at the correct level.

The reason I did it this way is b/c there are multiple networks paths that might work. For example - my path might be \\domain.subdomain1.company.com\ ...

S:\ might work for me, but not if users have their network paths named differently. \\domain.subdomain2.company.com re-directs to the same files, but might be slower or less reliable.

I've had times that I just browsed for the folder and ended up with both subdomain1 and subdomain2 listed.

An obvious solution is to copy the production path and the development (local) path to Notepad and copy and paste as needed.

Is there a way to have both listed? I.e. could I click add and add "C:\Users\MyUserName\Documents\CopyBe.accdb and then whichever one is checked is used by the development FE?

That takes discipline. Making DDL is the best solution. Make the DDL FIRST.

I need to look more into how to use DDL. I can find the previous links, just haven't gotten to researching it yet.

 

[ebs17]

Structural changes on the backend
Here you will find a small collection of instructions from DDL, DAO and ADOX.
It always makes sense to check a point first and then act accordingly
=> if FieldX does not exist in tableY, then create it

This means you can run such code as often as you like without errors and then accumulate as many actions as you want.

 

[Marshall Brooks]

Structural changes on the backend
Here you will find a small collection of instructions from DDL, DAO and ADOX.

Assuming I spoke or read German, which I do not ...

 

[Marshall Brooks]

I never said users were re-linking to the BE. "I" have to sometimes and I've been tripped up doing it incorrectly.

Nifty form and good suggestion on the text field in the database.

Thank you!!!

 

[gemma-the-husky]

I would never use the internal table link manager. I store the necessary links in the front end, and rebuild them if any are missing as part of the start up process, all in code.

 

[Marshall Brooks]

@Pat Hartman - Not trying to look stupid, but I was playing with the relink form example, and I can't see how to get it to work ...
I open the database and I get:

I click OK and I get:

If I paste the development BE in the New DB Name Field, nothing happens. If I click Browse, I get the "A file was not selected" message. I don't see a way to edit or change the Old DB Name fields. The Relink buttons give me an "Please select a New DB Name before clicking the Relink button."

Also - I don't want you to remake the form, but for my use, what I would like to see is the current BE listed on the New DB Name Field, and then the first text box would be the production BE location and the second text box would be the test BE location, and relink would connect to the selected BE and disconnect from the other one.