Unable to get data from linked tables coming from 6 diffrent databases.

[Exadd]

Ms access 2003 db is linked to other 6 ms 03 databases. The input of data is done on this 6 ms db however on a module where all 6 is linked this is what is there when logging in to the database

Option Compare Database
Option Explicit

Function Reset_Database()
Dim vAsk 
vAsk = MsgBox("Do you wish to reload the updates from the server? Do this if new employees have just been added. This might take a while.", vbCritical + vbYesNo, "Employee Database") 
If vAsk = 6 Then 
DoCmd.SetWarnings False Screen.MousePointer = 11 
DoCmd.OpenQuery "qry_admin" 
DoCmd.OpenQuery "qry_hargyp" 
DoCmd.OpenQuery "qry_navomill" 
DoCmd.OpenQuery "qry_navoplant" 
DoCmd.OpenQuery "qry_senior" ' ADDED JULY 16, 2009 FOR VWS 
DoCmd.OpenQuery "qry_NAVOVWS" 
DoCmd.OpenQuery "qry_tfr_estate" 
DoCmd.OpenQuery "qry_tfr_division" 
DoCmd.OpenQuery "qry_tfr_section" 
DoCmd.OpenQuery "qry_jobtitle" 
DoCmd.OpenQuery "qry_jobtitle2" 
Screen.MousePointer = 0 
DoCmd.SetWarnings True 
End If 
DoCmd.OpenForm "frmemployee"
End Function

I've checked the database and its query stated above for 2 weeks now and can't see anything wrong with it but still I'm unable to get update. This has been working so well for the past 3 years, last year we had a similar problem so I just copied across the module from a back up copy to this one but for now the back up copy of this modules are not working also. Please assist as I'm so so lost for now.

[alansidman]

Have you tried a compact and repair?

[Exadd]

I tried compact and repair but still getting the same response.When clicking the yes button of getting update from the server it just hangs forever.Is there any other solution to fix that?

[alansidman]

My only other suggestion is to create a new database and import all the tables, queries, forms, modules, etc into the new database. Sometimes, databases become corrupt and this is the only means to fix it. Short of that, I am out of answers. Perhaps, someone else will come to the rescue if this does not work.

[Exadd]

I've created a new db and transfered everything to a new one.Run the queries again to append data from the other 6 database to that of this new 1 and when clicked to Yes button for getting update from server, I manage to get 5 db data reflecting Ok on this new 1 however theres this query name "qr_navoplant" result came back as 'Record Deleted'.I've checked through the modules and i can see the 'qr_navoplant' is also mentioned in there.I've recreated qr_navoplant rerun it and it is the same.Am i missing something?

[alansidman]

Do you have the spelling correct. In your code you have the query listed as qry_navoplant yet in your thread #5 you refer to qr_navoplant. Are they the same, is this a typo.

[John_G]

Hi -

One thing I might suggest is that instead of using DoCmd.openquery, use
db.execute queryname, dbfailonerror

This will cause any query that has a problem to generate an error message, which will at least tell you where to look.

HTH

John

[ssanfu]

And remove any line that has to do with "DoCmd.SetWarnings"


Maybe post your mdb? Do a compact and repair, then zip it.......

[Exadd]

Hi, I'm attaching the example db.I've tried and tried but still getting the same message 'record delete' so i'm putting my faith to the experts now.

[ssanfu]

Don't see any attachment...?????

[Exadd]

Hi sorry all once again can someone have a look at the db and tell me what exactly is wrong.Sorry i went on leave and never replied to this.Your assistance will be greatly appreciated.

[John_G]

Hi -

It's difficult to diagnose without access to all those other databases, because the query design views don't work (!). Are you able to determine which query is causing the problems?

The problem may not in fact be with this database at all - it might be one of the other 6 you are linked to that is corrupted. Did you open each of them individually to see if they are OK? Do a Compact and Repair on each (Make a backup first!!!) to see if that helps.

John

[John_G]

Hi -

Another suggestion. As I have pointed out, the way you are running the queries in you code will not generate an error message if one of them encounters a problem - it may just send the whole process off into never-never land (as you have discovered!). Did you try running running each query individually, outside the code? They appear to be ordinary append queries with no dependence on form values.

And that brings up another question - since they are append queries, might there be a problem with the data being appended, that causes a key or index violation? Running the queries outside of the form code will help with that possible problem.

John

[Exadd]

Thanks for your reply what I did back here after looking at John's suggestion about the data I went back copied across tables and query using ms access 03 in one of this linked db to new db and did compact and repair on that new database.

Found the append query that was giving us problem if you look at the example it was append query name "QRY_NavoPlant" so reran the append query and the data is now populated under table TPM_personal ..

Thanks for all your ideas and assistance you may close this thread as the problem was rectified.THANK TO THOSE WHO HAVE SHARED THEIR knowledge on access with me.

[Exadd]

Thank you all this was solved but forget to close it..Thanks for your input