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 & "'");

 

[Pat Hartman]

To avoid radically slowing either Sage or Access, I don't keep the Sage/Access link open, but just when required

- permanently linked tables do not slow down operations or put an undue burden on the RDBMS. In fact, it is just the opposite. Unless all your queries are pass-through queries, having linked tables speeds up Jet's processing of queries and enables it to do less processing prior to sending the query off to the remote database.

Unless you have a process capturing change on the Sage side, the only way to determine if a record has changed is to compare it to a known value at a specific point in time. This processing is very time consuming. Except for the problem of database bloat, it is actually more efficient to simply delete the imported data and re-import new values.

As long as Access forms are based on queries with where clauses to limit the number of rows returned, linking directly to the Sage database is more efficient than importing it and trying to keep it updated. 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.

 

[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!)

 

[Pat Hartman]

You may need to define a psuedo key for the Sage table when you link it. Jet REQUIRES that all tables in a query be updatable in order for the query to be updatable. One of the requirements for considering a linked table to be updatable is that the linked table has a primary key or unique index. If Jet doesn't "see" the actual key when you link the table, it will prompt you to define one. Make ABSOLUTELY CERTAIN that you choose the correct field or fields to uniquely identify a row. If you don't, you will get strange results from your queries and if you update the linked table, you COULD corrupt it.

 

[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

 

[Pat Hartman]

The easiest thing to do is to delete the current link and then relink the table. You should get a dialog that asks you to choose which field(s) you want to define as the primary key.

 

[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

 

[Pat Hartman]

In general, should the users just not be allowed to access all the records in a table?

- They are allowed access to all the records in a table. They just can't randomly scroll through them. The larger a table gets, the less likely the user will be interested in scrolling through it. No one would have even considered scrolling through my 90 million row customer table in a project I did for Master Card. Most Access tables are significantly smaller than 90 million rows so we don't really think about it. But, pretend for a minute that the table is huge and therefore not scrollable so you must come up with a form that allows the user to navigate to the record he is interested in via selection criteria. Frequently the user will know the customerID or customerName so make it very easy to use primary key criteria. In some cases, you'll need to provide other selection criteria so the user can limit the number of records returned to a small, scrollable recordset.

The upshot of this is that I usually have criteria forms between the menu selection and the actual form. I also add menu items specifically for adding records. Therefore, when a user needs to add records, there is no need to populate a recordset with the entire contents of the table. The whole thing is much faster if you just use the option of the OpenForm method that allows you to open your regular form in DataEntry mode rather than Edit mode. So on the menu, "Add Customer" opens the Customer form directly in Data Entry view whereas, "Edit/View Customer" opens a criteria form which then opens the Customer form in Edit view.

 

[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?

 

[Pat Hartman]

No. I have seen one that talks about Access and SQL server but I don't think this will help you since it concentrates on .adp's which is why I didn't buy it. .adp's will limit you to SQL which is why Microsoft pushes them. I'll stick to .mdb's as long as they exist. They give me the flexibility of linking to ANY ODBC datasource.

Search the MSDN library and the knowledgebase. You will find articles on how to optimize client-server databases. Also look for the updatable queries article. It is old but still relevant. Help also has a good entry on updatable queries.