linking fields from one db to another? (Access XP)

Topic

New Reply

I’m back. I continue to have real problems trying to use the Update Query in the database I want to create. In our last episode I discovered that I cannot use the Update Query to update data from a linked table to another table. Is there a feature in Access similar to Excel that allows you to “link” fields from one db table to another or, even better link one field in a db to another field in another db?

I have even tried importing the table that I want to update from. Access will not allow me to update from an imported table either. The data that I am trying to update from is coming from a text file. This is frustrating because I can easily accomplish what I am trying to do with Excel but I can’t seem to get it to work in Access.

Thanks loads,

Bret

Reply | Quote

Replies

<>
I think you will find that Hans suggested that you cannot link to a text file and use an UPDATE query.

<>
Why don’t you post your database here for all of us to help, don’t forget the text file unless it’s already in a temporary table. Tell us the name of your query as well.
Failing all of this post your UPDATE query.

Pat

Reply | Quote

Pat,
I can’t seem to get it small enough to attach. The linked table is all that contains any data and zipped I am still 112 kb.

Thanks,

Bret

Reply | Quote

Send it to the email in my profile.
Pat

Reply | Quote

[indent]

---

Is there a feature in Access similar to Excel that allows you to “link” fields from one db table to another or, even better link one field in a db to another field in another db?

---

[/indent] No, there isn’t. Access has a structure that is much more rigorous than anything in Excel, so you won’t be able to do the kind of “freeform” stuff in Access you might get away with in Excel. If you give a full explanation of what you’re actually trying to accomplish, rather than than just what you’re trying to do, maybe someone will be able to help you.

Reply | Quote

[indent]

---

Access will not allow me to update from an imported table either

---

[/indent] Sorry, but that doesn’t make any sense. There is no restriction on updating from one table to another or even within the same table as long as they are Access tables. Once you import data into Access, it’s in an Access table. That means that if you can’t update using Access tables, your query is wrong or your table structures don’t have the necessary indexes to make an update possible.

Reply | Quote

Charlotte,

I have been trying to get this to work of sometime. Pat and Hans have been giving me as much help as possible. The error message I get when I try to Update from the linked table (linked from a text file) is, “Updating data in a linked table is not supported by this ISAM”.

Today I tried creating an Append query and then updating from the Appended table. No luck there either.

Bret

Reply | Quote

Bret
Got your DB, however there is nothing in the tables. All 4 tables are empty, the linked table is not there obviously.
I compacted your DB down to 268Kb, so it should go under 100Kb.
Post your DB with some data in both tables, Append and Products.
The 2 tables would have to have fields to join on, can you tell me what they are.
Also include the query you are trying to use.
Pat

Reply | Quote

You said you couldn’t do it with imported tables. That is not the same thing as linked tables and I have no idea what you meant by your last sentence. Imported tables are Access tables created from data imported from some other database or file, but they are not linked tables. An append query inserts data into an Access (or SQL Server) table, but it won’t update a linked file.

Reply | Quote

Charlotte,

Someone had suggested that I might be able to accomplish the Update Query if the table was created from an imported table of file. I still get the same error message when I work with an imported table.

The appended table was my idea. I just want to figure out how to make this work so, I reasoned (right or wrong) that if I created an append table that originated in the database that I am trying to create the Update Query in that it would allow me to then update from the Appended table to another table. It won’t.

What I am trying to do is create a recipe based inventory system for my restaurant. I am at best a novice when it comes to Access. It seems to me that it should be a simple thing to take data from an Access database (my POS system) and create another database that will tell me how many of what items I need to order based on what I have sold. Example, I sell a cheeseburger, I need to have the beef patty taken from inventory, the bun, the wrapper, the condiments, napkins and other associated goods. Based on a days sales count of all the food we sell I want an inventory system that will tell me what I should theoretically still have on hand in inventory.

We are currently using a Palm Pilot with barcode scanner to take a weekly physical inventory. I want to update my on hand inventory numbers from the text file that is created by that text file. This is the problem that has brought me here.

Thanks for your time,

Bret

P.S.
Why not buy a built database that will do this? I have tried, nothing seems to exist that will do as I need it to do. Why not pay someone to create it for me. I have had quotes from $3,000 usd to a low of $700 usd. I figure for either one of those amounts I will learn to do this myself or I will continue to struggle through with Excel.

Reply | Quote

Bret
I got your database with the text file as well. I can import the data using your query “S01_data Query” into the Append table.

I compacted your DB and zipped it down to 52Kb so you should have no trouble in posting it on the lounge.

I notice that there is the Products table but there is no field in this table to join to the Append table (used to store the imported S01_data), it should probably be the Barcode field. There is also no data in Products.

Post your DB with some data in Products. Also include the query you are trying to use to update the Products table from the Append table.

Pat

Reply | Quote

It’s seems a bit surprising that nobody is offering a commercial package for restaurants that ties into the POS system, but it seems that if your POS is Access based, then it should be fairly straightforward. On the other hand, inventory system are a challenge for most businesses – people tend to have unreasonable expectations about what they will do, and fail to realize that you will always need to make manual adjustments to the quantity on hand, as wasteage and other similar issues aren’t usually accounted for.

In any event, the problems you are having are IMHO mostly the result of inexperience with Access. You should be able to write a query that updates a table in Access, as long as it is not a linked table that is a text file or an Excel workbook. Pat’s comment about needing a unique identifier for the imported text table that lets you make a query updatable appears to be on target. If this continues to frustrate you, you might try to find a local consultant who will spend a couple of hours with you to sort through these issues. Good luck.

Reply | Quote

Hi Bret
I have changed your Products table to include a Barcode field. I then put a couple of records into Products and changed your query (named “Append Query”) to update the UnitsInStock in the table Products with the field “On Hand” in the Append table (this table contains the imported S01_Data).
Pat

Reply | Quote

Hi Bret
Have you solved this problem yet?
Pat

Reply | Quote

I haven’t found the time to attack it with your last suggestion. Thanks for asking. Maybe later tonight, I keep running into little stumbling blocks this weekend and today.

Reply | Quote