Consideration when creating views in an existing Microsoft Access database

We have a database in MS Access.  This DB has over 100 tables and lots of views.  This DBs' apps is about to go thru some required changes by users.

For what we need to do, the DB hasn't direct table relationship within it's structure (by this we mean a lot of child tables has no parent id being referenced) to produce the result required. 

We decided  to create views.

Our main concerne is the DB current structure and what impact will addtional views implies?  We don't want the application to be affected by the modification so the report is created in a new apps and will be linked by clic from main apps.  The o ly modification that's going to be done are new views.

Please advice
rayluvsAsked:
Who is Participating?
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
<Define what you mean by "views">
Yes, in Access there is nothing known as a "View", so I am confused by your terminology here.

You may be referring to "Queries", though...?

If this is the case, then I  have heard that a Report will run faster if the source is a saved query (or table for that matter), rather than an SQL statement.

The downside here is that using too many saved queries will complicate the management of the db.
1. With no direct ties to the report, a query can be deleted by accident, thus the report may crash.
2. You may end up with many very similar queries, and outright duplicates.
3. Some queries may contain (or rely on) on other queries (further complicating the above two scenarios...)

Hope I am understanding your question though...


JeffCoachman
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
A view is just a subset of data that may include one or more tables (assuming we're talking about SQL Server views, that is). It cannot impact other tables or views, since it's just pulling data from the source tables.

So if all you're doing is ADDING new views, then you really cannot affect existing views.

Obviously you could impact existing data if you perform data actions against the view (update, delete, etc), but I don't think that's what you're asking.



0
 
rayluvsAuthor Commented:
We are working with Microsoft Access, not MS SQL.

Please advice
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
hnasrCommented:
Try to recreate the issue using 3 tables and attach the database.
Include few records in each table and show the required output.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Define what you mean by "views" ... there's really no need to upload a database for a question like this.
0
 
rayluvsAuthor Commented:
You guys may be right.  I don't think us called "views" in Access (we were thinking of SQL).

I'm not at the computer, but I think it's queries.

Based in this, we so have lots of saved queries.  We just want to have an opinion of what shoul we consider when creating more queries when the access table is already filled with lots of queries.


Our main concern is if we create new queries any existing tables or queris be affected negatively I'm any way?
0
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
<We just want to have an opinion of what shoul we consider when creating more queries when the access table is already filled with lots of queries.>
Again, it more queries will just make the DB harder to manage, but there is nothing "Harmful" here...

<if we create new queries any existing tables or queries be affected negatively I'm any way?>
No, not by "creating" a query.
The kicker here is that a query in Access by default is "editable", ...meaning a user (if not restricted in some way), can modify the data.

And as I stated, ...if you start creating new queries out of existing queries, you may get tangled in knots.  In these situations deleting one query will cause all of the dependent queries to fail.

Finally note that if a query is created in a certain way, it may be "Non-Updateable", so if you will be using these queries as the source for Forms, then you need to be very careful.

This is all based on my understanding of your question here.
You may have to provide more info on *exactly* what this database and the queries will be doing.

That all being said, let's see what LSM posts...


Jeff


0
 
rayluvsAuthor Commented:
Great Info!

Ok, we need to develop a report, not for a Form that will consist of 5 tables.  By form we mean a inquiry screen or data entry.

    - In order to print the report successfully, we'll start all queries from fresh, not from
      existing queries.
    - The access to the the DB is only via apps, not on the DB, so users can't access
      directly on the DB.
    - Finally, the report is not complex, so we think the term "Non-Updateable" should
      not be a concern.

Going about this way, would be ok to proceed?

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
Yes, you would be fine with this process. As Jeff said, building new queries (even if you base those queries on existing queries) will have no effect on existing queries.

"Finally, the report is not complex, so we think the term "Non-Updateable" should       not be a concern."

Many queries for a Reports are not updateable, but then you don't generally perform data manipulation in a report - so that's not really a concern.

0
 
rayluvsAuthor Commented:
Thank you very much.

Just to close the question, is it safe to say that MS Access has no type of "view" tables as in MS SQL?
0
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
Correct, MS Access does not have an object called a "View" as exists in MS SQL.

Without going into the details, the closest thing to a View in MS Access is a Query.
0
 
hnasrConnect With a Mentor Commented:
"a lot of child tables has no parent id being referenced"
One can create a query (view) to link M:M relation to two 1:M relations. This is why a sample database and clarification of the process and required output were asked for.
0
 
rayluvsAuthor Commented:
Thanx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.