473,320 Members | 1,870 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Problem with linked table

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

Jan 18 '07 #1
9 4536
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" <er**********@gmail.comwrote in message
news:11**********************@51g2000cwl.googlegro ups.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

Jan 18 '07 #2
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_rec". 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

Jan 19 '07 #3

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?

Jan 19 '07 #4
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" <er**********@gmail.comwrote in message
news:11*********************@51g2000cwl.googlegrou ps.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_rec". 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

Jan 19 '07 #5
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" <er**********@gmail.comwrote in message
news:11**********************@a75g2000cwd.googlegr oups.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?

Jan 19 '07 #6

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

Jan 22 '07 #7

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...

Jan 22 '07 #8
Got it working...never mind. Thanks for all ur help.

Jan 22 '07 #9
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" <er**********@gmail.comwrote in message
news:11*********************@s34g2000cwa.googlegro ups.com...
Got it working...never mind. Thanks for all ur help.

Jan 25 '07 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

20
by: Neil | last post by:
I have an Access 2000 MDB file with a SQL 7 back end. I have a main table with 50,000 records; and I have a selections table with 50,000 records for each machine that uses the database (about...
2
by: Robert McGregor | last post by:
Hi all, I've got a Front End / Back End database that was working just fine. One day i opened the FE to find that if I tried to open one of the linked tables from the database window, nothing...
3
by: Michael Plant | last post by:
Hello one and all. I have a stored table in my database and the form I'm using is based on a query that draws data from my stored table and a linked table. The linked table is a *.txt file. ...
5
by: b b | last post by:
I created the following code to delete all linked tables in my database (Access 200): -------------------------------------------------------- Dim tbl As TableDef Dim dbs As Database Set dbs...
2
by: DataB | last post by:
Hi everyone! I have a forms problem. Bakground: I have created a number of tables. Of these, I have a main parent table (Personal Details) and a number of other child tables (Tax file No.,...
1
by: kuhni | last post by:
Hi everybody! Writing this time, I'm really desperate. Basically, I have a conceptual problem of how to solve a certain "problem" in MS Access 97. General objective: In order to categorise...
11
by: bofh1234 | last post by:
Hello, I am having a problem with linked lists. My program is based on a client server model. The client sends some packets of data to the server. The server reads those packets and is...
0
by: erick-flores | last post by:
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...
4
by: grego9 | last post by:
I have two tables that are linked by a common field called 'reference'. i am running a query and want the query to post a text of "EXCHANGE OV CHANGE" against each record where a field called "ORIG...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.