Attachments to ACE db broken

JohnPapa

Registered User.
Local time
Today, 22:22
Joined
Aug 15, 2010
Messages
1,117
I have my FE BE software running on a network of 13 pcs, without a dedicated server. The ACE db is on a separate pc.

I was in the process of installing an update (new .accde) and everything was going well until pc #11. When I installed the .accde on this pc and I run the software to check that everything is OK the attachments on the other 10 pcs were broken and I had to re-attach. Once logged out of the software on pc11 and I reattached on any of the other pcs, the other pcs were up and running.

I switched pcs etc but I experienced the same problem.

Any ideas, why the pcs lost their attachments? The software is written in Access 2013and it is an old proved software that dates back to 1997 (www.VisualDentist.com)

I know that the owner needs a dedicate server and have mentioned this 100 times.
 
OK, first - there is no real problem (protocol--wise or configuration-wise) with having a back-end hosted by a non-server PC. As it happens, Access does not use any of the features of the Windows Server operating system. Windows Home or Windows Office can do what is needed.

However, the fact that one particular PC has a "gotcha" is grounds for comparing the configuration on that PC to the others to see if there is a difference in Windows versions or Access versions. I'm going to SUSPECT that something is wrong with the setup for PC #11 - but this might not be AS bad as it could be.

I would go into that PC and look at the configuration of the FE file using File >> Options >> Current Database. The settings OUGHT to follow the copy of the FE, but double-check it. Some things are left in the registry, which means that some settings would survive copying a new FE over the old one. (Though I didn't think THAT particular setting was in the registry.) The OTHER possibility is that somehow PC #11 doesn't recognize where your BE is kept as being a "trusted" folder and that this becomes some weird kind of permissions/exclusivity thing. And THAT is DEFINITELY in the registry, which means that a single PC could cause havoc while others are blithely ignorant of any issue.

The File >> Options >> Current Database suggestion is cheap and easy and non-destructive. And once you are in the File menu, you are near where you would have to check on Trust Center settings anyway. So I don't feel bad about asking you to check on the database "Open Exclusive" setting in passing.
 
I assume when you say "attachments were broken" you actually mean the links to the back-end tables (within the other 10 accde front-ends). It might be caused by a recent bug from Microsoft see this (there are numerous posts in this forum about this recent issue):

Cheers,
 
1. The last thing you should do before making the .accde is to relink the tables to change from YOUR test BE to the production BE. It is best to NOT use mapped drives. Use the UNC path. That means that the drive where the BE is located can be mapped as X: on one PC and Y: on all the others and the links won't break because they say \\servername\foldername\be.accdb
2. There are some very easy ways to distribute updates to the FE. Maybe you should switch to one rather than going to each of 11 PCs one at a time. The simples method is with a shortcut to a batch file. A more complicated method is to use a separate distribution DB. Several have been posted here. If you can't find them, just ask and we'll give you links.
 
Hi. Just curious... So, what is stopping you from using a dedicated server?
Thanks for the reply. Nothing is stopping me from using a dedicated server, but this is the decision of the owner who has been warned, as I mentioned, 100 times.
 
OK, first - there is no real problem (protocol--wise or configuration-wise) with having a back-end hosted by a non-server PC. As it happens, Access does not use any of the features of the Windows Server operating system. Windows Home or Windows Office can do what is needed.

However, the fact that one particular PC has a "gotcha" is grounds for comparing the configuration on that PC to the others to see if there is a difference in Windows versions or Access versions. I'm going to SUSPECT that something is wrong with the setup for PC #11 - but this might not be AS bad as it could be.

I would go into that PC and look at the configuration of the FE file using File >> Options >> Current Database. The settings OUGHT to follow the copy of the FE, but double-check it. Some things are left in the registry, which means that some settings would survive copying a new FE over the old one. (Though I didn't think THAT particular setting was in the registry.) The OTHER possibility is that somehow PC #11 doesn't recognize where your BE is kept as being a "trusted" folder and that this becomes some weird kind of permissions/exclusivity thing. And THAT is DEFINITELY in the registry, which means that a single PC could cause havoc while others are blithely ignorant of any issue.

The File >> Options >> Current Database suggestion is cheap and easy and non-destructive. And once you are in the File menu, you are near where you would have to check on Trust Center settings anyway. So I don't feel bad about asking you to check on the database "Open Exclusive" setting in passing.
Thanks for the reply. I forgot to mention that all pcs work from the Runtime version of Access 2013, so I cannot directly do what you mention. I could programmatically. You have included some useful info.

The solution to the problem as was suggested below was the Microsoft update. Please see my comment below.
 
If the link above is what talks about the software, upsizing it to sql back end as well as having a dedicated server is the way out.
Thanks for the reply. I have two dental practice software packages
1) A desktop which is exhibiting the problem (www.VisualDentist.com)
2) A cloud based software package, on SQL Server hosted on Azure (www.VisualDentistCloud.com)

Regarding your suggestion, having the knowledge of upsizing to sql backend, needs months of work and although I agree with you that having a dedicated server is advisable, as you can see below the problem was the Microsoft update KB5002104.
 
I assume when you say "attachments were broken" you actually mean the links to the back-end tables (within the other 10 accde front-ends). It might be caused by a recent bug from Microsoft see this (there are numerous posts in this forum about this recent issue):

Cheers,
Vlad, thanks for the reply and for hitting the nail on the head.

The problem was indeed Microsoft update KB5002104.

Once this was removed, everything worked perfectly.

You cannot believe how much time you saved me.

Regards,
John
 
1. The last thing you should do before making the .accde is to relink the tables to change from YOUR test BE to the production BE. It is best to NOT use mapped drives. Use the UNC path. That means that the drive where the BE is located can be mapped as X: on one PC and Y: on all the others and the links won't break because they say \\servername\foldername\be.accdb
2. There are some very easy ways to distribute updates to the FE. Maybe you should switch to one rather than going to each of 11 PCs one at a time. The simples method is with a shortcut to a batch file. A more complicated method is to use a separate distribution DB. Several have been posted here. If you can't find them, just ask and we'll give you links.
Hi Pat, thanks for the reply. I have been thinking of an automating the FE .accde deployment, but could not bring myself to do it. Here are the steps that need to be done. Mind you that all pcs running the FE .accde have the same mapped drive which points to the BE .accdb. Sometimes I add fields and/or tables to the BE .accdb, depending on new functionality:
1) I copy the BE locally to a pc and put it in a folder together with the FE (the default is C:\VisualDentist).
2) When I run the FE using the runtime C:\VisualDentist\VD.accde /runtime, the BE may or may not be updated, depending on whether there is new functionality.
3) I then copy the BE, back to the folder, where the common mapped drive is pointing.
4) I reattach the FE which at present points to the local BE, to the BE where the mapped drive is pointing.
5) BTW my FE is locked to a specific BE (cannot use the FE without the specific BE), so for every PC I need to bring to a local PC the clean FE and BE, run it and then reattach to the BE where the mapped drive is pointing.

I believe the process above is a mouthful, but would be more than happy to view any suggesting that you may have, for automating the process..

Thanks in advance,
John
 
having the knowledge of upsizing to sql backend, needs months of work
I can upsize any application that I built in a couple of hours of testing. Only rarely need to change code or queries. If I upsize someone else's app, I might need a week depending on how bad the app is. The one app I sell has the option of either SQL Server or ACE. I even did a custom install for one client of Oracle. The FE has one section that had to be different for SQL Server. The app comes with a set of test data that matches some external word and excel documents. So, the user can reset the training data after he is finished with training new people. In order to make this work, I needed to retain the original autonumbers. This is easy in Access but not so easy in SQL Server so I used a couple of lines of different code to get past the SQL Server problem. Otherwise, there is no difference except when I have to modify the BE. For the SQL Server clients, I send a T-SQL script and everyone else gets an Access db to do the conversion to the BE.

2. Updates to Both FE and BE need to be coordinated. If these are running at a customer site, I create a conversion db to upgrade the BE from version x to y. The client has to run that in addition to replacing the master FE with the new version. One decision I made early on was that I was NOT going to ever delete data for apps I built to sell. The data belongs to the client. So, future releases might stop using particular fields or tables but the BE update doesn't get rid of them.
3. I include a relinking form. The new FE is copied to the master directory and opened there at the client site. Tables are relinked there.
5. I use a product key but the connection to the be can BE done using hidden tables. I also use tables in the BE and FE to ensure that the versions match.

Then when users open their shortcut, the new FE is downloaded.
 
Last edited:
To facilitate the dual BE, I used a tool named SQL Examiner. It is really hard to keep track of BE changes and since I wasn't going to be there to fix problems, I had to get it right. SQL Examiner compares two databases (various formats but not ACE) and creates a script to make one db = the other db and this is what I used to send the client. I used it for my ACE BE also by upsizing the ACE BE and running it on that also. I couldn't use the script directly. I had to break it into individual DDL queries since Access can only run one query in a querydef. Then the db just ran a code procedure that executed all the DDL queries in a loop.
 
Hi John,

Glad to hear you found the problem. Regarding the front-end deployment you might want to check out my free launcher (http://forestbyte.com/ms-access-utilities/fba-db-launcher/) that should be able to automate that task. The idea is to put the updated front-end in the same location as the back-end (in the shared mapped drive) with a higher version number than the local FEs. On my site you will also find a solution that helps with sending back-end updates such as adding fields from within the front-end itself (might not need that as you seem to have full access to the back-end).

Cheers,
 
I can upsize any application that I built in a couple of hours of testing. Only rarely need to change code or queries. If I upsize someone else's app, I might need a week depending on how bad the app is. The one app I sell has the option of either SQL Server or ACE. I even did a custom install for one client of Oracle. The FE has one section that had to be different for SQL Server. The app comes with a set of test data that matches some external word and excel documents. So, the user can reset the training data after he is finished with training new people. In order to make this work, I needed to retain the original autonumbers. This is easy in Access but not so easy in SQL Server so I used a couple of lines of different code to get past the SQL Server problem. Otherwise, there is no difference except when I have to modify the BE. For the SQL Server clients, I send a T-SQL script and everyone else gets an Access db to do the conversion to the BE.

2. Updates to Both FE and BE need to be coordinated. If these are running at a customer site, I create a conversion db to upgrade the BE from version x to y. The client has to run that in addition to replacing the master FE with the new version. One decision I made early on was that I was NOT going to ever delete data for apps I built to sell. The data belongs to the client. So, future releases might stop using particular fields or tables but the BE update doesn't get rid of them.
3. I include a relinking form. The new FE is copied to the master directory and opened there at the client site. Tables are relinked there.
5. I use a product key but the connection to the be can BE done using hidden tables. I also use tables in the BE and FE to ensure that the versions match.

Then when users open their shortcut, the new FE is downloaded.
Hi Pat,

Many thanks for the info. Our SQL version of the software uses Web services that we created and it is not a simple upgrade and move from ACE to SQL Server. We used .net and the data is not bound as in the case of the desktop. In our case it was a compete rewrite. For a simple application. I would agree with you, but for a dental practice management software which handles everything under the sun, with lots of graphics and complicated logic, you need many months and I am being optimistic.

I would look through the other recommendations.
 
Hi John,

Glad to hear you found the problem. Regarding the front-end deployment you might want to check out my free launcher (http://forestbyte.com/ms-access-utilities/fba-db-launcher/) that should be able to automate that task. The idea is to put the updated front-end in the same location as the back-end (in the shared mapped drive) with a higher version number than the local FEs. On my site you will also find a solution that helps with sending back-end updates such as adding fields from within the front-end itself (might not need that as you seem to have full access to the back-end).

Cheers,
Thanks for the info which I will review. One twist to the setup is that the FE is locked to the specific BE and the specific pc, so the FE it has to be opened on the specific pc.
Since all communication takes place through the same mapped drive it is theoretically possible to change the links to the FE prior to opening it and in this case I would be able to simply copy the FE. I will test.
 
Last edited:
you need many months and I am being optimistic.
Of course you do if you rewrite the application to be unbound.:) But, as I have pointed out many times, Access is a RAD (Rapid Application Development) tool. The reason you can create applications with Access in a fraction of the time needed for other platforms is because of the RAD tools. When you choose to not use the RAD tools, you eliminate any reason for using Access at all. You would be better off using a platform where you can create an executable or for the web, run it in a browser. That eliminates the security risks and distribution issues caused by using Access I have many complex applications, some with multi-million row tables and they all use bound forms and linked tables. Most can swap between ACE and SQL Server on command if I set them up that way. A client wouldn't do that except on install. But it does give them the option of installing with an ACE BE or SQL Server. Or for a couple of hours of a consulting fee, use any standard RDBMS on the market. That kind of flexibility is available ONLY because I am using Access as the FE.

For the apps I sell, I keep the ACE/SQL Server option to give clients a choice and for my own sanity, I manage only one code base that works with ACE and RDBMS both. The SQL Server BE is obviously more flexible because even with a sound LAN, the ACE BE has a hard limit of 255 concurrent users but starts to break down when you get more than a hundred users at a time but with SQL Server, you are only limited to the customer's SQL Server seat licenses so technically, you could have thousands of concurrent users with an Access FE and a SQL Server BE.
 

Users who are viewing this thread

Back
Top Bottom