Funny characters in record

[JohnPapa]

In a specific record there appeared funny characters as well as oriental looking characters. These characters were not visible in the form but were visible in the underlying table. Also the specific record could not be deleted through the software and I had to delete it from the underlying table.

The incident appeared in only one of maybe 100 installations of my software (www.VisualDentist.com)

Any ideas would be welcome.

Regards,
John

 

[RainLover]

That is a typical sign that your Data is corrupt.

You need to delete these records then find the cause of the corruption as it most likely happen again.

eg Are you sharing the Front end.

 

[JohnPapa]

The network is made up of 2 pcs. When the problem first appeared I used 2 different mde's, one on each pc. Now I use 1 mde which resides on the pc with the data. Both pcs invoke the same mde.

 

[RainLover]

You need a Front End and a Back End Data Base. (Split DB)

The Back End (BE) holds the Data and the Front End (FE) is your Forms Reports Queries etc.

Each user should have their own FE with the tables linked to the one BE.

The BE will have the current corrupt data. Suggest you back up the BE then delete the corrupt records. These are lost unless you want to pay money to have an expert attempt a recover.

You should back up the BE regularly. I have done this from hourly to daily. Its up to you to choose the period. There should also be a master FE for development that you back up even more often when you are working on it.

Hope this helps a little.

BTW I have assumed you have corrupt data based upon what you said. I cannot guarantee that I am correct. Again your call however a back up of the BE will allow you to go back to it.

 

[JohnPapa]

RainLover,

Many thanks for your reply.

My db is split. I do not have a separate fe for each installation. Both the fe and be are stored centrally in one folder and are accessible by all users. I have about 100 installations and this configuration it has worked well over the past 14 years and has saved a lot of time when I distribute updates.

I deleted the record, compacted and repaired the be and it seems that the problem has reappeared. It does not stop the user from working in general, it just limits functionality (operations that can be performed) using the specific record.

Apart from Compact and Repair, are you aware of any other way to un-corrupt the db?
John

 

[RainLover]

RainLover,

Many thanks for your reply.

My db is split. I do not have a separate fe for each installation. Both the fe and be are stored centrally in one folder and are accessible by all users. I have about 100 installations and this configuration it has worked well over the past 14 years and has saved a lot of time when I distribute updates.

I deleted the record, compacted and repaired the be and it seems that the problem has reappeared. It does not stop the user from working in general, it just limits functionality (operations that can be performed) using the specific record.

Apart from Compact and Repair, are you aware of any other way to un-corrupt the db?
John

John

If you do some searches it will agree that each user should have their own FE. However it is difficult to argue with you if your system has worked fine for 14 Years.

The automatic update of new versions to each user can easily be done. Again a search will find many examples of this.

None of this however solves you immediate problem. Are you sure you deleted ALL the corrupt files. Have you looked at every table or just the one you suspect as being a problem. Try importing that table into a new DB. If you have more corrupt files then this action will most likely cause an error.

Have you been back to the people who wrote this in the first place. Maybe they could fix it for you. Just an idea.

Perhaps someone else has an idea.

 

[JohnPapa]

RainLover,

Many thanks for your reply. As I mentioned the software works, only with some hiccups with the specific record.

I am the original author of the software so I can try anything. No third party code was used in the development, that is why the software has been problem free.

Regarding the single mde approach, it has worked very well and generally it is faster than the use of different mde's. I will look into your suggestion anyway.

If anybody else has an idea or can share a past experience, it would be appreciated.
John

 

[RainLover]

I saw the link in your first post, hence I thought that this was 3rd Party software.

Now I know better I would suggest that you decomplie the MDB convert and reinstall.

What version are you running? I assume you wrote it in 97 so what is it now.

 

[JohnPapa]

I never used decompile. Thanks for letting me know.

The software started with A97 but has been running under A03 for 8-9 years or so.

I found the following:
To decompile your database, follow these steps

1. From the Windows, Start, Run command line, type: msaccess.exe /decompile
   where msaccess.exe includes the complete path. For example, for Access 2010:
   C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE /decompile
2. From Access open the database you want to decompile (with trusted authority for Access 2003 or later)
3. Open up any module. Compile it via Debug, Compile.., then File, Save.
4. Go back to the database and Compact it. The location of the Compact command varies by Access version.

Looking at 3. , does decompile work only on modules? Do you know whether I can do a decompile of all modules in one go.

Regards,
John

 

[RainLover]

I have attached a DB that does an automatic Decomple.

I have not used it in ages so let me know if there is a problem.

It decompile the whole DB in one go.

Make sure you go back and compile any Module after decompiling.

 

[RainLover]

What happens to a DB over a perod of long term development, especially as long a period as you have, is that you have copied and deleted many Forms and Reports. Not always do you lose the associate code that went with them. Sometimes they come back to bite you. So decompile is a good way to clean this garbage our of your DB. You may see a reduction in the size of the data base too.

Not everyone has heard of using decompile. But it really should be done before every release.

Quick Question. The table you are having trouble with, does it have a Memo Field. If so then some advise to place it in a separtate table in a One to One relationship. Not that this will solve your problem but they say that Memo fields are prone to corruption. So if this happens you only lose the Memo not the entire contents of the record.

PS What is the link of yours about in post #1. Is that you on the right?

 

[JohnPapa]

Hi RainLover,

The specific table has 2 memos. It is the table which contains the patient data.

I will look a bit further into decompile.

If you follow the link in #1,under Team, I am the one on the right.

Regards,
John

 

[RainLover]

John

I just need to get something straight in my mind.

You talk 100s but then 2.

So have you installed this to 100 different companies and then within those companies you have one back end shared by 2 or so users.

What we are looking for is clashes or crashes. It would be more likely to happen with many users rather than just 2.

Also I assume that with your qualifications you have looked at high voltage spikes etc. I onced asked a similar question that you have to a very smart Electrical Engineer whos speciality was generation in power houses. The first thing he went to was a high voltage crane we had. Then wiring then about another 20 other things. Got too much for me but I would think you would know about this stuff. Might be worth checking.

 

[JohnPapa]

Hi Rain,

There are 100 installations and the problem happened only in one installation, which is a network of two pcs.

I don't think there was ever a crash of the system. The user was unable to manipulate the corrupted record and I had to delete it (with difficulty) from the table.

Being an EE I asked the dentist whether he has power problems. He answered "no", but the reply was not based on any proper investigation. I will try to convince him to conduct a proper test. Maybe that will solve the problem, which btw appeared twice in 12 months.

Regards,
John

 

[boblarson]

Again, I will reiterate so you know that it is important. ALL users should have their OWN copy of the frontend. Using a single point of entry is not good for multiuser use. Yes, it can be done. Is it safe or wise? No, it is not.

Next, to get rid of the offending records you will need to run a compact and repair and then you should be able to go to the table and they will be the records without a primary key and may show ######## in one, or more, fields. Those records would need to be re-entered if the data is important.

Third, it is wise to store memos in their own table and not in tables with lots of other fields. You can link them together using the PK from the main table and the FK in the memo table.

 

[RainLover]

John

Bob has taken the hard line. And he is correct.

I or anybody else cannot tell you why you are getting corrupt files. All we can do is follow what we have learnt the hard way.

Number One. Every user must have their own copy of the FE.

As far as new versions are concerned, that problem can be overcome quite easily. There are samples on the site for FE updater. Updating the Back End is also possible but a lot more involved.

I am working on a DB at the moment which is 1,000 miles away. I can update that any time I want. The users do nothing different and would not know it happened until they saw a change in a Form or Report etc. This update takes less than ONE second.

I know that the many years of success is hard to overcome. Maybe you could try it on just the one site where you are having problems. You need first to make sure you have deleted all the corrupt records. They can be added back later from the back ups the client has. And you should be able to locate them by the missing numbers in the Primary Key. Assuming you used AutoNumber.

Please keep us informed.

 

[RainLover]

John

I have been doing some more thinking. I have not had a corruption problem since the one where I sought the advise of a Engineer.

If I remember correctly we did have some high voltage all around us. It was an electrical workshop where we would overhall Transformers and we even overhalled a, can't think of the correct name here so I will use generator, from a locl power station.

We had no surge protection at all on the server. So we installed one which was quite expensive. I think that solved our problem.

Just thought I should throw this into the pot.

If you want a FE updater go to my Sky Drive as there is one there that you could use. Ideal for A 2003 but should work on any version. The link is in my Signature at the bottom of this post.