Help with be.mdb

[kymjay]

Hi all,
I read the faq181-90 post and was very successful in creating the .mde file which helped to solve a bunch of problems in my application. But now I have a couple of questions.
1. When I look at the three files that are there, the xxx.mdb, xxx_be.mdb and the xxx.mde files, I can see that the .mdb and .mde files are updated as the users’ use them daily but the _be.mdb file is not. I thought that the _be.mdb file would be because that is where the data sits. Why is that?
2. Also, when there is an error in the data and I need to correct it, I would think that I would correct it in the _be.mdb file but I am not sure, where should I go in and correct?
3. One more question, I now need to change the code in the VB program and recompile. Due to the change, I will need to change one bit of information in one table. Do I need to make a new backend and .mde? If so, should I be using the current .mdb file to create the new backend and .mde?

Thanks for all your help. This is a little confusing for me but I am getting there.

Kim

 

[MajP]

When you split a database the backend has just the tables. The front end has the forms, code, reports, and queries. In the front end you make a link to the back end that allows you to work with the backend tables as if they existed in the front end. Without getting technical, you basically can work with the tables without really opening the backend database. It is like tunneling into the backend. That is why the file is never modified.
If you need to modify the data you could open the backend, or the front end. The linked tables in the front end function as if they were resident in the front end. Normally it is set up so that the user never works with the backend. All data manipulation is through form's and queries in the front end.
If you change code in the front end you will need to recompile and make a new mde.
If you change data in the backend or change the structure of the backend you do not need to do anything because the users should be using the front end and only linking to the backend.

There are some good articles on the web. See if this helps:

http://www.databasedev.co.uk/split_a_database.html

 

[kymjay]

Thank you, that was great but it may be a little over my head. That is why I asked the specific questions which your information supplied a lot but it did not exactly answer my questions, it just created more questions (LOL). I am assuming what you are calling the front end is the .mde, correct?

Back to my original post, why is the be_mdb not updated regularly if it houses the tables or are you saying it just houses the table structure not the data (that is still my question 1)? Concerning my question 3, to recompile and make a new mde what do I use as a starting point? Do I use the current mdb?
Thanks
Kim

 

[MajP]

1) In your case the front end is the MDE. It should not have any tables in it just links to tables in the backend. You should icons that looks like a table, but it has an arrow. This indicates the table does not reside in the database, but there is a link to the table
2) All tables and all data reside physically in the backend.
3) I have no idea why you can work with the backend linked tables without the file last modified property getting updated. Not sure what the big concern is, but that is just the way it works.
4) To recompile and make a new MDE you would have to start with the MDB. With the MDE you no longer have access to any code. You can however modify forms, reports, and tables in the MDE if that is all you need to do.

 

[kymjay]

Oh, that helped so much. Thank you. Now I have one problem, I think I messed something up when I tried to make a change a few weeks ago. In your response #1 above you said I should see the little blue arrows in the MDE but when I looked inthere it was not there. Instead I saw the little blue arrows in the MDB. Why is that happening?

So I think I am going to make my changes, recompile and start over. But I have one other question. If my BE.MDB is larger in size than my MDB, can I assume correctly that is because data has been added by the users to the BE.MDB? Therefore, the BE.MDB has the most current data. How do I use that to be my starting point (MDB) for the database split? I need to use the most current data that they have entered so no data is missing when I recompile. Does that make sense?

 

[MajP]

You can do all of this manually, and it may just be a lot easier.

1) Create 2 brand new databases files completely blank. Call one something like "xxxx_BE.mdb" for the backend, and the other "xxxx_FE.mdb".
2)Open up the blank backend and choose "File", "Get External data", "Import". And pick only the tables from whichever one of your databases has the most current tables. You are done with the backend.
3) Open the blank front end and choose "File", "Get External data", "Import". and pick from your old front end and import only the forms, reports, macros, and code modules.
3) With your new front end go to "Files", "Get External Data""Link Tables". And link the front end to the back end tables. This front end is now your design master
4) Now if you need to lock down your code because you are going to distribute it, Open the front end. Select "Tools", "database utilities", "make mde".

If you need to make changes to the tables do that in your backend. If you need to make changes to other objects do it in your front end mdb and if you want use the front end mdb to make a new front end mde.

 

[MajP]

By the way, it sounds like it was not done correctly. If you did not see the arrows in your MDE then it tells me you were working on local tables and not linked tables. That means you really did not have the data base split and no changes were occuring in the backend.

 

[PHV]

MajP, I'm afraid you forgot the queries in your post timestamped 15 Jun 10 16:04.

 

[MajP]

Thanks PHV.

Correction to line 3 above.
3) Open the blank front end and choose "File", "Get External data", "Import". and pick from your old front end and import only the forms, reports, macros, code modules, and queries.

 

[kymjay]

Thanks so much MajP. I really appreciate it. I am going to follow your instructions and work on rebuilding that tonight. I guess I was doing it wrong and I kind of figured that. Remember, I asked that question in my original post as question #1 because the be_mdb was not updating. So that explains it. You are the best. I will let you know if it works out good.

 

[MajP]

What is the big picture of what you are doing? Is this going to be a multiuser database with the backend on the network? How many users?

You may find that doing all of this manually gives you a better understanding. I never use the db splitter, and once you done it once manually it is just as fast.

 

[kymjay]

Hey MajP,
To answer your question above. It is an application created in VB that is used by multiple users and the backend is one the network. The number of users is approximately 10 give or take at any given time. Also, you are correct, it is actually clearer to me doing it this way but I had one hiccup.

In your step 3, you said to import only the forms, reports, macros and code modules into the blank front end. If I am only importing these and not any tables, how will I link the tables in the front end to the tables in the back end? Am I confusing myself again?

Thanks
Kim

 

[MajP]

To link to tables in a be database it is a little different in 2007 than previous versions

With your new front end go to "Files", "Get External Data""Link Tables". And link the front end to the back end tables. This front end is now your design master

with 2007
Go to the external data tab. Select Access and the next pop up has a radio button to "import" or "link"

So now in the front end you see the tables with the little arrow icon. These tables are not in your front end, but you are linking to them in the backend. You can go to design view on these tables, but it is read only. To design the tables you need to go into the backend. You however can use these tables in the front end just like if the tables existed in your frontend.

 

[kymjay]

Thanks MajP. I think I am clear now. It works beautifully as I can see arrows in the FE table but not in the BE. It is easier this way. Thank you so much. You have been a tremendous help.

 

[MajP]

Also make sure that each user has a front end on their local computer. Some people will split the db, but put the front end on the network. This defeats the whole purpose of splitting the database. Once you are happy with your front end mdb store it as your design master. Now make an mde so that you can lock down your design. Copy the mde for each user. If you need to modify your front end (forms, reports, queries, code) make the changes in your design master, and then make updated MDEs for your users.

 

[kymjay]

One more question MajP, you mention putting the front end on the users PC, not on the network. If I am putting the front end on each users PC, should not the application itself point to the front end on the users PC? I guess I am asking because I had the .mde on the network and the application pointed to that. Should I now put the .mde on each computer in the same directory and let the application point to that same directory where the .mde is on each computer?

 

[MajP]

Sorry, I was not clear on the terminology.
1. The backend is the database with only tables. That sits on the network so that everyone can share the same data. If you made this into an .mde it really does not do anything. A .mde protects the forms, reports, and code from design changes not the table.

By converting your Microsoft Access database into an MDE file you will prevent your database users from tampering with your VBA code. When a user accesses an MDE file, they cannot:

* View, modify or create Microsoft Access forms in design view.
* View, modify or create Microsoft Access reports in design view.
* View, modify or create Microsoft Access modules in design view.
* Add, delete or change references to databases or to object libraries.
* Change any VBA code - there is no source code available.
* Import or export any forms, reports or code modules.

2. The front end resides on each user machine and it has the forms, reports, queries, macros, and code. It links to the data on the shared network backend. In this way you only pull data across the network not the forms and reports. The Front end could just be an .mdb. But if you want to lock it down so that the users can not modify the code, forms, and reports you can make it into an .mde. So in your case you distribute an .mde as your front end.

However, you can not edit the .mde either. So you have to revert back to your desingn master .mdb to do changes. Then you have to make all new .mde and redistribute.