Deploying / installing a new database

[robsworld78]

Hi, I've been working the last 10 months on a database and now its time to put it to the ultimate test, real life. I also want to thank those that helped me with some issues along the way! The database is quite large, has about 700 objects and about 35 of those are tables.

What I did was split the tables in multiple backends, there are a total of 6 backend files, each backend file is linked to the frontend file. I did this because I thought it might run more efficient as a single file would get quite large over time. During building its been running fast but now I just installed it on the network its going to be used on and its runs quite slow. Some forms have a lot of data on them and they can take about 1 minute to open, seems like 10 though. What worries me is it doesn't have lots of test data so it should be quick like whne I'm developing.

The frontend file has been compiled and I'm running the executable file, accde, which works the same on my computer.

So now I'm wondering if I should have the backends merged to 1 file or is it better split the way I have it and possibly issues with the network. The network was setup last year by someone who is so-so at networks, had troubles doing it but did get it.

Does anyone have any tips or articles that explain running it on a network better.

Thanks

 

[Pat Hartman]

Merge the databases. It will take a long time to get to 2G. Aside from making Access work harder to join tables, you can't enforce RI across databases. Once you put them all back into a single BE make sure you define relationships and always enforce RI.

 

[robsworld78]

Hi, thanks I have merged the files into 1 however I'm not sure what you mean by "enforce RI"?

 

[Severin]

Somewhere in the access options for the current database depending on your version of Access there will be something like track name autocorrect... Uncheck it.. that might help!~)

 

[robsworld78]

Somewhere in the access options for the current database depending on your version of Access there will be something like track name autocorrect... Uncheck it.. that might help!~)

Thanks, I did that when I started building, read it somewhere along the line.

 

[Severin]

...after that then you can set all table Subdatasheets to [None] and then remember that with a network you get best performance if you have a copy of the frontend locally!~)

 

[robsworld78]

...after that then you can set all table Subdatasheets to [None] and then remember that with a network you get best performance if you have a copy of the frontend locally!~)

I can't find the subdatasheets settings, where would I look?

The frontend file does run off the local machine, only backends on server.

 

[Pat Hartman]

I attached a picture of the relationships window and the relationship between two tables. Enforce RI tells the database engine (Jet/ACE) that I don't want a record added to tblAmortization that is not related to a valid mortgage in tblMortgage. I also have the MortgageID column set to required in tblAmortization. So, it must be present and it must be valid (have a parent in tblMortgage) or Access will refuse to add a record. I also set the Cascade Delete option so that if a mortgage is deleted from tblMortgage, the database engine will automatically delete the "child" rows from tblAmortization. Don't just automatically set Cascade Delete. You need to think about what you want to happen. In my application, mortgages are related to properties and they come and go. If a mortgage is deleted, then there is no reason to keep the amortization schedule. There are other situations such as in an Order Entry application which has a relationship between Customers and Orders. Generally, you don't want to set Cascade Delete on this relationship because you don't actually want to delete Customers that have Orders. So, by not checking Cascade Delete, you are telling the database engine that you don't want to delete a Customer if he has any dependent orders in the Order table. However, a customer without orders is not affected by the Cascade Delete rule because it has no "children".
I did not set Cascade Update because all my tables use autonumbers as primary keys and since you can't modify the value of an autonumber, there would never be a change to cascade. The ONLY updates that are ever cascaded are changes in a primary key value to all related "child" records. So, if my CustomerID was a number that came from another system or was generated by some set of rules in my app whenever a new customer was added, it might be possible under some circumstances to have to change a customer number. That would be a monumental endevor if it weren't for Cascade Update.

 

[robsworld78]

Thanks for explaining that to me, based on your comments I think I'm ok. Before I started I did try to get every right with relationships but because I know nothing about access I could only do what I could.

All but a couple of my relationships have the enforce RI checked, at this point I'm scared to check the 2 relationships that don't because things might not work properly then, at this point as far as I can see everything works as it should. I was able to figure out the cascade delete and when I remove an order it takes out the details from one table and the payments from another, I don't get stuck with any orphan records when I delete something and I can't delete "contacts" of any kind if they have something in the system which I like.

The cascade update I always checked it when I checked cascade deleted as I wasn't sure what was really happening but felt they went hand in hand. I do use autonumbers in all tables so I'm guessing it won't be an issue, it doesn't seem to be.

Here's a pic of my spiderweb

 

[Pat Hartman]

Bite the bullet now and enforce RI on the two remaining relationships. It is important for you to know that you don't already have bad data in your tables. If you choose the Enforce RI and Access tells you it can't, you'll need to get rid of the orphan records and then try again. The find unmatched query wizard will find the orphans for you if you don't know how to build a left join by yourself.

 

[PNGBill]

Regarding your Network Speed, there are many issues that can effect your database on a network.
Building it on a single pc hides a lot of speed problems.
If you can identify a task that appears slow, review the code used.

Your sql may be slow and could be redone faster.
Your vba code can easily run 10 times faster, if only some simple changes were made.

If you feel it is not a Network Issue but Database Issue then review your tasks and post here for advice on how to speed up that process.

Also, look at tables. I saw a database recently that had 255chrs for a 3 chr field.
Again, when building nearly whatever you do seems fast but when deploying, reality takes over

 

[robsworld78]

Thanks Pat, I enforced the remaining relationships there were actually 4 in there and everything seems ok. That's just making things proper, right? Could that have any affect on network speed?

Thanks PNGBill for the tips, when I was building this I wanted to setup a small network at home to build on but that never worked out.

I know a lot of my code could be written better and I was going to do that next winter along with the other changes you mentioned, I have to admit I know I have lots of 255 char for fields that will never need it. I guess when building I underestimated that stuff, I probably go through it tonight and change fields as noted.

I've been scared for this day, I realize when people use it it'll be used different, I think I covered all my bases, I'll know in a couple weeks.

I have a gut feeling it was really slow on the network because of all the backend files, on some forms when they opened it needed data from 4 out of the 6 files. I ran a simple version last fall for testing and the network was better than it is now, although I don't think it was ever fast.

Also when the frontend is open I need a connection to the backend file so its always connected, it only disconnects from the backend when I close it, I read that's a good thing for speed because a connection is already made.

I'll be re-installing tomorrow, I'll let you know if its improved any.

 

[gemma-the-husky]

for what it's worth, i would remove cascading update/deletes from all the relationships. its much safer to write code to control these things rather than let the dbs engine do it. you can't get the stuff back.

2 other reasons.

1. you should not need cascading updates in a properly designed database.
2. data is expensive to get it in there. as far as deletions go, it is better just to set a "completed" flag than delete hard-won data.


do you have a lot of data in there. if not, you should not be getting speeed problems. if you are, and this is for a company, then it might be worth spending a bit of money getting a professional review at this point.

one other point is that if your tables are not perfectly normalised, you tend to run into difficult-to-fix problems down the line. the early you resolve these the better. it's a bit late now, anyway, as you have written the code, but it would still be worth getting everything reviewed.

 

[robsworld78]

More good comments, thanks!

I have a feeling my cascade updates are doing nothing and one day soon I will remove them, as for deletions there are none happening through-out the db unless its a simple record that's meaningless, all the records that have a cascade delete won't be getting deleted anyways but its nice to have setup so when I'm testing if I delete an order in a table it takes the details and payments out of the respectable tables.

I think there's a lot of data but no there probably isn't much. I expect most tables to stay under the 5,000 records except for the order details table as each order could have 20 records so it could get to 100,000. I think I did a pretty good job with normalizing my tables, data is never stored twice, ID's are meaningless and most important everything seems to work. I've gotten quite a bit of help on forums with some difficult things that would only work if the tables were normalized, a few times I was told if my tables were done properly this piece of code would work and it did work so I'm pretty confidient however I know very little about access so there will be problems.

The db is for a company which operates half a year and every year the db will start fresh so it will never have years of data in it. I thought it would be nice to get a real review on it, kinda like a grading if you will but that cost money and things are pretty confusing in the db with naming things, didn't do the best there.

 

[robsworld78]

Oh one other thing I have a question about, compact and repair?

I know its important to run compact and repair regularly and I have right from the get go. Every couple hours while working on it I would close it down so it could compact.

In the frontend db options I have the "compact on close" checked off and the program gets closed daily so that should be good but I have questions about the backend file.

I figure that file should never be opened on the server so will the frontend compact it when it closes? Am I missing something here?

 

[Severin]

I will give you a quick review!~)

send to spg.eksperten@gmail.com

 

[PNGBill]

Every time you create a new Front End, it is compacted. for developing database, then you have a lot of compact and repairs Free of Charge

 

[Lightwave]

Also - You might want to review and find out about run time front ends.

It's not clear but I suspect your front ends are not runtime versions.

It is very easy to create a run time front end. You just go to tools/database utilities/make mde (in A03) A07 and A10 they are called accde

Ensure you have a copy development front end as once you've made a front end mde (or run time version) you can never edit it again.

Run time front ends are smaller as much of the development facilities are disabled.

It is my experience that runtimes slightly speed up database operating times over networks.
(it all helps)

 

[Pat Hartman]

for what it's worth, i would remove cascading update/deletes from all the relationships. its much safer to write code to control these things rather than let the dbs engine do it. you can't get the stuff back.

2 other reasons.

1. you should not need cascading updates in a properly designed database.
2. data is expensive to get it in there. as far as deletions go, it is better just to set a "completed" flag than delete hard-won data.

Sorry gemma but I absolutely disagree. Application programmers are not more capable than the RDBMS engine of deleting the correct records. I understand that there are cases where you would not ever want to delete records but would flag them instead but once you decide to delete them, it is far better for the db engine to do this than for you to program it. Not to mention the fact that many databases are shared by different applications. You would then need code in all the fe applications to either prevent deletes or cascade their effects - truly bad business.

 

[robsworld78]

Every time you create a new Front End, it is compacted. for developing database, then you have a lot of compact and repairs Free of Charge

Not really sure what that means but I'm guessing its all good.

Lightwave I am using the runtime for the frontend, first I make executable then I use the package solution to make the installer, however one feature I added only works if the full version is installed, but it will work in the runtime format which I thought is strange. That's the "linkedtables" window. I added an autoexec that launches code, if an error happens on db open (as in can't find backend) then it opens the table linker so they can be relinked. I love how it works, next year just need to make it work without full version. When I did this I didn't expected it to run with runtime version, only did it so I can lock it up at home and still change the backends location. It runs as a standalone program. I realize there are addons for table linking but I just didn't have time, this was quick and efficient.

I agree with gemma that coding is probably better for a few reasons but only if you are really good. If I was good enough to do things like that I would be using SQL database and visual studio. When I started this project I wanted to use that but way over my head, I can't right queries or relationships, especially the queries I have, they get complicated so I think. I don't think you want to be explaining all that to newbies.

Severin thanks for offering to review the db, I have nothing ready I'll see what happens, didn't install today will be tomorrow now.