Using data from Linked Tables

[Mary]

I have a linked table. Is there a way to link that table with a table that
can be updated?

In other words: i want to use all the data in tblLinked (cannot be updated)
in tblNewInformation (can be updated).

 

[Jeff Boyce]

Mary

I'm having trouble visualizing how you are trying to do something... (that
I'm also having trouble visualizing!)...

You have a table that is not updatable, but you want to update it via
another table?

WHY? Please describe what you want to accomplish ... there may be a way to
do it that doesn't use the approach you described.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

I have a linked table. Is there a way to link that table with a table that
can be updated?

In other words: i want to use all the data in tblLinked (cannot be updated)
in tblNewInformation (can be updated).

What do you mean by "use"?

You can *copy* the data into a (redundant) second local table, where it can
then be edited. Of course these edits will not be reflected in the original
linked table.

If you want to be able to update the linked table, then you need permissions
to update it. Or are you assuming (incorrectly) that linked tables are ipso
facto not editable?

John W. Vinson [MVP]

 

[Mary]

Hi Jeff,

Thanks for your reply. Maybe I am making a wrong assumption. Our company
software is Vantage. I have exported a query from Vantage and then linked it
to Access. I cannot update the existing data in that linked table. And, as
far as I know, I cannot add fields to that table either. So, I thought I
would make a second table with the data I need to keep track of. I somehow
need to link the two tables. If i establish a relationship, it won't let me
update the second table.

For example, the first table has the customer order and number of service
days sold. In the second table I need to keep track of the dates we
provided service. I want new orders to automatically appear on the second
table.

I hope that helps explain further. I look forward to your reply.

Mary

 

[Mary]

Hi John,

Yes, I am assuming that linked tables are not editable. How do you make them
editable? Can you add new fields to a linked table?

As I said in my previous post, our company software is Vantage, and I have
exported a query from there and linked it in Access. The linked table has
the order and number of service days sold. Beyond that I need to keep track
of the dates of serivce provided. What would be the best way to do this?

Thank again. I look forward to hearing your reply.

Mary

 

[Jeff Boyce]

Mary

I'm not familiar with that software, so I'm not following what you mean by
"exported a query from Vantage and then linked it to Access".

Does this mean?:
* your data is in a single table in Vantage
* you created a query in Vantage
* you created a datafile (but not in Vantage ... where is it?) containing
the data your query returns
* you linked to that datafile

Is the table in Vantage updatable (i.e., add/edit/delete data)? Is the
query in Vantage updatable? Is the datafile updatable?

When you say "make a second table with the data I need to keep track of", do
you mean in Vantage? In Access? What "data ... keep track of"? Can you
provide an example? How does this data relate to the data in your query
from Vantage? Do the two have any common elements/fields?

Based on your description (table1 - customer order/# of service days; table
2 - dates service provided), I don't see any common field (e.g., CustomerID,
OrderID, ...). I suppose you could count the rows in table2 and see if that
number matched the total # of service days in table1 ...

I still don't have a very clear picture of what you have, what you've done,
or what you are trying to accomplish. Could you state what you will be able
to do (i.e., prepare a report showing X, Y, Z) if everything works out?
Focus less on the HOW (table, query, link, ...) and more on the WHAT. I'm
not asking out of curiosity, but because the folks here in the newsgroups
may be able to offer alternate approaches if we knew where you were going.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Mary]

Hi Jeff,

Thanks for your input. It doesn't really matter about Vantage because
linked tables in Access are all the same, not updatable, right? And if you
can't update the table, you can't update the query based on the table, right?
Nor can you update a table with a relationship to the linked table, right?
If the answer to all those questions is yes, there is the dilema.

Table 1 - Linked Table Fields:
Order Num
cust Id
name
address
city
state
zip
Num Days Service Sold

So what I want to do is take the number of service days sold and keep track
of the dates of service:

Table 2 - New
Order Num
Start date
end date
Installed by
Number of days supplied
Days supplied versus days sold
total days sold per year
total days supplied per year

If everything works out, new orders will show up automatically in table 2
and I will be able to update table 2.

Let's say the Order Num field is in table 2, still don't know how to relate
these tables since once a relationship is established, it won't allow updates
in table 2.

hope that paints a clearer picture, thanks again for your input.

Mary

 

[Jeff Boyce]

Mary

If I'm understanding correctly, you are referring to whether the STRUCTURE
of a linked table can be modified from the "front-end" (the .mdb file which
holds the link). No. The data in a linked table can be updated, but not
the structure.

Thanks for the clarification on table structure. You might want to take a
look at the sample database that comes with Access, the Northwind database.
Right off the top of my head, it strikes me as terribly redundant to put the
customer's name address city state zip in again and again, once for each
order. A more normalized design would use a Customer table to store name
address ... just once, and a CustomerID to store which customer placed the
order in the Order table.

Moreover, your table2 appears to hold calculated values (e.g., days
supplied, total days, ...). It is quite rare to need to store calculated
values if you have the raw data from which the calculations can be done.
One major reason for NOT storing calculated values is that you then have to
come up with the synchronization routines that will have to be run any time
any of the underlying (or calculated) values change!

Sorry to be dense, but I'm still not understanding the WHAT. It seems like
you are still trying to define what you want in terms of HOW you will do
something, but I don't understand what that is.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Mary]

"The data in a linked table can be updated, but not
the structure" How?

 

[John W. Vinson]

Thanks for your input. It doesn't really matter about Vantage because
linked tables in Access are all the same, not updatable, right?

Wrong. It's perfectly normal to update linked tables.

And if you
can't update the table, you can't update the query based on the table, right?

That's right.

Nor can you update a table with a relationship to the linked table, right?
Wrong.

If the answer to all those questions is yes, there is the dilema.

It's a dilemma for *your particular linked table* - it's not a problem with
linked tables in general!

John W. Vinson [MVP]

 

[John W. Vinson]

Hi John,

Yes, I am assuming that linked tables are not editable. How do you make them
editable? Can you add new fields to a linked table?

As I said in my previous post, our company software is Vantage, and I have
exported a query from there and linked it in Access. The linked table has
the order and number of service days sold. Beyond that I need to keep track
of the dates of serivce provided. What would be the best way to do this?

I'll follow your discussion with Jeff - he's asking the very questions that
I'd be asking.

John W. Vinson [MVP]

 

[Jeff Boyce]

Mary

I'm not aware of any special settings to make a linked table able to handle
updates and new rows. My linked tables do that.

Back to an earlier list of questions I asked. Can you update the "query
exported from V"? It may be that the dataset you have linked to is NOT an
updateable recordset.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Mary]

Great John,

Thanks....could you point me in the right direction. How does one add data
to a linked table? or how to add data to a table that's got a relationship
with a linked table?

Mary

 

[John W. Vinson]

Great John,

Thanks....could you point me in the right direction. How does one add data
to a linked table? or how to add data to a table that's got a relationship
with a linked table?

IF - and this is apparently not the case in your application - you have a
linked Access database table, or a dBase table, or a SQL/Server table, or any
of the many updatable linked tables, the linked table works, acts, and feels
just exactly like a local table. You base a Form on the table, or on a query
joining the linked table to a local table, and update it just the way you
would update any other table.

Nothing special is needed if it is updateable.

If it is not updateable then you cannot update it.

I don't know how to explain it other than that.

You *STILL* have not explained the data storage format of your linked table.

John W. Vinson [MVP]