Question Best way to implement new code in MDE environment (1 Viewer)

[RoyinForest]

I have an application using an MDE database and have a new version of it to implement. I have the MDB file for the new version and have not yet split it.
I also have the old MDB file for the existing version.

My dilemma is finding the best way to upgrade to the new version and migrate the data to it - without losing data. There are 3 new fields in the new app so I know those fields will need to be populated another way. (not a problem)

What is the "best practise" for extracting data from the old app to then load into the new one?

 

[MarkK]

If the old mde is not split, why not just use it as the BE for developing the new app? If you plan to split the new DB anyway, it doesn't make sense to me to bring all the data into your new DB, and then split it out again. So your old mde is an instant BE. Done.

And adding fields to the old table should be simple. If the new app hasn't been deployed yet, then there is no data in the new fields.

Does that make sense, or am I missing something?

 

[RoyinForest]

I appreciate your help. Let me try to explain better. Several years ago I wrote an application to track service calls, time, parts, invoices, etc for a plumbing company. (friend of the family). The app was split into a FE/BE config so nobody would make alterations to it. Multiple computers in the office had shared access to the MDE file to run the app. The MDB was retained in case fixes or modifications were desired. It was not used to run the app.

Fast forward to last October when I was contacted to see if I could modify the app to make it more useful to them. The business had evolved and without modifications the app was only marginally useful.

I used the MDB file to modify extensively the old app. It has taken me 3 months since I hadn't touched Access in almost 6 years. I had to re-learn a lot.
During that time, the unmodified app has been in daily use, in it's MDE form.

So I am now ready to roll out the updated app. How to get the latest data into it? The data is in the old MDE. If I can "unload" it I can load it into either a new MDE or new MDB file.

Hope I've done a better job explaining this time around.

Thanks again for your help.

 

[Beetle]

Were the bulk of the modifications to the front end or the back end?

The data is in the old MDE

The data should be in the back end (you did say that this is a split application), which should not be a MDE file, the MDE should be the old front end, separated from the data.

If your modifications were to the front end, then you should just be able to link the new front end file to the existing data file as Mark said. If you made major modifications to the back end, then it may be a bit more complicated.

 

[MarkK]

If the original app was split then I don't understand the problem. In a split DB there is no reason to "get the latest data into it." The latest data is in the BE, so leave it there. The new app is the FE. Connect the new FE to the old BE. Done. That is why you do a split, so you can keep the user interface (queries, forms, reports--FE) and the data (tables--BE) isolated from each other, so they are completely independent.

Maybe there is a misunderstanding about the words "split" or "FE/BE" ???

 

[RoyinForest]

At the risk of exposing to all and sundry just how little I know about split FE/BE environments, here is what I did.

1. Made all the changes to the MDB file since it has the tables, modules, etc

2. When I was finished testing I used the "Database Tools" "Make MDE" button to create the MDE file.

3. Placed the MDE file on the LAN in a shared directory so multiple users had access. The MDB was saved in a non-shared directory in for future changes, maintenance, etc.

So now the future has arrived. I used a copy of the original MDB file and made all the changes to it. New forms, reports, modified tables, etc.

The MDB file does not have up to date data and I want to move the latest data from the MDE file to the new app (either to the MDB before I split or to the new MDE after I split). Whatever works.

Thanks again

 

[MarkK]

Can you answer these questions so we know we are talking about the same things...
1) What is a split?
2) What is the difference between an FE and a BE?
3) How do an FE and a BE work together?

 

[RoyinForest]

I don't know much more than I've already said about FE / BE etc. The database was "split" by the "Make MDE" button so that the tables, and code are protected from user changes. I originally thought it also meant that the MDE file had to have a path to the MDB file in order to save data, but that is clearly NOT the case. In the MDE file I cannot get to DESIGN mode, I can not see any tables, etc. BUT the database documenter does show there are over 51,000 records in the database.
I thought I could at least extract them to reload to a new database (MDB) but when I try I just get one record consisting of a header with field names.

It makes no sense that there is no way to back up, or unload a database if the file is an MDE vs MDB format but if that is the case I'll stop beating a dead horse.

 

[moke123]

do you have 2 seperate files? one a front end and a seperate 2nd backend?

 

[sneuberg]

Under the heading MAKING UPDATES LATER this web site outlines a procedure that seems to be what you want to do. I suggest making backup copies and giving it a try. Please let us know how it goes.

Follow these steps to update your MDE file:

1. Rename your MDE file as a backup file.


For example, add today’s date to the end of the file name (right before the .mde part). You’re about to create a new MDE file, but you don’t want to lose the data in this file.

2. Open the original MDB file and make any changes to contain the forms, reports, and VBA code that you want.

If you plan to make drastic changes, make a backup copy of the MDB first.

3. Choose Tools –> Database Utilities –> Make MDE File and save it as an MDE file with the name that your MDE file originally had.

Now you have an updated MDE file with new, improved forms, reports, and VBA procedures, but with old data. You also have an updated MDB file with your new, improved forms, reports, and VBA code (but out-of-date tables).

4. Delete all the tables from this new MDE file.

In the Database window, click the Tables button in the Objects list, click each table in the list that appears in the right pane and then press the Delete key for each table. You need to confirm each deletion by clicking the Yes button. Deleting tables sounds dangerous, but remember you have all these tables stored safely in your old MDE file.

5. Import the tables from the old MDE file to the new one.

Choose File –> Get External Data –> Import and choose the name you gave your old MDE file in Step 1.

You see the Import Objects dialog box, with tabs for Tables, Queries, Forms, Reports, and other objects.

6. Click the Select All button with the Tables tab selected and then click OK.

Access imports your tables from the original MDE to the new MDE files, replacing the older data in the tables.

7. Import any queries or macros in the old MDE database that you created or changed.

Repeat Steps 5 and 6, but use the Queries and Macros tabs on the Import Objects dialog box to import whatever has changed.

If you are going to do this often, consider splitting your table into a front end and a back end. With a split database, you don’t have to re-import your updated tables: You can just leave them in the unchanged back-end database.

 

[MarkK]

The database was "split" by the "Make MDE" button so that the tables, and code are protected from user changes.

This is not what a split is. A split means that the tables are in a different file than the forms and reports. A split database has a front-end (FE) with queries, forms, reports, and modules/classes which is a distinct Access file, and the FE connects via links to the tables in the back-end (BE) data file.

 

[GBalcom]

It seems like you need to go in at night or some other time when the office is closed, split the existing db, then link your new FE to the existing BE. (I'd make a copy first!)