Real Estate template Project (1 Viewer)

[Dreamweaver]

What I've decide to to with this project is take you though as many of the development steps as I can.

I'm using my site as I can do what I like on there so don't whine about it I'm doing it to help members "You know who you are"

I will update this post when I add more info this will be on a daily basis I will try and be as detailed as posible giving you the reasons why I did x or y and even my mistakes

For this first post I have added 3 posts this starts for the very beginning of the project ERD, Business rules,Project Creation, I wont add a list of tables or queries as the first come from the ERD and the second I haven't got a clue I will create Queries for all objects even lookup lists I don't use SQL Statements except in code.

I'm far from perfect but when commenting on a mistake first think of the project scope this is "To recreate the MS Template of same name making if comply to my rules or good practice", when looking at the ERD i've added an image of the MS template you'll get an idear what I think of the MS templates.

Login

Login

databasedreams.createaforum.com

 

[AccessBlaster]

MickJav,

Are these two fields in some sort of relationship, or am I looking at this wrong. The amount of relationships in this project are mind boggling to me. Good luck with your project, it will be fun to watch the progression.

 

[Dreamweaver]

No It's just the way Draw.IO shows it
EmployeeID Relates to tblEmployees
TenancyID has been updated to LeaseID and relates to tblLeases
I'll see if I can colour code the links

thanks @AccessBlaster

 

[Dreamweaver]

Comleted the Concept, ERD and Tables Task

Login

Login

databasedreams.createaforum.com

 

[Dreamweaver]

Day 2
thought of a few issues with my ERD while in spar of all places.
done a bit more yesterday plus works today

Login

Login

databasedreams.createaforum.com

Also updated the objects list

Login

Login

databasedreams.createaforum.com

 

[Pat Hartman]

I don't use SQL Statements except in code.

Funny, I stopped using embedded SQL 25 years ago when I stopped using COBOL. In the early days of Access, it was much more important to use querydefs than it is today but there is still efficiency to be gained by using querydefs.

The first time a querydef runs, Access calculates an execution plan and saves it with the querydef. Therefore, every other time the query runs, the saved execution plan is used. Access doesn't need to calculate a new one. Calculating an execution plan takes time (not very much) and the more frequently you run a query, the more important that time savings is. Older versions of Access used to bloat when you used embedded SQL because it didn't clean up after itself so not only did you pay the price of time, your database grew larger every day.

When you run an embedded query, Access perforce calculates an execution plan each time you run the query but it has no place to save the execution plan so it has to recreate the plan each time you run the query. Now that the bloat issue is gone, using embedded SQL isn't as horrific as it once was but you still have more maintenance to perform since you probably are not reusing queries. I reuse queries whenever I can so reports and forms for the same data are almost always bound to the same querydef. That way, if I need to change the query, it gets changed for both uses. If you embed the SQL, you need to remember to find all the places you need to modify and then apply the change.

If you use good naming standards, you can effectively use a single querydef in multiple places. This is the same concept as called procedures and normalized tables. Create once, use in many places if possible.

 

[Dreamweaver]

It was because of a post you made @Pat Hartman a while back that made me change my ways lol

I was just wonder which would be better in a forms record source If no criteria used

A table tblContractors a query?

 

[Dreamweaver]

I've updated the topic with what has been done today have also linked back here to a topic I created to help solve one of my issues.

You'll notice I found a couple of errors in my ERD that I had to fix, I haven't updated the ERD but should anybody want me to just let me know.

Login

Login

databasedreams.createaforum.com

 

[Dreamweaver]

Got a lot done but if you noticed one of my post I have one of them days where you want to bash yourself on the head lol

Login

Login

databasedreams.createaforum.com

 

[Pat Hartman]

It was because of a post you made @Pat Hartman a while back that made me change my ways lol

It must have been an impostor since I use querydefs 99% of the time. There are occasions when I use embedded SQL but it is mostly when I create a search form with a lot of options. In that case, I usually build the dynamic SQL on the fly but static SQL is ALWAYS a querydef. Dynamic SQL isn't possible with a querydef although static SQL can take parameters.

I was just wonder which would be better in a forms record source If no criteria used

The vast majority of my forms have selection criteria which means they would be using querydefs but occasionally, I create a form for a small table and just bind it to the table rather than making a querydef. It really doesn't matter to Access whether you bind tables or queries to forms/reports. What matters (when you are using ODBC) is severely limiting the rows and columns returned from the server. When the BE is Jet/ACE, there is no server involved. Everything happens on your computer so Access does its own optimization.

The biggest disappointment people who convert an old style Access app from Jet/ACE to SQL Server face is that their app got slower rather than faster!!! How did that happen? They didn't change their forms to be bound to queries with selection criteria to limit the rows retrieved from the server. Using good client/server practices, you get the server to do the heavy lifting and only send you limited recordsets.

 

[Dreamweaver]

I don't really get involved with SQL Server Although I have it installed and will get around to it lol.
I did hear once it would be better to use UBound forms where pos with servers limiting the data to one record I.E. the Requested Record or a new record with a dbOpenDynaset, dbAppendOnly

what I ment with the criteria post was I used to use "Select field1, field2, Etc FROM Table and any WHERE Statments needed" in the record source but not I just use a Query .

mick

 

[Dreamweaver]

Another day done and got further along

Login

Login

databasedreams.createaforum.com

 

[Dreamweaver]

Decided to make a few more changes to the ERD hopefully for the better, moving along with the project nicely

Login

Login

databasedreams.createaforum.com

 

[Pat Hartman]

I did hear once it would be better to use UBound forms where pos with servers limiting the data to one record I.E. the Requested Record or a new record with a dbOpenDynaset, dbAppendOnly

You were misinformed. Access makes every attempt to "pass through" every query. So if your query requests a specific record, the criteria goes to the server and only a single record is returned and that is why binding forms to queries with criteria is so important.

The nice thing is that proper client/server design works just fine even if your BE is Jet/ACE. However old style Access design works quite poorly in a client/server environment. This has given Access a bad name over the years. During my 25+ years with Access, I have used a lot of updateable views to make queries with complex joins more efficient but I have used very few stored procedures or actual pass-through queries. Mostly I use them to create very complex reports. As to unbound forms, I might have created 2 (always search forms) over 25 + years and close to a hundred applications. So, you might need to create an unbound form but never go into a design with this as your plan.

My very first Access project with 2.0 in the early 90's used a DB2 (IBM's RDBMS) BE. That is what hooked me on Access. Once I found out that I could create apps that used data on the mainframe and even update it, I was hooked. Why write all that code for COBOL/CICS when I could point and click to build forms and write only validation code in form events. All the framework was done for me.

Access is a RAD (Rapid Application Development). If you are not taking advantage of the RAD features especially bound forms, you should not be using Access. Access has a very heavy footprint and serious distribution issues plus it comes with the inherent limitations of a task specific tool. Access excels at data-centric applications that can be built by a single developer. You can't use it as a web app with anonymous users. You can't use it to create cool games with lots of graphics. However, it is infinitely scaleable when you are using a RDBMS as the BE and the number of users is limited only to the number of RDBMS seat licenses you have. That means as long as the FE is distributed so each user has his own personal copy of the FE, you can have thousands of concurrent users with no issues.

Where Access fails is if your project is so large that it requires multiple developers. The one-file format of Access makes this almost impossible and even if you have a source control tool, it is very cumbersome.

 

[Dreamweaver]

Thanks @Pat Hartman

 

[Dreamweaver]

I'm linking it all together now, Still have the payments and the schedule to do but it's coming along nicely.
Made a few more changes One of these days I'll get a ERD done that I don't need to edit LOL

Login

Login

databasedreams.createaforum.com

 

[Dreamweaver]

Getting to the end of the forms design section now but still got a seach & print screen on the list, Will be adding the schedule creator tomorrow for the monthly lease amount.
I've decided to only run it on the main control when admin logs in otherwise if the systems on a network I would run for every employee who loged in, even though it will only check a date in preferences for when it was last run but I don't see it as a good use of resourses.

Login

Login

databasedreams.createaforum.com

 

[Dreamweaver]

Done more work today and started data testing which has shown a few errors that I needed to sort.

Login

Login

databasedreams.createaforum.com

 

[Dreamweaver]

Just about done with the forms now, todays been more about cleaning up and testing with a few updates where needed.

Login

Login

databasedreams.createaforum.com

Also added the code for the schedule s if somebody see's an error they might let me know.

 

[Dreamweaver]

well pleased, Just created a help system linked to my site with a shortcut menu.
I have to create a topic for each Section I want to provide help, but users could add to the topic even asking questions.
Will mean more work for me on my site but now users of my templates/example will be able to get focused help.

Login

Login

databasedreams.createaforum.com

Login

Login

databasedreams.createaforum.com

Now I've got to help the mrs clean the house that must be my punishment