hello ;
Access 2000, sql server backend

I need to pass a value to an integer variable. The dbfield is the
rowid (the key) of the table.
The logic is as follows (this is just a mockup)
(the integer variable is rownumvar)

rownumvar=select rowid from table1 where state = statevar and
customerid = custvar

I am first getting the values of the 2 other variables no problem
within a loop.

How can I, within the loop, get the rowid from another table based on
those two variables?

Thanks in advance for any assistance.

Sincerely,
Tony

Tony_E wrote:
Oh no. In a loop? Time to back up and provide us more details so we can
recommend a better way to solve this problem.

On Apr 11, 3:10*pm, "Bob Barrows" <reb01... (AT) NOyahooSPAM (DOT) com> wrote:
hello
yes sir. In a loop because there are multiple records I have to
update.

I have 2 tables

1 table is linked into Access - it is a sql server table, this is the
table I have to update.

2nd table is an Access table.

These 2 tables have 2 fields in common. What I have found is that my
updates are ignored
when I run it using these two fields as the linking fields.

What I mean by that is: I do an open record set with the Access table

Do while not eof

I get the values from each record
for those two fields

Process an update statement using
those 2 fields in the where clause.
And it gets ignored.

What I have found is that the update works if I can get the value of
the rowid field from the sql server table.
(I tried this in a hard-coded test)

So I want to use the value from those 2 fields to get the value of the
rowid and then use rowid as the where clause
in the update. .....for every record.

Tony_E wrote:
Ignored? I can well imagine them taking a long time to be performed ... but
ignored? Are the datatypes of the fields compatible. I would expect an error
message if they weren't but I have to ask.
Are thery any differences at all in the data contained in the two tables?
That would be the only thing I can think of that would cause a query to run
without error but not to update any records in sql server. This can be
easily tested: if you create a select query instead of an update query
joining these two tables on those fields, does it return any records?

My preference would depend on the relative amounts of data in the two
tables. Which is the larger table?

If there are only a few records meeting the criteria in the Access table, my
preference would be to use a query to export these records into a work table
in sql server and use an update query to update the relevant records in the
sql server.

On Apr 12, 6:38*am, "Bob Barrows" <reb01... (AT) NOSPAMyahoo (DOT) com> wrote:
Hello;
Thanks for the reply. Yes I have been getting these results probably
because the field I am trying to update is a Binary field. What I did
was build a working table in Access (created by a mkt tble query) from
the two tables. Then I was able to run the update to the binary field
in vba successfully (using the the rowid field).

Thanks and I appreciate you working with me in this bizarre pain in
the neck scenario.

Sincerely,

Tony