Problem with linked table

[erick-flores]

If you have access to the database that the linked table is in, modify
it there. You can't modify a linked table. Alternatively, you can
import the linked table, then it won't be linked any more and you can
modify it. There are potential problems with this strategy, as you will

be working on a copy of the original table, so any new data that goes
into the original table from elsewhere will not be reflected in your
database.

Hello all

I know I can't make modification to a linked table. But I do want to
add a couple of field to each record so I can type aditional
information. I know I can import the table and then be able to do
whatever I want but then the data will not be real time update. Can I
run a macro to update the import table everytime I open the database?
or maybe another way around to have a couple of field add to the linked
table?

Thanks in advance

['69 Camaro]

Re: Problem with linked table

Hi, Erick.

I know I can't make modification to a linked table. But I do want to
add a couple of field to each record so I can type aditional
information.

Presumably you can't alter the table you're linking to because someone is
holding a gun to your head. What's stopping you from creating a second
table in your front end with a one-to-one relationship with the linked
table, adding the necessary columns to this second table, and then creating
a query with the two tables joined on the primary key of both tables?

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.


"erick-flores" <erickjflores@g mail.comwrote in message
news:1169137345 .915402.212150@ 51g2000cwl.goog legroups.com...

If you have access to the database that the linked table is in, modify
it there. You can't modify a linked table. Alternatively, you can
import the linked table, then it won't be linked any more and you can
modify it. There are potential problems with this strategy, as you will
>
be working on a copy of the original table, so any new data that goes
into the original table from elsewhere will not be reflected in your
database.
>
Hello all
>
I know I can't make modification to a linked table. But I do want to
add a couple of field to each record so I can type aditional
information. I know I can import the table and then be able to do
whatever I want but then the data will not be real time update. Can I
run a macro to update the import table everytime I open the database?
or maybe another way around to have a couple of field add to the linked
table?
>
Thanks in advance
>

[erick-flores]

Re: Problem with linked table

Presumably you can't alter the table you're linking to because someone is

holding a gun to your head. What's stopping you from creating a second
table in your front end with a one-to-one relationship with the linked
table, adding the necessary columns to this second table, and then creating
a query with the two tables joined on the primary key of both tables?

Thanks for your answer. I tried to do this but I guess I wasn't doing
it right. Can you give me some more details of how to do this? I did
create a table called "Comments", my linked table is "v_aging_re c". I
only need a couple of fields in the Comments table. I am almost sure
that the pk in the v_aging_rec is inv_num. I dont know if that
information helps you or not but thats what I have.

So, what field should I add to my Comments table as the pk, I am really
confused...if you can guide me here I will really appreciated

Thank you

[erick-flores]

Re: Problem with linked table


Ok I think the problem is the link table does not has a pk. And of
course I cant set the pk because I cant make any changes to the link
table. That is why I can not create a one-to-one relationship and to be
able to enter the comments.

Any ideas or work arounds?

['69 Camaro]

Re: Problem with linked table

Hi, Erick.

I am almost sure
that the pk in the v_aging_rec is inv_num.

You have to be sure of both the name and the data type, because you'll be
joining on this column in your query.

So, what field should I add to my Comments table as the pk

To make it easiest, it should be the same name and data type as the column
in the linked table. Ensure that it is assigned as the primary key in your
Comments table, so that there will be a one-to-one correspondence with the
records in the linked table, even though you won't be able to enforce
referential integrity between tables located in different files.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.


"erick-flores" <erickjflores@g mail.comwrote in message
news:1169223284 .057946.14080@5 1g2000cwl.googl egroups.com...

>Presumably you can't alter the table you're linking to because someone is
>holding a gun to your head. What's stopping you from creating a second
>table in your front end with a one-to-one relationship with the linked
>table, adding the necessary columns to this second table, and then
>creating
>a query with the two tables joined on the primary key of both tables?

>
Thanks for your answer. I tried to do this but I guess I wasn't doing
it right. Can you give me some more details of how to do this? I did
create a table called "Comments", my linked table is "v_aging_re c". I
only need a couple of fields in the Comments table. I am almost sure
that the pk in the v_aging_rec is inv_num. I dont know if that
information helps you or not but thats what I have.
>
So, what field should I add to my Comments table as the pk, I am really
confused...if you can guide me here I will really appreciated
>
Thank you
>

['69 Camaro]

Re: Problem with linked table

Hi, Erick.

Any ideas or work arounds?

A guy has a car towed into an auto shop, and he tells the mechanic, "I want
you to make this car go faster." The mechanic lifts the hood to see what he
has to start with, but there's no engine.

"Where's the engine?" asks the mechanic.

"I don't have one, but I have some boat engine parts in a box in my garage
at home," says the customer.

"Do you want to purchase an engine and have me install it for you?" asks the
mechanic.

"An engine is irrelevant. I just need you to make this car go faster."

The analogy is that a box of engine parts is like a bunch of data dumped
into a table with no primary key. Until someone comes along with the skill
to organize the engine parts and build a working engine out of those parts
or to normalize the table with a primary key (which identifies individual
records and establishes relationships between records in other tables), it's
just a box of junk. The primary key is _that_ vital to having a table of
data: it's equivalent to the car's engine.

No engine, no faster car. No primary key, no table, no relationships.

Get a primary key on that table, and then you'll be able to work with the
data.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.


"erick-flores" <erickjflores@g mail.comwrote in message
news:1169241443 .829788.128180@ a75g2000cwd.goo glegroups.com.. .

>
Ok I think the problem is the link table does not has a pk. And of
course I cant set the pk because I cant make any changes to the link
table. That is why I can not create a one-to-one relationship and to be
able to enter the comments.
>
Any ideas or work arounds?
>

[erick-flores]

Re: Problem with linked table


OK, I get the point. Since I can not modify the table (in the back end)
I need to find a way of creating a copy of the table and then add a
primary key to that copy table. I was thinking of using an Update Query
to update my copy table everytime I open the application. This is what
I have done so far

1. Create a duplicate copy of my table.
2. Add a field to my duplicate table, the primary key
3. Create another table, "Comments" and create the one-to-one
relationship.

Everything is working fine BUT I want to be able to update my duplicate
table everytime I open the application. I was trying to use the Update
Query but I guess I wasnt doing right because the fields on my
duplicate table were not getting updated. So my question is, what is
the best solution to update my duplicate table everytime I open the
application so all my fields are up-to-date?

Thanks in advance

[erick-flores]

Re: Problem with linked table


When I run the Update Query to update the data on my duplicate table, I
got a message saying: "Operation must use an updatable query". Is this
because my source table is read-only (Link table)?

Any ideas how can I update my table?

I'm going crazy with this link table issues...

[erick-flores]

Re: Problem with linked table

Got it working...never mind. Thanks for all ur help.

['69 Camaro]

Re: Problem with linked table

You're welcome, Erick. But until you assemble that engine, your car won't
go very fast. Don't ignore the missing engine (primary key on the linked
table).

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.


"erick-flores" <erickjflores@g mail.comwrote in message
news:1169493541 .359007.62150@s 34g2000cwa.goog legroups.com...

Got it working...never mind. Thanks for all ur help.
>