Maintaine query structure

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

Hi,

Our company has a database that has a query, "query1" that has 3 tables
which are joined. Each month the tables 1, 2, and 3 are replaced and the
query is rerun.

Here is the problem - the query does not maintaine its structure. This is
either a flaw in the access design or most likely a settings that we need to
change, since we are not access experts.

Problem = if table 1, 2, or 3 is deleted and the query is viewed. The joins
are removed from the query and any formulas are preceded by "exp1". This is
ok but the problem is that if
1.) the query is closed WITHOUT SAVING,
2.) then each table is recreated
3.) the query is opened again --> the joins are gone and the "exp1" remains

Is there a way to maintain the structure of the query?

Thanks for your help!
 
Why do you delete the tables and then open the query? You know it won't work!

The QBE will try to resolve joins and if it can't, the query is broken. Just accept that
and life will be simpler for you.

Alternatively, you can save the actual SQL into a constant in VBA code and reset the
query's SQL with this line ...

Currentdb.QueryDefs("Query1") = "SELECT * FROM tblYourTable INNER JOIN etc."
 
On Fri, 30 Jan 2009 06:00:12 -0800, Jeff

A convoluted way of saving the structure is to save the SQL statement.
Design the query when all tables are present, switch to SQL view, and
save the text in a safe place.
Deleting tables is unusual. Are you sure there isn't a better way?

-Tom.
Microsoft Access MVP
 
A coworker recommended that we save the sql into a text file. But this is a
problem that I hear constantly from other people. That if you are working
with queries in access the structure changes if the tables were deleted, even
if you DO NOT SAVE THE QUERY.

Since most people use databases to solve ad hoc problems this can be
frustrating to deal with.

Let me ask you this... is there a way to prevent a table from being deleted.
I dont think there is. There is really no way to protect data in access.
Isnt that the main point of a database? To have a way of efficiently
accessing and protecting data.

In excel you can protect the sheet, and even set a password. There is no
such feature in access to protect a query or table. (There is a security
wizard but that is pretty convoluted).

Microsoft should add a protect feature to tables and queries just like excel.

There really is no logical reason why the query should automatically change
the joins when the query IS NOT SAVED.

Thanks for you replies!
 
Why not use a form and comand button to open the query? This way you can use
code to ensure the tables are there before opening the query.

Also, why do you delete the tables and not just append the new data to them?

I would not save the SQL to a text file. You could save it as a constant in
your VB code and just reload it by clicking a button like Danny suggested.

Access security will allow you to prevent users from deleting tables. I
don't let my users even see the tables, but just allow them to use forms and
reports. You can not get around Access changing the queries when tables are
deleted, the best method is to try and prevent people from opening them when
the tables are not there.
 
- I dont have a lot of experience in Access and have never created a form.
One problem is that the queries are used to do a "fund rollforward" and the
same queries used one quarter will not be able to solve our problem the next
quarter. So each quarter we usually have to edit the queries to solve our
problem. Unfortunately since as our business changes our queries need to be
constantly updated.

The tables need to be deleted because the usually a table is policies from
Quarter 1, and in quarter 2 we need to replace all the policies information
since the information like fund value, death benefit will change. So
appending the data will not work (I think anyway).

How can you change the settings so tables are'nt deleted?
I think I will start saving the sql quer in the macro window.
 
Does your table structure change? If not, you can append the data, just add
a field that you can put in information to identify the quarter and year
(Date field?). You can then use criteria in your query to give you the
correct quarter you are looking for. I would suggest taking some time and
learning about forms and using visual basic code. These could save you a lot
of headaches.
 
Back
Top