Deploying / installing a new database

[Pat Hartman]

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.

SQL strings embeded in the RecordSource property of Forms and Reports are actually stored as querydefs. They start with a ~ and are hidden unless you check the show system objects button. So essentially they are no different from normal querydefs.

I understand it is the preferred method for "experienced" developers

No. Storing the queries directly in the Forms and reports makes it easier for some things like copying forms/reports from database to database but impossible for others such as reuse. I prefer to have standard queries that I use in many places. It reduces the overall number of objects in my databases (remember the RecordSources are saved as objects) and saves me work because I don't have to keep reinventing the wheel. Querydefs also can be changed by NameAutoCorrect, not that I have it always on, but I do occassionally want to rename table columns and I want the queries to be changed automatically.

Several of you are still chatting about compacting, temp tables, and SQL strings. Please read my post #39 where I identify the two largest causes of bloat.

If you are really worried about someone modifying your queries, the only viable solution is to ditch Access and go with a compiled language such as VB.Net or C#.Net. An Access database is a container and as a file type, it is much easier to hack than an .exe.

Once you are ready to build the .accde, hide all the objects and set the navigation pane to not show any groups. Make a backup!!!! Set the Allow Bypass property to prevent opening with the shift key (you should code a back door in the app to reverse this), create the .accde, rename it to .accdr. The database is not secure by any means but you've made it very difficult for non-technical people to even see anything, let alone modify it.

 

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

 

[Pat Hartman]

Most of them can be saved as querydefs. You then use DAO or ADO or Access to run them.

 

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

 

[gemma-the-husky]

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.

i see what you mean, Pat.

I was coming more from the pov, that with cascading deletes permitted, a user could very easily inadvertently delete data that was actually required within the system.

 

[Jeanette Cunningham]

The front end won't compact the backend automatically.

There is a sample database that shows how to write code to compact the backend.
http://www.rogersaccesslibrary.com/forum/topic378.html

Note: you can't compact the backend while any of the front ends are connected to the backend. All users will need to be logged out of the backend so you can compact it.

 

[robsworld78]

I'll save them as querydefs on day if I think its needed, need to see what happens to file size as data is entered, so far so good and its been used a few days.

I was just worried about bloating the file as that appears to affect it. I know the code I have isn't the fastest or most efficient by any means but its not slow, as time goes on I'll be re-writing a lot of it as I know more now. Because I didn't know how to do things as I was building I came up with radical solutions to a lot of things which now I know a better way and will replace, main thing is its working as it should so I feel I've accomplished something.

Thanks for the link with that code, it won't work right now the way I have it setup, when the frontend is open it connected to the backend, can't have it open without a connection. But one day in the near future I will change things so I can incorporate it.

Thanks for all the valuable info from everyone and willingness to help!

 

[Severin]

if you create a shortcut to your file then change the path in properties to path plus a space plus /compact then open with your shortcut when the db is not in use. You can also use a command like /repair and you can combine them!~)

 

[robsworld78]

if you create a shortcut to your file then change the path in properties to path plus a space plus /compact then open with your shortcut when the db is not in use. You can also use a command like /repair and you can combine them!~)

So that shortcut would point to the backend file?

Then when frontend is closed run the shortcut and it did its thing?

 

[Severin]

yes!~)

...Tip: If you have a corrupted database and you can't get in... then use /compile it will often do the trick..

 

[robsworld78]

This saves me a lot of work. Thanks!

 

[Severin]

great!~)

...it's a good trick.. you can also send commands into access this way...

 

[morfusaf]

Nice, thats a good deal!

 

[evanscamman]

Single biggest thing you can do to improve form loading speed:

Change your database to read-only or MDE. Takes the load time from up to several minutes down to seconds.

 

[Pat Hartman]

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.

PNGBill was not telling you to convert them to code. His preference is to define them directly in individual reports and forms. Mine is to save them as querydefs. I reuse querydefs in many places. If you save the queries directly in the RecordSource, Access saves them as querydefs anyway. You just don't see them and you can't reuse them. Unhide the MSysObjects table. You'll see the RecordSource and RowSource queries named after the form/report they are used in preceeded by a tilda (~). So whether queries are defined specifically as querydefs or embedded in the RecordSource property, they still end up as querydefs.

I still have a lot of trouble with "'s when I'm creating strings. One thing I did which simplifies things greatly was to create a constant that I named QUOTE. I use it whenever I need a string within a string. For example

strSQL = "Select * From MyTable Where LastName = " & QUOTE & Me.LastName & QUOTE & " AND DeptID = " Me.DeptID

If you decide to create the constant, it needs to be in a stand-alone module. It can't be in a form or report class module or it will only be available when that form/report is open.

Public Const QUOTE = """"