Deploying / installing a new database

[PNGBill]

When you make changes to your development copy of the Front End you have to publish this as a "e" version.
Doing this also compacts the front end you deploy.
This means if you deploy a new front end every week, then it is compacted every week.

The need to compact more then weekly may well mean you have some bloating issues that should be addressed.

 

[robsworld78]

I'm embarrassed, I still don't get it.

I do publish the "e" version which gets changed to the "r" version. Since I'm just getting the db in place I expect to replace the front end at least once a week but in a couple months I'm hoping I don't need to work on it and can take a break.

This is where I'm confused, doesn't that compact on close do anything? Or because this is runtime its functionless? Or are these different compacts? I didn't think a front end had anything to be compacted once its not being developed, I thought only the tables need compacting, which I can't decide if that happens through the front-end or should I be periodically opening and closing the backend file?

I've noticed while developing when I do changes to queries and forms the file size really increases, this is why I was continually closing and reopening, I thought it was better if it had less to compact when it did its thing, The frontend has been 23mb without images for a while but for some reason it just went down to 17mb I also noticed when I just use the frontend as a frontend it doesn't increase in size so I decided that only happens during development on the frontend. But I've always been worried about the backend.

10 years ago I made a simple MPG db which had basically no code and I knew nothing, it wasn't compacted once or split. It worked for a couple years then went corrupt, now I'm guessing that's why. I can't have that happen to this one, which I don't think will....

Where do things go bad, frontend or backend?

 

[PNGBill]

Your Development copy doesn't need compacting (normally).
When you create the deployment version, it is compacted.

Backend, as stated in other posts, can only be compacted when no users are logged in to their front end, including you.

Open the backend and do a compact, after making a backup.

But, what is causing the bloating as our backend is only reduced by 1% or less when we do a compact and this is only done on a "rainy day" ie no bloating.

We did have considerable bloating and avoided this once some bad habits had been replaced with better code and procedures. Especially removing all Temp Table usage.

 

[robsworld78]

Ok that makes sense, thanks! I have a feeling I'll be alright. I think my backend file is ok, I haven't monitored it much but it never seems to be overly large, right now its 1.6mb. I'll be closing monitoring everything, the db hasn't been used yet so I don't know if there will be any issues, at the moment I don't think there are, except for my obvious ones.

 

[PNGBill]

With a 1.6mb backend Compact and Repair would be way down on the list of things to do.

 

[robsworld78]

That's what will take time, me knowing how big the file should be, right now I have no clue, that could be bad as far as I know. I'm going to go through all the fields and change all the 255 character fields to a length that's really needed. That should make it even smaller.

How is about 20mb for a front end file size? Soon to be 700 objects.

 

[PNGBill]

How do you jump from 1.6mb to 20mb in two posts ? or am I missing something here ??

 

[robsworld78]

backend file is 1.6mb but that's empty, with a bit of data and orders its 1.8mb.

frontend file is 17mb at the moment, was around 23 for a long time and for some reason went down recently.

 

[PNGBill]

Allways look for improvements and or faults to resolve but the sizes you have now seem quiet normal.
You would be hard pressed to have a smaller Front End and the Back End will grow when data is added.

A new database would have a bigger front end then back end, but not for long

Backend - Keep field size relevant and avoid temp tables.

Front End - Reuse Forms and Reports, place recordsource sql's in the form and report so they are not in the navigation part.
Learn vba asap.
Review queries that rely on other queries. Often you can resolve the issue with one query only rather the 5 or 6, or more

 

[robsworld78]

Cool, I always felt the size was ok as it slowly grow with more objects. There are lots of areas I need to improve the db frontend, when I started I had difficulty with queries and yeah some are trains and hard to track.

That sucks to see you say place sql's in forms, I asked around on that one to see what gave better performance and in the end I opted to save them all as queries instead. Actually put time in just to go to each form and save the sql. Oh well another day I guess. I know its not that hard putting it back, just go to query copy sql and put back in form. How much difference in "performance" will that make?

I am starting to figure out the vba, now I can write some simple sql updates, etc... Hardest thing about the vba for me is all the ' " & I've been told how they work but more than not then get me going in loops.

I have no temp tables although I could in the future as I found a way to use them to turn non-updateable queries into updateable ones, and I sure could have used that a few times during building, but I will always keep that in mind. The ones I may use will only have a few records, they would never grow.

Next winter I'm going to rebuild some parts mainly the inventory handling as its very complex and I know I can improve it much, that's what I started with and I knew very little, now I know little.

 

[PNGBill]

I don't know that having the SQL in the Form or Report will speed up or slow down the database but it certainly keeps things neat and tidy and saves file size. I understand it is the preferred method for "experienced" developers Ensure you Delete the query once it is saved to the Form Report otherwise, you achieve less.

I am saying to Avoid Temp Tables, not advising to use them.
I was able to avoid using them once I improved by queries. Not on day one by any means.
Where it is handy, I have opted for a permanent table but the code deletes the data that is not needed. This way the table doesn't cause Bloat.

Check Allen Browne's web site. He has a very good Form you can add to your front end. This allows you to Copy and Past your SQL, click a button and you have an SQL formated for use in vba code.
http://allenbrowne.com/tips.html Some other good tips and tricks here as well.

 

[robsworld78]

I will start to transition the queries back however that tool you mention sounds interesting, isn't it best to have the sql as vba for a couple reasons at least? Security (as in no one can get to your work) I'm thinking and I'm sure more.

I am starting to get a grip on queries, since I found that solution for un-updateable queries I've been wanting to use it, set it up for a few things but figured out the queries better at the same time so I didn't need them and not using them now and yeah hopefully I can always figure out the queries, queries are cool what can be done.

I've been to that site in the past and yeah got some good things, missed the form your talking about, I'll look to add it later. Thanks for all the valuable info you've given me!

 

[PNGBill]

I will start to transition the queries back however that tool you mention sounds interesting, isn't it best to have the sql as vba for a couple reasons at least? Security (as in no one can get to your work) I'm thinking and I'm sure more.

Not sure what you mean here. An SQL is a query. But.. if you want it to run inside your vba code, you need to add "" and other bits and pces so vba can use it. VBA does not recognize SQL. It can allow sql to run if it is formated as a string - don't ask why and how.
All you do is make the sql to be a string - as per the form in Allen's web site.

Security ?? no idea of this issue. I would have thought this was covered in overall security of your database.

Once you deploy your Front End, (accde) no one can see your code. Your Data is another matter.

 

[robsworld78]

When I said that I meant putting the SQL back in the form/report and deleting the queries. Having no queries is best for security, I figure.

That was the bad part about putting my sql in a query view, they are more vulnerable. The queries aren't protected when you create an executable, I can't see design view for forms but I can open the query in design and change it still. If they are in the forms then I can't get at them.

But then you could still have queries that are for other things are not in a form so those are left vulnerable but if those queries are vba then nothing can be changed, just the data.

I've disabled the shift key on opening, hidden the panel, lost the toolbar, disabled right click on the last form before compiling and left the main forms non-popup tabs with the tabs hidden so it appears to the viewer access isn't involved.

Now if someone opens the executable with access they can't right click the last form to close it, unless there's a special set of keys I don't know about. If they do they're stuck with a gray screen and no toolbar so I don't think much can be done. But still on a developers point everything in code is more secure least no one can alter any of your db if they can break through the other stuff, just more disappointments. I like to think my db is locked tighter than fort knox.

 

[robsworld78]

Well it will be tighter than fort knox once I get my queries fixed up.

 

[PNGBill]

Sorry but you are mixing words. Check your post...

You really can't avoid having some queries as query objects.

I say have forms and reports with their unique sql (query in sql view) pasted into their property and not as seperate queries. (some very long queries/sql's can not fit but very rare situation)

How do you have queries as VBA ? do you mean not use a query but use vba code only ? I guess this is possible but is it worth the work ??
By Security you mean preventing an operator from ing their front end ?
I guess they will only do this once! and then you give them a new FE and they don't do it again.
If you are concerned they may copy your sql/query and sell it on ebay don't think they will get much for it.

 

[robsworld78]

You really can't avoid having some queries as query objects.

Why not, you said that tool will change the SQL to VBA so the queries remaining can be converted easily to VBA and then no more query objects.

Its good if you are selling a db and don't want any edits done unless your doing them and you know what edits mean. Now without me the db can never change, see I just made myself important.

 

[PNGBill]

I was referring to vba code that Includes sql's in them. Of course, these sqls need be nowhere else but within the vba code, where infact they are just string chrs and not executable sql.

If you have General purpose queries in your database, which i assume everyone has, then how you can hide same in vba code is something I have never thought of.

Suming up.. we are talking of three sql's here. One is the common query/sql that you have in your database and can be seen by all.
another is sql's used as Form and Report record Source - these should be in the Form or Report and nowhere else (except if they are too big)
and the last type is the sql string used in vba code in Modules and events related to Forms and Reports.

If you have a method to prevent users from viewing the Navigation Panel then great.
We haven't bothered with this as users are just not allowed to go there.
Carrot and Stick approach. Keep your job or loose it

 

[Pat Hartman]

I didn't think a front end had anything to be compacted once its not being developed, I thought only the tables need compacting, which I can't decide if that happens through the front-end or should I be periodically opening and closing the backend file?

Development causes substantial bloat because you are constantly changing objects and writing new queries. Once the FE is stabalized and delivered to the client, there are two things which are responsible for the majority of the bloat.
1. SQL strings in code - For those of you who have ever used a compiled language, this equates to Dynamic SQL whereas querydefs are Static SQL. Querydefs are "compiled" when they are saved or the first time they run and the execution plan is saved for future use. SQL strings in code are "compiled" every time they run. That makes them slower although not enough to notice unless you run a lot of them and because they use work space to calculate the execution plan, that space becomes bloat since Access cannot recover it without a compact.
2. Temp tables. Deleted objects leave empty space and that includes temp tables. It also extends to records so using a permanent table but deleting all the rows and replacing them is essentially the same process. Temp tables are rarely needed since queries serve the same function in most cases. If you have an application that relies heavily on temp tables, it is best to create them in a separate linked database that is used only for this purpose. Create the database on the fly, create the temp tables, delete the database when you're done. That way you won't bloat the FE.

Even if your application does not bloat because you have carefully crafted it to avoid the techniques that cause it, it is still important to compact the database at least weekly or monthly. Compacting copies the old objects into a new database and leaves behind all the old compiled code for forms/reports/modules/macros and querydefs. The result is the first time you run something in the compacted database it must be recompiled. This is important for querydefs because databases are dynamic. Row counts change, indexes are created and compiling the querydef forces Access to create the most efficient execution plan based on current information.

 

[gemma-the-husky]

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.

it is probably unusual to clear data and restart with a clean database. historic data is useful, and there should be no speed issues with storing old data.