Can you create a table in your back end while open in the front end?

[JMongi]

There are some older posts that imply that functionality MIGHT exist.

 

[isladogs]

Yes - using DDL
However, there should be very few occasions where this is appropriate

 

[JMongi]

So, what is your methodology if you are adding a table or two to an existing db that is already split.

 

[The_Doc_Man]

Normally, if you have an app that is in production and you have an update to the BE, you schedule a down-time for the functionality you want to add. Notify all users of the down time. It might mean you need to have odd hours to get it done because you would be the only person working at the time. When you get everyone out, immediately RENAME the BE file so that nobody can get in it. Then COPY the file to a place where you work on it. OR copy the file elsewhere and then work on it in place. (I prefer a separate working area myself, but that's just me.)

Make your changes to the DB. Usually, adding a BE functionality has to be matched by adding a FE function as well. So... while you've got it all down for maintenance, do your thing to FE as well as BE. Eventually you will rename the BE file to what it should be. When that happens, your users will be able to get in again. If you don't have an automatic FE updater then you need to send out a message to everyone to update the FE manually.

The way the Navy had me do my databases was to have several staging areas. The /DEV folder was where I worked. Had a .BE for the DEV file so I could use field and table definitions that actually meant something. The /TEST folder was where I would promote a DEV copy for testing. Had a TEST BE file separate from the DEV files - though USUALLY the DEV and TEST files were the same anyway unless the thing I was testing was a BE update, in which case the TEST files would catch up. Then, when a new version was ready to go, they went to the STAGING directory where I would start the process of converting all address references and label content. STAGING did NOT have a separate BE file. It pointed to the live BE file. However, if there was a BE change being staged, then it got more complex. Finally, once the links were all set up and I verified that the staging copy would open, I did the shutdown I mentioned to take users out of the DB. Then I copied anything that was needed over to the PROD folders. It was at this time that i would take the system down for maintenance, which ALWAYS included making a manual backup copy and a Compact&Repair operation.

If all I had was a FE patch, the user would be notified to download the new FE copy. But that wouldn't usually require downtime because I never allowed a FE patch out that would break something if it co-existed with older versions.

 

[LarryE]

Here is some simple code. Replace TblTest with your own name and Test field with your own name.

Public Function CreateTables()
On Error GoTo CreateTables_Error
Dim db As DAO.Database
Set db = CurrentDb
Dim myTable As DAO.TableDef
Dim myField As DAO.Field
Dim MyTableName As String
Dim MyConnection As String
MyConnection = DLookup("[DataBase]", "MSysObjects", "[DataBase] Is Not Null") 'Backend location
MyTableName = "TblTest" 'Table Name between quotes
Set myTable = db.CreateTableDef(MyTableName)
With myTable
    .Fields.Append .CreateField("Test", dbText) 'Field Name between quotes
End With
db.TableDefs.Append myTable
'If split database. Append to backend
If DCount("[DataBase]", "MSysObjects", "[DataBase] Is Not Null") > 0 Then
    DoCmd.TransferDatabase acExport, "Microsoft Access", MyConnection, acTable, myTable.Name, myTable.Name
    DoCmd.DeleteObject acTable, myTable.Name
    DoCmd.TransferDatabase acLink, "Microsoft Access", MyConnection, acTable, myTable.Name, myTable.Name
End If
Set myField = Nothing
Set myTable = Nothing
Exit Function
CreateTables_Error:
DoCmd.CancelEvent
MsgBox Err.DESCRIPTION
Exit Function
End Function

 

[CJ_London]

Or per post#19 of your other thread https://www.access-programmers.co.u...ssing-performance-issues.291269/#post-1847514
Use BEdb.execute (DDL sql here)

 

[Pat Hartman]

So, what is your methodology if you are adding a table or two to an existing db that is already split.

@LarryE loaded your gun for you. And CJ pointed the gun to your head. But I'm here to talk you off the ledge. NEVER, EVER modify the BE while it is open for users. NEVER!!!! Doc has outlined a professional solution. My procedure is slightly different.

Copy the BE to MY dev work area to replace my old copy of the BE. Rename the BE on the server as doc suggested.
Zip the BE so you have a backup from just before the change.
Make the changes to the copy of the BE in your dev area.
Test the changes thoroughly.
Copy the BE back to the server.
Zip the BE again.

The only reason for my changes is because it requires fewer renames and it also eliminates the need for me to relink for testing.

The method of changing the BE is different if you need to have it offline for more than a few minutes. Usually, if I am going to add fields or add a table, I take the BE offline for a few minutes and make the changes. Then I work on my local copy of the BE and make my changes to the FE. That way, I may not even have to take the BE offline again to update the FE to work with the new fields. However, if you are removing/renaming, changing the relationships etc. again, the updates to the BE might involve futzing with production data and that is more dangerous and needs planning and possibly an evening or weekend to implement the changes once they have been tested on your dev platform.

 

[LarryE]

Pat is of course correct. Make sure you take a gun safety course before you do this. I make no comment what so ever on whether you should or should not do this. You may have a very good reason. I have done it only once in many years of creating databases with ACCESS and then for very specific purposes. You may also edit field properties at the same time, but.....

 

[isladogs]

So, what is your methodology if you are adding a table or two to an existing db that is already split.

Similar to the above comments, with my apps, this is only ever done when updating to a new version.
The changes are built into the version update and are performed by the program admin before distributing the app to end users.

If the BE is an Access database, I use DDL for this i.e. a CREATE TABLE statement ....
For a SQL BE, I use a SQL script file created by SQL Server as part of the install file

I agree totally that maintenance work of this kind should only ever be done when the database is closed to end users.
Having said that, for the specific case of a new BE table linked to a new version of the FE (and which isn't yet available to end users), it is possible for the program admin to safely perform this change without taking the app offline.
Nevertheless, it is good practice to avoid this.

 

[JMongi]

Whew! Stirred the pot again
This is likely a case of me just being inexperienced, literally ignorant, of the functionality in Access I'm looking for.
Some clarifications:
1. This question is within the purview of working on development versions of the FE and BE.
2. The only reason this is an issue is because I'm not skilled enough to create a brand new table(s) for new functionality without needing to tweak it at some point possibly.

So, I slimmed down my current app to the basic structure of things to get everything up and running (user management, admin management, deployment, etc) plus a basic actual business process that we need to implement. Then, over time I will add further "modules" to the database to expand its functionality. Some of this functionality will involve many tables, queries and forms and their interactions and will likely be revised often due to my ignorance.

I envisioned working on these modules with the new tables residing (temporarily) in the front end as everything is developed and then when they appear suitable, "pushing" them to the development back end and continuing in development.

So, either there are a few ways everyone is doing this, or, this a totally stupid idea and I need to repent in dust and ashes

 

[GPGeorge]

Spoiler: etc

Whew! Stirred the pot again
This is likely a case of me just being inexperienced, literally ignorant, of the functionality in Access I'm looking for.
Some clarifications:
1. This question is within the purview of working on development versions of the FE and BE.
2. The only reason this is an issue is because I'm not skilled enough to create a brand new table(s) for new functionality without needing to tweak it at some point possibly.

So, I slimmed down my current app to the basic structure of things to get everything up and running (user management, admin management, deployment, etc) plus a basic actual business process that we need to implement. Then, over time I will add further "modules" to the database to expand its functionality. Some of this functionality will involve many tables, queries and forms and their interactions and will likely be revised often due to my ignorance.

I envisioned working on these modules with the new tables residing (temporarily) in the front end as everything is developed and then when they appear suitable, "pushing" them to the development back end and continuing in development.

So, either there are a few ways everyone is doing this, or, this a totally stupid idea and I need to repent in dust and ashes

Somewhere in the middle.

I don't think that many people would ever create new tables in a development copy of the Front End accdb and only export it to the production Back End accdb as the last step prior to deployment.

On the other hand, it's not stupid to consider it if there is some conceivable advantage to you as the developer to do it. I can't see any advantages, but that doesn't mean you couldn't concoct a scenario where there is one.

However, bouncing from one extreme to another is not really necessary and probably prevents logical evaluations.

I think the far more common approach taken by experienced Access developers is to have development copies of both the Front End and Back End accdbs. In that scenario, they start out with modifications to their master, or development, copy of the Back End accdb. When they are ready, they export modified tables and new tables into the production copy of the Back End and re-release it into testing for later release into production.

I can't imagine a scenario short of a five-alarm emergency, though, when anyone would consider pushing changes to either the Front End or Back End of a production relational database application while it is in normal use, or even potentially in use. Safety of the data comes first. Risking corruption of the data by updating a live, in use accdb is simply not common sense. I doubt it would get approval from management.

 

[JMongi]

So, what I'm hearing is that, in general, all of you experienced developers when adding functionality to an existing database (within a development setup, NOT production):
1. Directly open your development BE and create any new table structures and relationships.
2. Close the development BE
3. Open the development FE and link those new tables
4. Continue with queries/forms/reports etc in the development FE
5. Test
6. Inititate whatever your specific development deployment process is

So, you just don't have a need to modify any tables after #1 occurs or so rarely that you just create everything in the development BE.
I'm beginning to see this is a me problem. I change my mind, my ideas are dumb, don't normalize properly, etc.
So I was looking for a solution to allow these table changes (due to my dumbness) without needing to constantly close the devFE, open the devBE, make changes, close devBE, open devFE and continue.

 

[CJ_London]

If you want to do it 'properly'

Usual process is to have three environments - development, testing and production. In addition there should be a full specification of what the app is required to do and how it is intended to operate - where it gets the data from, what it does with it and what it outputs.. You often get 'user stories' which will typically clarify what a form needs to looks like, how it is accessed etc. Some of these are open to negotiation to find compromises when required or during development wrinkles are found and the spec needs to be changed or clarified. For changes to existing apps these documents may be created by the developer - but still need to be signed off by the users.

From these documents 'test scripts' can be created that testers can follow to check the app is working as intended. At the low level might be checking a control for a field will only accept 5 characters or that must be numeric a date field that cannot have a value greater than today. Or it might be users with limited read/write capabilities cannot get beyond that.

The development environment is whatever it needs to be but will typically mimic the production environment if 'pre testing' testing (i.e. a split db but all in the same folder). It will use whatever test data the developer deems necessary. Once development is considered complete (or at least reached a point where users have something to test), a copy is moved to the test environment and this definitely needs to mimic the production environment - so backend on the server, FE on the users machine. The data (or a subset) can be copied from the production environment. Tests are undertaken and any issues flagged as critical or not critical. A critical flag means further testing in that area has to cease until fixed, but testing in other areas can perhaps continue. In any event the errors are reported back to the developer to fix and reissue a new copy for testing - at which point the test BE needs to be refreshed.

Once all testing is complete, the new FE can be copied to the production environment and any changes to the BE made - this might mean changes to the original BE or copying all data into a new BE.

 

[JMongi]

Somewhere in here we seem to be having a bit of a disconnect.

I am NOT asking how to handle deploying a development database through testing and release to the production environment. I've read in this thread and others some very fine examples of this process from @The_Doc_Man, @Pat Hartman and others. But, again, to be clear. This is NOT what I'm asking about.

My question is narrowly focused on the idea in this quote of myself:

So I was looking for a solution to allow these table changes (due to my dumbness) without needing to constantly close the devFE, open the devBE, make changes, close devBE, open devFE and continue.

It's becoming quite clear that this is not something anyone other than me is worrying about or needs to do!
This is only an efficiency question as it is quite doable to perform the database close/open dance whenever needed. I just mistakenly thought there might be a better way. I apologize for the confusion.

 

[CJ_London]

So I was looking for a solution to allow these table changes (due to my dumbness) without needing to constantly close the devFE, open the devBE, make changes, close devBE, open devFE and continue.

Providing you don't have forms open in the FE linked to the BE (or other method maintaining a persistent connection) you can open the (development) BE without closing the FE. If you were to create the table in the FE with the intention of copying across to the BE - look at using the export feature, then the linked table manager.

Quite frankly the time it takes to copy over is going to be much less and easier than writing a DDL query

I'm beginning to see this is a me problem. I change my mind, my ideas are dumb, don't normalize properly, etc.

Carpenters (not doubt others) have expression - 'measure twice, cut once'. Perhaps spend a bit more time thinking about what you need to do - sketch it out on paper, draw a diagram, whatever before starting to actually create or modify your tables.

I look at some of the stuff I did years ago and think 'why on earth did I do it that way?'. If I was to do it again today, I might well take a different approach because I have a better understanding of what works and what doesn't plus I have learned new methods (or new methods have come along)

 

[GPGeorge]

Somewhere in here we seem to be having a bit of a disconnect.

I am NOT asking how to handle deploying a development database through testing and release to the production environment. I've read in this thread and others some very fine examples of this process from @The_Doc_Man, @Pat Hartman and others. But, again, to be clear. This is NOT what I'm asking about.

My question is narrowly focused on the idea in this quote of myself:

It's becoming quite clear that this is not something anyone other than me is worrying about or needs to do!
This is only an efficiency question as it is quite doable to perform the database close/open dance whenever needed. I just mistakenly thought there might be a better way. I apologize for the confusion.

Thanks for clarifying. However, I think you may be overcomplicating even that. There is probably little reason to keep closing and reopening the back end like that.

There are some older posts that imply that functionality MIGHT exist.

In this original post, there was not attempt to put the question into context. That appeared only in post #14. In other words, the way the original question was posed was confusing.

You finally decided to explain the reason for the question, and it turns out to be rather trivial. If you are working on the back end and front end simultaneously, you have two choices.

1. Keep them both open and switch back and forth as needed.
2. Open and close them repeatedly when switching back and forth.

 

[JMongi]

@GPGeorge - I deliberately did not put the question into context because I did not want to muddy the waters. Perhaps that was a mistake. Colin answered my question just fine and I was trying to get some elaboration from him. By the time he resonded to my question in post #3 the thread had already been slipping. Anyway, what's done is done.

As is often the case, because the answer is trivial, it is assumed that I wasn't actually asking it. In this case, it is the very essence of what I am looking for (because apparently I've missed something obvious). When I open the devBE while having the devFE open they do not both remain open. How do I open both at the same time. This would indeed negate my question entirely.

 

[Pat Hartman]

I think the measure twice, cut once reference covers it. I change tables after I make them. Usually to add fields I forgot. Sometimes to change names. But, It's not like I have to do this 5 times a day. And I don't understand why updating the BE is such a chore for you. It is a PITA if you change a column name. But nothing else should affect anything. Just open the BE and make the change. If it is to add a new table. Add the new table and then link it in the FE.

 

[Pat Hartman]

Databases do not close automatically. If you open the BE, it stays open until you close it. You can't have a table in design view and expect to use it in the FE but aside from that, there is no problem having both open at the same time

 

[The_Doc_Man]

The only think I can think of that you could do simply that might SLIGHTLY speed up the process is to build the table in your dev BE file. Do your dev testing. When you are ready to make the big swap, make a transitional copy so that you can change the links in the transitional FE from dev to production. Erase the content of the new BE table if that is appropriate.

Then in your BE down-time, use the External Data feature to import the new, already-detailed table into the BE file. You don't have to reiterate manual creation of the table and fields. Now, if you used and kept the DDL for the new table when you built the dev copy, you can just run that again because DDL is pretty fast. But if you had built it and tweaked it by hand, just DELETE * FROM the-new-table and then import it to the live copy. (Or export it from the transitional copy, since that external data facility goes both ways.)