• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 319
  • Last Modified:

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
0
rayluvs
Asked:
rayluvs
  • 5
  • 3
  • 3
  • +1
5 Solutions
 
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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
Jeffrey CoachmanCommented:
<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
 
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 CoachmanCommented:
<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 )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 CoachmanCommented:
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
 
hnasrCommented:
"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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 5
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now