Deploying / installing a new database (2 Viewers)

[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

 

[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.

 

[robsworld78]

So much to learn!

Carrot and Stick approach. Keep your job or loose it

Around the place I'm at people can get away with anything, never a consequence. I have the messed up job of building a system so people can't get away with stuff. I say what you say, don't need to build a system for that, straighten up or loose your job but instead they prefer to pay me to try and create something so that can't happen. The place I work at is a little different.

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.

The type of business it is there's no value in the old data and its not wanted. As above this company is different.

1. SQL strings in code

Do you mean things like

DoCmd.RunSQL "UPDATE Vehicles SET InUse=" & 0 & " WHERE UnitNumber=" & Forms!createorders!OrdersOrdersSubform.Form!UnitNumber

If so I'll have to monitor it more closely, I have lots of INSERT and UPDATE. Have no temp tables and not much gets deleted so I pass there.

I'm not to worried about the queries being visible because its hard (impossible for me) to get to when its locked down.

I've done what you suggest Pat for building the .accde, I went one step further and hid the toolbar.

Do you know of any code that works with the runtime version that will run the compact and repair feature? Something I can add to a button.

Thanks for all the great info people.

 

[robsworld78]

Oh, almost forgot, on the original subject about network speed issues, turns out it was network so that was good news.

 

[PNGBill]

Yes, that is what I mean't by sql in code.

Good news re network speed.

 

[robsworld78]

Yes, that is what I mean't by sql in code.

Good news re network speed.

Well, you win some you lose some.

Would update and insert queries be better? I thought they were the same.

If not what technique is best to replace those SQL strings?

I really do have lots of them in the code. In one day it could get to 100 or more of those things getting triggered.

 

[PNGBill]

If not what technique is best to replace those SQL strings?
I really do have lots of them in the code. In one day it could get to 100 or more of those things getting triggered.

Not sure what your problem is about 100 sql's activated in one day ? many applications could have 1,000's in a day.

If you mean some code is slow ?
Review some long/slow code.
Some things I notice slow code: - (Just refering to one Function/Sub)

If you have a variable you need through your code, get it once only eg, say CurrentUser(). Get it the first time you need it and save it to a Dim variable and refer to that through your code rather then keep using CurrentUser() each time you need it, in that Function/Sub.

If your code refines the Recordset as it goes, then just collect the req'd data each time you need it - save to Dim variable as above.
eg, if you want to find customers driving yellow cars and with two wifes then first use an sql to return only the customers with yellow cars.
The use an sql to check that recordset for ones with two wifes.
I am not explaining this very well but test it on sqls in your navigation bar first.
One sql to return all your filtered records And supply all the variables you need may be quite slow where as two sql may be much faster.
The first quickly filters the records and the 2nd collects the variable data for those records.
If you find two is faster then one, then have two in your code. Otherwise, use one sql.
I have had code where I collect the ins and outs of a ducks a... for all records and then proceed to do something with a small group of those. Bit silly and slow. The reverse is better, I feel. Select the correct records first and then get your required data for those records.

"Select Case" can be faster then a complicated "If Then" and easier to follow.

User Input can slow the task eg, we have just rethought some tasks that required a user to click to action a Reminder Email to Late Payers. This caused an operator to go Click for 1 hour.
Now the code identifies prospects, opens a continuous form and the operator then unchecks any where he/she feels an email isn't appropriate now. Clicks one button and all unchecked records have an email sent.
While the code is working a form lets the operator know wha is happening and the message/font colour changes accordingly.
Actual pc process time may be slower but the overal time to do the task is much faster and the operator can get on with other duties (polish fingernails) while the pc is working.

When you have code you feel is slower then it should be, post sme on this forum and advice is never far away on how to speed it up, if possible. You then learn a new skill.