Solved Relationships with linked table (1 Viewer)

Cris VS

Member
Local time
Today, 21:28
Joined
Sep 16, 2021
Messages
75
Hello,

I am very new to Access and I am not sure if this makes any sense but I am just going to explain it to see if you can help me. I have to import an Excel sheet to Access. As this sheet is continuously modified, I imported it using the "Linked tables" option. This table contains items that have 4 different attributes: unique ID, Name, Height and Weight. I chose this import option because new items are added very often and sometimes attributes are modified (except the ID).

I need to create a form where the user can select through a combo box the corresponding item and fill in two additional fields that should be stored in the database. My idea was to create a table for these two fields, with an AutoNumber as primary key and a lookup field to relate each record with the ID field of the Linked table's records.

However, when I create the form I have managed to show the item's info when selecting it's ID through a combo box but I cannot fill the other two attributes, as the fields appear but it doesn't let me type. I have tried modifying the join properties and changing the row source of the ID field from one table to the other but I am afraid don't know where the problem is.

I hope I was more or less clear. Could you help me out?

Cheers
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:28
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF!

As much as possible, you should try to base the form on a single table only. The extra information you want to store should be in a separate table than your linked Excel spreadsheet, since you won't be able to edit that table anyway.

So, you bind the form to your local Access table and simply use the RowSource property of the combo to refer to the linked table from Excel.
 

Cris VS

Member
Local time
Today, 21:28
Joined
Sep 16, 2021
Messages
75
Hi Pbaldy, I get an error message saying the file has been permanently moved
 

Cris VS

Member
Local time
Today, 21:28
Joined
Sep 16, 2021
Messages
75
Hi. Welcome to AWF!

As much as possible, you should try to base the form on a single table only. The extra information you want to store should be in a separate table than your linked Excel spreadsheet, since you won't be able to edit that table anyway.

So, you bind the form to your local Access table and simply use the RowSource property of the combo to refer to the linked table from Excel.
So it is not possible to show each item with the 6 attributes in the same form? It is important because the user needs to see the imported information in order to fill the new attributes.

Thanks again
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:28
Joined
Aug 30, 2003
Messages
36,118
Hi Pbaldy, I get an error message saying the file has been permanently moved

With code, or with the link? I just tested the link from 2 different computers, worked fine.
 

Cris VS

Member
Local time
Today, 21:28
Joined
Sep 16, 2021
Messages
75
With code, or with the link? I just tested the link from 2 different computers, worked fine.
Yes sorry, just saw it. I am not entirely sure it is what I am trying... Let me show you an example because maybe I didn't explain it correctly or I didn't understand what you explain in the link.

This is my imported from Excel table - it is a linked table so it has no primary key:

IDNameHeightWeight

I need to see this information in the form:

IDNameHeightWeightNewAttribute1NewAttribute2

The thing is that I want to choose the item through a combo box and have the imported attributes displayed - which is what you explain in the article (... right?)-, but I also want to fill the new attributes, all in the same form. These attributes should be stored in a separate table, which I thought should be something like this:

AutoNumber (primary key)IDreference (foreign key)NewAttribute1NewAttribute2

Maybe the accurate question is
How should I establish the relationships between the two tables (join properties, etc) and how should the RowSource of the form be in order to be able to display the six attributes together?

Thank you very much for your time!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:28
Joined
Oct 29, 2018
Messages
21,358
So it is not possible to show each item with the 6 attributes in the same form?
It's possible, but if you're talking about displaying attributes from multiple tables, you would normally represent them on a form based on the table relationship. For example, if you're trying to represent a one-to-many relationship, you would use a form/subform setup.

If you can post some screenshots or a sample copy of your db with test data, we might be able to show you the way to go.
 

Cris VS

Member
Local time
Today, 21:28
Joined
Sep 16, 2021
Messages
75
Hi, this would be it :)
 

Attachments

  • DatabaseHealth.accdb
    428 KB · Views: 415

theDBguy

I’m here to help
Staff member
Local time
Today, 13:28
Joined
Oct 29, 2018
Messages
21,358
Hi, this would be it :)
Unfortunately, you left out the "sample data" part. You gave us a linked table without the source Excel file, which means we can't open your form.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:28
Joined
Feb 19, 2002
Messages
42,974
The linked spreadsheet is not updateable. When you join your local table to the linked spreadsheet, the result is a not updateable query. When any part of a query is not updateable, the entire query is not updateable. I think Access should be smarter than this but it isn't. If you want this process to work, you are going to have to import the data. Then each time you get a new spreadsheet delete the existing rows and add new ones. This process will prevent you from using RI between the spreadsheet table and your local table or if you do, you need to use DDL to create the constraint after thebdata is loaded and drop it before the data is deleted.
 

Cris VS

Member
Local time
Today, 21:28
Joined
Sep 16, 2021
Messages
75
Unfortunately, you left out the "sample data" part. You gave us a linked table without the source Excel file, which means we can't open your form.
Apologies :)
 

Attachments

  • weightheight.zip
    6.4 KB · Views: 452

Cris VS

Member
Local time
Today, 21:28
Joined
Sep 16, 2021
Messages
75
The linked spreadsheet is not updateable. When you join your local table to the linked spreadsheet, the result is a not updateable query. When any part of a query is not updateable, the entire query is not updateable. I think Access should be smarter than this but it isn't. If you want this process to work, you are going to have to import the data. Then each time you get a new spreadsheet delete the existing rows and add new ones. This process will prevent you from using RI between the spreadsheet table and your local table or if you do, you need to use DDL to create the constraint after thebdata is loaded and drop it before the data is deleted.
So then what use case is the "linked table" import option intended for? Thanks
 

isladogs

MVP / VIP
Local time
Today, 20:28
Joined
Jan 14, 2017
Messages
18,186
Linked Access and SQL Server tables are updateable but not linked tables from Excel. The ability to edit those was removed due to a copyright infringement case dating back to 2002.
There are workarounds including the use of Excel automation or querying the Excel data but you may be better using a different approach.
 

Cris VS

Member
Local time
Today, 21:28
Joined
Sep 16, 2021
Messages
75
Linked Access and SQL Server tables are updateable but not linked tables from Excel. The ability to edit those was removed due to a copyright infringement case dating back to 2002.
There are workarounds including the use of Excel automation or querying the Excel data but you may be better using a different approach.
So if I manage to have the data that will be updated in another Access file or SQL Server instead of Excel, I should be able to query it and work with it in my database?
 

isladogs

MVP / VIP
Local time
Today, 20:28
Joined
Jan 14, 2017
Messages
18,186
Not if it's still a linked Excel table in the same database or another Access database.
You can of course import the data and edit it ...but the Excel file won't be affected.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:28
Joined
Feb 19, 2002
Messages
42,974
There is a way you can work this out using two subforms. You would have one subform showing the linked data and the other showing the values of the local table for the current record in the first table. You might be able to show both lists side-by side. I think there is code posted here that shows how to do that but I worry that users might be confused that is why I would only show ONE updateable value. Or do it the other way around and show your local table as a list and only show the fields for the linked table. That would probably be more confusing because the local table does not have any identifying information. Most people won't recognize an ID.
 

Users who are viewing this thread

Top Bottom