Bad network performance over 10Mb line with database

[kers]

Hello,

New here. Have been searching this forum for a couple of hours now ( i am not kddin).... Havnt been able to find the solution to my problem.

I have a access database. Split front and backend. Both located on a server.
Users access it trough a shortcut on there desktop

The original database was created in 97 .... upgraded and modified in version 2K and now upgraded, modified and running in 2003.

The database is running on a server standing in my country (norway). Users have no problem using it. Multiple user login about 5 ... no probs.
If i copy the database to another server of our company (located in Danmark) the same story, everything works fine.

BUT if i try to access the database standing on the Danish server from Norway (over a 10Mbit line) it is VERY slow. 2 minutes to open the a form.

I have tried making a MDE file. Get an error message that there is a problem with 'they table' and that it would create duplicate values in the index, primairy key or relationships.
But it does create a DB1.mde file anyway. It does not work, but the file is there.
Even though i did tell Access to call the new file differently.

I dont know SQL and to be honest i dont think it would make it better since on one server it runs fine, it is just the 10Mb line that seems to be the problem.
The IT guys told me that the real dataload on that line for my database was about 1Mb max, so it should not be a speed issue.



Any one have any experience with this?
Or any suggestions what to do with the creation of the MDE. Is there a way to find out What exactly is wrong with my database instead of this non specific error message.??

thnx.

Erik.

[MMcCarthy]

Hi Erik

First open the Database and try to compile it from the VBA editor. See if there are any conflicts or incompatibilities left over from converting it up to 2003.

Let me know what you find.

Mary

[kers]

I have tried compiling the database.
I works fine... i get no messages. Untill i try to close the database.
it asks me if i want to save changes to the form i used to get in the VBeditor.
if i say yes, i get the message that there is a problem with 'they table' and that it would create duplicate values in the index, primairy key or relationships
If i hold the escape button i get a load of those untill finally i am out of the program. After that i cannot start it again beacause of a VB corruption.
My front end doesnt work anymore..... (ofcours i made a backup :-)

Any suggestions ?, apart from starting over again maybe ?

thnx.

[MMcCarthy]

What recordset is the form bound to?
What is the code behind the form?
When you open this form does a record automatically get created?

[kers]

Did some testing.
Have a form called test in my front end. It has no coding, just some boxes.
When i tried to get in VB using this form and compiled the database, when it was done and i tried closing, it asked me if i wanted to save 2 forms..... test and another.
Deleted the other completely. Using test form to get in VB, compiled. Trying to save database. Same problem. It generates 3 error messages telling me there is a problem and then it gives me one message. One record deleted. Then another 3 error messages, and then one Record deleted. This goes on for a while, and when i am out, my front end is corrupeted and cannot be used anymore.

[MMcCarthy]

Did some testing.
Have a form called test in my front end. It has no coding, just some boxes.
When i tried to get in VB using this form and compiled the database, when it was done and i tried closing, it asked me if i wanted to save 2 forms..... test and another.
Deleted the other completely. Using test form to get in VB, compiled. Trying to save database. Same problem. It generates 3 error messages telling me there is a problem and then it gives me one message. One record deleted. Then another 3 error messages, and then one Record deleted. This goes on for a while, and when i am out, my front end is corrupeted and cannot be used anymore.

Ok to test for corruption.

Open a brand new database and import each object into it. See if any of the objects won't import correctly.

The hidden form may have had a purpose. What form is set to open on startup and what is the code behind that form.

Mary

[kers]

I open my database using autoexe macro. The main form is just a ordinairy form with a bunch of buttons to open other forms. Not a real access generated switchboard, this one i made myself.
But there is a public function that is being called, that tries and find the backend automaticly and reconnects it to the frontend. This code is not mine, i got it from a guy who whas really into this stuff (and as you might have figured i am not )
So i dont think that could be the problem, but i will do some testing with and without the public and try to import stuff into a new database, i will let you know how it goes.

thnx

erik

[kers]

exported everything to a new database.... did compress repair... make mde.
than it gave me this help info on a error.

This error is usually associated with compiling a large database into an MDE file.* Due to the method used to compile the database, a considerable number of TableID references are created for each table.* The Microsoft Jet database engine version 4.0 can only create a maximum of 2048 open TableIDs at one time.* etc etc.........

I am guessing my database with its 30 tables, 130 or so queries and 32 forms, combined with some tabs within the forms and around 40 reports, it just is to much for access to handle.

I have been trying to scale down, by rewriting the thing, but it is not easy. My limited programming skills prevent me from using less queries and lets me even use more forms.

Any suggestions are welcome.

erik

[MMcCarthy]

exported everything to a new database.... did compress repair... make mde.
than it gave me this help info on a error.

This error is usually associated with compiling a large database into an MDE file.* Due to the method used to compile the database, a considerable number of TableID references are created for each table.* The Microsoft Jet database engine version 4.0 can only create a maximum of 2048 open TableIDs at one time.* etc etc.........

I am guessing my database with its 30 tables, 130 or so queries and 32 forms, combined with some tabs within the forms and around 40 reports, it just is to much for access to handle.

I have been trying to scale down, by rewriting the thing, but it is not easy. My limited programming skills prevent me from using less queries and lets me even use more forms.

Any suggestions are welcome.

erik

What is the actual file size of your database when compiled?
What version of Access are you using?
Have any of your tables got more than one primary key?

[Killer42]

What is the actual file size of your database when compiled?
What version of Access are you using?
Have any of your tables got more than one primary key?

:confused:
A table can have more than one primary key???

[MMcCarthy]

:confused:
A table can have more than one primary key???

Bad phrasing, sorry.

A table can have a primary key made up of a composite key involving 2 or more fields.

[kers]

To answer the questions.

The front end is about 10MB and the back end about 5.5Mb
As far as i know i have no double Keys in my tables.
The version i am using right now is 2007. But as i have written before, the original database was build in 97 and then changed and upgraded to 2000, changed and upgrade to 2003 and changed and upgraded to 2007.
I can imagine that all these upgrades and changes in between could do some damage, although the database seems to be running fine as long as you just use it.

Still havnt been able to find out what the problem is with my network. The IT guys now tell me that it might be...... that the 10Mb line between Norway and Denmark is set to be half duplex 10Mb and therefor never reaches more than 400Kb p/s.... The initializing of the database pumps up about 17Mb of data we have seen, so it explains the incredible time it takes to start up. So crossing my fingers hoping this is the problem.

Still does not explain of course why my database does these strange things whenever i try to change some of the forms, or try to make a MDE out of it.

Suggestions are still welcome.

Erik.

[MMcCarthy]

Hi Erik

Open the databases VBA Editor window and compile the code. See if any errors show up.

Mary