Using saved query for different tables

[Barry]

I want to use the same query for many tables. I'm new to Access. I see where
you can save the query and name it, but is there a way to then use it with
different tables? I can't see where that can be done. This would be most
helpful in Update Queries.

 

[KenSheridan via AccessMonster.com]

Barry:

You could build the UPDATE statements in code in a dialogue form's module,
getting the variable table names from unbound controls on the form, and then
execute the statement. You could even modify the SQL property of a saved
query in code and then execute it.

The fact that you are contemplating this does suggest, however, that the
tables are of similar structure. This in turn suggests that separate tables
might be being used to differentiate between differences in what is
essentially a single entity type, which would be inappropriate as it amounts
to encoding data as table names. A fundamental principle of the database
relational model (the Information Principle) is that data is stored as values
at column positions in rows in tables and in no other way.

I think it would help us guide you if you could provide some background as to
what you are trying to do in terms of the real world entities involved. I
suspect there may well be a far simpler solution to your problem given a
little recasting of the model.

Ken Sheridan
Stafford, England

 

[Bob Barrows]

I want to use the same query for many tables. I'm new to Access. I
see where you can save the query and name it, but is there a way to
then use it with different tables? I can't see where that can be
done. This would be most helpful in Update Queries.

No it cannot be done. Dynamic queries need to be created dynamically,
typically via VBA.

I've been creating database-driven applications for a couple decades now and
I fail to see how this would be useful or desirable. What you need to
realize is that the graphical representation of a query that you see in
Design view is not the query. What it is, is a tool to generate what is
actually the query: the sql statement that is sent to the database engine to
be executed. You can see the sql statement that will be generated by
switching your query to SQL View.

Now a sql statement has several requirements, one of which is a static data
source (typically the name of a table or another saved query) in the FROM
clause that the query engine can inspect so it can parse the query for
correctness, i.e., correct syntax used, and all object names (fields,
typically) actually applying to objects that are within the scope of the
query.

Frankly, I cannot see how having a dynamic data source would be helpful in
Update Queries, given that you have to actually name the fields to be
updated. If you have several tables that all contain fields with the same
name, then that is typically a case of poor database design, unless what is
known as a subclassing design pattern is being used. More often, it's a poor
decision by a developer to "vertically partition" his data, for example,
putting sales for 2008 in one table, 2009 in another, and so on,
necessitating the creation of new tables for each sales year. This design
might be acceptable for a spreadsheet, but it is not so great for a
relational database. A better design is to think "narrow and long", i.e., a
single table with a field to designate the year to which the data in a row
applies.

 

[Barry]

Ken,

Here's a very basic example of why I want to save queries to be used in
other tables:

I receive weekly databases of name, address, city, state, zip code. They
come to me in Excel. I convert them to Access.

The addresses have two fields, address1 and address2. First, I create a
newaddress field. Then, I create an update query that combines the two fields
into one: address & " " & addr2. This is just one very simple example, but I
have to believe that if that query is saved, it can be used for any table, so
long as the structure contains address1, address2, and newaddress.

I am sure this is Access 101, but that gives you some idea of my experience
with Access. These files are always used to prepare mailings. I never need to
use Access for its relational capabilities.

 

[KenSheridan via AccessMonster.com]

The only way you could use the same query would be if you import your Excel
data into the same table each time so the query uses that table name and its
columns. But as you really just using Access as a transit camp between the
worksheets and the mailing lists, if the data you receive each week replaces
the previous data you could just create permanent links to the Excel files
and use queries which returns such things as the concatenated address as the
basis for mailing lists.

If the data is cumulative you could instead append the data in to an existing
table in which you've defined one or more columns as the primary key to
prevent duplication of existing rows.

If the data is both cumulative and also can have updated values in non-key
columns then you can do it in stages. I had a situation a while ago where
weekly price list data was being imported in this way. What we did was set
it up to:

1. Delete all rows from a 'master table' which was just a straight Access
equivalent of the Excel worksheet.

2. Import the Excel data into the 'master table'.

3. Execute an 'update' query in which the master table is joined to the
'live' price list table on the product, and updates the prices for existing
products.

4. Execute an 'append query' which adds new products from the 'master table'
to the 'live' price list table.

5. Execute a 'delete' query which deletes rows from the 'live' price list
table where there are no matching products in the 'master table', i.e.
discontinued products.

The layout of the Excel file was the same each time, so all this was done at
a single button click which first opened a dialogue to browse to the Excel
file, then executed the queries in sequence. I've simplified the description
a little as there were several related and correctly normalized tables which
made up the 'live' price list in fact, but it doesn't sound like that would
apply in your case. The button's code was:

Private Sub cmdImport_Click()


On Error GoTo Err_Handler

' call procedure to browse to Excel file
' and import data
ImportProductList "ProductsMaster"

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

End Sub

which passes the name of the 'master table' to the following procedure:

Public Sub ImportProductList(strTable As String)

On Error GoTo Err_Handler

Dim OpenDlg As New BrowseForFileClass
Dim strPath As String
Dim cmd As ADODB.Command
Dim strSQL As String

OpenDlg.DialogTitle = "Select Excel File to Import"
OpenDlg.DefaultType = "*.xls"
strPath = OpenDlg.GetFileSpec
Set OpenDlg = Nothing

' exit procedure if user cancelled
If strPath = "" Then Exit Sub

'empty table, ignoring error if doesn't exist
strSQL = "DELETE * FROM " & strTable

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
cmd.CommandText = strSQL
On Error Resume Next
cmd.Execute
On Error GoTo Err_Handler

' import Excel data into ProductsMaster table
DoCmd.TransferSpreadsheet _
TransferType:=acImport, _
TableName:=strTable, _
Filename:=strPath, _
HasFieldNames:=True

' call procedure to append, delete or update
' product data
UpdateProductData

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

End Sub

The UpdateProductData procedure which this calls then executes the relevant
queries.

To browse to the Excel file Bill Wilson's class module is used. Its freely
available from:

http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=22415&webtag=ws-msdevapps


The key point about the above is that the only variable in terms of object
names is that of the Excel file, so the user has to browse to and select this.
Otherwise all table names and query definitions are completely static.

Ken Sheridan
Stafford, England

Ken,

Here's a very basic example of why I want to save queries to be used in
other tables:

I receive weekly databases of name, address, city, state, zip code. They
come to me in Excel. I convert them to Access.

The addresses have two fields, address1 and address2. First, I create a
newaddress field. Then, I create an update query that combines the two fields
into one: address & " " & addr2. This is just one very simple example, but I
have to believe that if that query is saved, it can be used for any table, so
long as the structure contains address1, address2, and newaddress.

I am sure this is Access 101, but that gives you some idea of my experience
with Access. These files are always used to prepare mailings. I never need to
use Access for its relational capabilities.

Barry:

[quoted text clipped - 23 lines]

 

[Barry]

Ken,
Thank you for all of your input. It appears that it's just easier to write
the new query as needed.

--
Barry

The only way you could use the same query would be if you import your Excel
data into the same table each time so the query uses that table name and its
columns. But as you really just using Access as a transit camp between the
worksheets and the mailing lists, if the data you receive each week replaces
the previous data you could just create permanent links to the Excel files
and use queries which returns such things as the concatenated address as the
basis for mailing lists.

If the data is cumulative you could instead append the data in to an existing
table in which you've defined one or more columns as the primary key to
prevent duplication of existing rows.

If the data is both cumulative and also can have updated values in non-key
columns then you can do it in stages. I had a situation a while ago where
weekly price list data was being imported in this way. What we did was set
it up to:

1. Delete all rows from a 'master table' which was just a straight Access
equivalent of the Excel worksheet.

2. Import the Excel data into the 'master table'.

3. Execute an 'update' query in which the master table is joined to the
'live' price list table on the product, and updates the prices for existing
products.

4. Execute an 'append query' which adds new products from the 'master table'
to the 'live' price list table.

5. Execute a 'delete' query which deletes rows from the 'live' price list
table where there are no matching products in the 'master table', i.e.
discontinued products.

The layout of the Excel file was the same each time, so all this was done at
a single button click which first opened a dialogue to browse to the Excel
file, then executed the queries in sequence. I've simplified the description
a little as there were several related and correctly normalized tables which
made up the 'live' price list in fact, but it doesn't sound like that would
apply in your case. The button's code was:

Private Sub cmdImport_Click()


On Error GoTo Err_Handler

' call procedure to browse to Excel file
' and import data
ImportProductList "ProductsMaster"

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

End Sub

which passes the name of the 'master table' to the following procedure:

Public Sub ImportProductList(strTable As String)

On Error GoTo Err_Handler

Dim OpenDlg As New BrowseForFileClass
Dim strPath As String
Dim cmd As ADODB.Command
Dim strSQL As String

OpenDlg.DialogTitle = "Select Excel File to Import"
OpenDlg.DefaultType = "*.xls"
strPath = OpenDlg.GetFileSpec
Set OpenDlg = Nothing

' exit procedure if user cancelled
If strPath = "" Then Exit Sub

'empty table, ignoring error if doesn't exist
strSQL = "DELETE * FROM " & strTable

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
cmd.CommandText = strSQL
On Error Resume Next
cmd.Execute
On Error GoTo Err_Handler

' import Excel data into ProductsMaster table
DoCmd.TransferSpreadsheet _
TransferType:=acImport, _
TableName:=strTable, _
Filename:=strPath, _
HasFieldNames:=True

' call procedure to append, delete or update
' product data
UpdateProductData

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

End Sub

The UpdateProductData procedure which this calls then executes the relevant
queries.

To browse to the Excel file Bill Wilson's class module is used. Its freely
available from:

http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=22415&webtag=ws-msdevapps


The key point about the above is that the only variable in terms of object
names is that of the Excel file, so the user has to browse to and select this.
Otherwise all table names and query definitions are completely static.

Ken Sheridan
Stafford, England

Ken,

Here's a very basic example of why I want to save queries to be used in
other tables:

I receive weekly databases of name, address, city, state, zip code. They
come to me in Excel. I convert them to Access.

The addresses have two fields, address1 and address2. First, I create a
newaddress field. Then, I create an update query that combines the two fields
into one: address & " " & addr2. This is just one very simple example, but I
have to believe that if that query is saved, it can be used for any table, so
long as the structure contains address1, address2, and newaddress.

I am sure this is Access 101, but that gives you some idea of my experience
with Access. These files are always used to prepare mailings. I never need to
use Access for its relational capabilities.

Barry:

[quoted text clipped - 23 lines]

different tables? I can't see where that can be done. This would be most
helpful in Update Queries.

--



.