Update query using a linked ODBC table as source - Please help!

[marblegiant]

Hi,

I'm building a database of people, linked to a list of organisations taken from Sage as an ODBC linked table.

To avoid radically slowing either Sage or Access, I don't keep the Sage/Access link open, but just when required - using a query to maintain a copy of the relevant fields from Sage in my Access database.

For the most part this is working fine. I'm using an append query to take any new organisations in Sage and add them to a table of organisations in Access.

But what I'm now trying to do is to use an update query to update the table in Access with any changes made to any record (such as a change of address) in the Sage. And I can't get it to work.

As far as I can see, an update query insists on a one to one link. There is (theoretically) a one to one link between the Sage organisations and the Access organisations. But unfortunately, because I can't set a primary key in the Sage table, it isn't a one to one link that Access and the query recognises. So I just get a 'Operation must use an updateable query' error.

This is the SQL that I'm using: UPDATE SALES_LEDGER
INNER JOIN ORGANISATIONS ON ORGANISATIONS.ACCOUNT_REF=SALES_LEDGER.ACCOUNT_REF
SET ORGANISATIONS.ACCOUNT_REF = SALES_LEDGER.ACCOUNT_REF, ORGANISATIONS.NAME = SALES_LEDGER.NAME;

Any help would be very much appreciated.

W

 

[ByteMyzer]

Try this:

UPDATE ORGANISATIONS
SET ORGANISATIONS.NAME=DLookup("[NAME]","SALES_LEDGER","[ACCOUNT_REF]='" & ORGANISATIONS.ACCOUNT_REF & "'");

 

[marblegiant]

Wow! Many thanks for your two very speedy and VERY helpful responses. Very much appreciated. I'm completely new to the world of Access and SQL and not finding it that easy...

ByteMyzer's method worked perfectly first time (having tried everything I could think of before, and failed, this was SO refreshing!).

But running the query really highlighted the point that Pat made. Updating one field on 750 records took 35 seconds. But in reality I'll need to update 12 fields on 4000+ records. Which, by my maths, could take half an hour!

So, I'll follow my understanding of Pat's advice. I'm planning on setting up a data database and a queries/forms/reports database linked to the data database. And I'll keep the ODBC link to Sage as 'just another table' in my data database.

Many thanks to you both once again. Really appreciated.

W
(PS If you can recommend any SIMPLE books... thanks again!)

 

[marblegiant]

Hi,

Many thanks again for all of your help.

I didn't mean to bother you again, but just wanted to confirm that, if I don't ever want to update the data in the linked table, do I need to worry about the pseudo key that you mentioned?

I'd rather not give any access to Sage, or alter any data in Sage, just take information from it.

But if I do need to worry about the pseudo key, how do I assign one to a linked table? I can't find any reference to a pseudo key in any of the help etc files that I've got.

Thanks again

Wayne

 

[doulostheou]

Access db's created by developers used to working with Jet tables can cause network problems when converted to use ODBC data sources. An Access form based on a table or a query without selection criteria is like an open pipeline to the back end database. Jet just keeps pumping data across the link until all the rows are retrieved from the source table. This a DBA's nightmare and why Access gets such bad press in the ODBC world. But the problem is not with Access but instead with unknowing developers.

Pat,

I read what you wrote above and was afraid I may be one of those unknowing developers. I am a self taught database developer who learned most of what I know within a MS Access environment. I am at a new job now and am attempting to work with a MySQL database through ODBC.

I am actually having a problem because when my code that tries to grab data from the MySQL tables runs it returns a permission error (which does not occur if I have already linked to a table in the session).

But in addition to the above problem, I want to make sure I understand your post. Should I always use a WHERE clause when connecting to the MySQL tables? In general, should the users just not be allowed to access all the records in a table?


Thanks,
Kris

 

[doulostheou]

Thanks for the explanation. I can see the foolishness of allowing the user to scroll through all the records on an extremely large table. Though my database is not that big, we intend for it to get big; so I will try to start changing my thought process now.

I'm sure you're asked this question frequently, but do you know any good books that would thoroughly discuss the intricacies of using MS Access as a front end to a MySQL database?