Convert multi tables in one - not a query. (1 Viewer)

[mahenkj2]

I am using collect data via email process to collect data by email, purpose is to update not to add new records but to update. The condition for update is to have data from one table only.

I have 4 tables data from which should be sent by email. These tables are related. I made a query based on tables and query is update-able. When I use this query, wizard does not give option of updating the data but only of adding new records.

I tried to first make a make table from a make table query but that too have the same result.

can somebody suggest me the ways to make treat these tables in a single table? Almost all fields except one shall be just to read and one field shall be updated.

best regards.

 

[llkhoutx]

When a query indicates that a table, and I assume that you mean a table not a (query of a ) query, is non-updatable, try adding the table's primary(s) keys to the query.

 

[mahenkj2]

I could not get exactly your meaning but I would like to elaborate my dilemma further.

The condition by Microsoft is that "Collect data by Email" wizard shall only add new records if it has to populate two or more tables and updating the record facility is only available if a single table is to populate.

When I use a single table, I am able to add or update the table by wizard. Even if I make a query from a single table, I am still able.

But,

When I make a query from more than one table, then I can only add new records and wizard does not give provision of updating the records.

I was thinking that if by any way I can convert my four tables into one temporary table and update that table first and then transfer the data in main table. In fact out of four table, I will send many fields just for showing and only one or two field of a single table, I will use for update.

I tried to use a Make-Table query also but I do not know why it also does not work. Is there any difference in a normal table and a table created by Make-table method.

I did Macro as:

Openquery
SelectObject
RunCommand - CollectDataviaEmail

I hope I am clear enough.

PS: I ensured that query used by me is updatable otherwise.

 

[llkhoutx]

Think about what a query is for; to display data. Queries are generally not updatable because Access doesn't necessarily know which table to update. Add all primary tables to the query and give try an update then.

"Boogle" non updatable queries.

A table is a table is a table. It doesn't matter how it is created.

 

[mahenkj2]

Thanks for your comments.

If I use a single table, works fine, if I use a query based on the single table works fine. As soon as I add one more table and create a select query, the wizard does not offer updating record capability.

If I make a make table query from a single table and then I use newly created table for data collection, it does not offer data update capability. The difference I found is make table does not hold primary key, that's the problem.

I have reproduced below what Microsoft says:

http://office.microsoft.com/en-us/access-help/collect-data-by-using-e-mail-messages-HA010015427.aspx

In most cases, you will be able to decide whether you want to store the collected data as new records, or to use the data to update existing records. In the following situations, you will only be able to store the data as new records:
You are collecting data to populate two or more tables.
The underlying table does not have a primary key field. The primary key values are necessary to map each reply to an existing record. If your destination table does not have a primary key field, either add it now or assign an existing field that has unique values as a primary key.
The underlying table does not have any records. If the table is empty, the wizard assumes that you want to add records.
The e-mail addresses of the recipients are not stored as a field in the database. When collecting data to update records, you will not be able to manually type the addresses in the wizard-generated message. The address field must be in the destination table, or in a table that has a relationship with the destination table. For example, if you are collecting data to update the Orders table, the addresses must be stored as a field either in the Orders table or in a related table (such as the Customers table).

 

[mahenkj2]

Just now I learnt a way to add a primary key by SQL from Microsoft site and now I am able to use new make table for collecting data via email.

Thanks llkhoutx for helping me out. Still I am not done the whole thing but a step further is being moved.