Update Query problem

[TooManyReports]

OK, I attached a sample database. I hope it works like it does here.

 

[vbaInet]

I hope it works like it does here.

I don't want to download it to find that it's working ok. Have you tested it to ensure that the UPDATE query fails?

 

[TooManyReports]

I don't want to download it to find that it's working ok. Have you tested it to ensure that the UPDATE query fails?

Looks like the UPDATE query works fine if it a local table, but no when it is the AS400 table (DW_PRICE is an ODBC connected table on the primary database).

 

[vbaInet]

(DW_PRICE is an ODBC connected table on the primary database).

Well, that would have been a useful piece of information in the first place.

Does the DW_Price table have a Primary Key?

 

[TooManyReports]

No, DW_PRICE is non-index with no primary keys.

Sorry about the ODBC thing. I assume since that I said that is was AS400 table, people would think ODBC. I guess not.

 

[vbaInet]

From what I understand it needs a PK in order for it to work.

 

[TooManyReports]

I guess you are right, but why does it work locally without any PK or indexing? Permission problems or ODBC problem?


Never mind. You have done what you could me. I guess it just will not happen until my company changes its database policies and does them the right way.

 

[vbaInet]

Because the table is local it doesn't need a PK. Linked tables act that way. I would imagine that you can insert into it without problems but it's the Updating that needs a PK.

If the DW_Price table has a relatively small dataset, you can pull the data into a local table and use the local table in the UPDATE query.

 

[TooManyReports]

No, it will surpass the 2GB limit of Access. Over 7 million record there.

 

[vbaInet]

With the joins in place, how many records on average will it pull from DW_Price?

 

[TooManyReports]

You know I did not think of that. Daily Change only has 10000 records in it currently, so it would max out at daily changes record limit. I could do it that way. Well it should only end up being about 6 - 7k coming back realisticly.

 

[vbaInet]

So if that table doesn't change often you could run a daily insert into a local table and run your updates on that.

Or

* Run a daily insert into the local table
* Each time the UPDATE is run, run a query to find newly added records and insert it into the local table (basically appending to the local table but using an INSERT)
* Then run the UPDATE query.