Find rownumber in sql database

[craigachan]

I'm not familiar with the sql database structure. I've encountered a sql table that has no apparent unique ID that I can see. If I link to that sql database in an access app, how do I make sure I'm updating the correct row in the sql database. Something about getting the row_number()?

sql table
month day year code description PatientID
3 3 2020 MS Mastercard 22233
3 3 2020 H2 Histo 2 22233

I guess somehow I don't understand how the sql db keeps track of the record. Thanks for any help.

 

[CJ_London]

without a uniqueID, most likely not possible - (sql server rules, not access). It may be that sql server is using a composite PK, but from the data provided, not possible to say what it might be.

suggest provide an example of a record you want to change, what you want to change and to what

 

[theDBguy]

Hi. If the table didn't have a designated PK, are you able to select a field or combination of fields to uniquely identify each record? If it has to be all fields, then I guess it will have to be (although you would probably do something else like create one rather than use all the fields for a PK).

 

[Galaxiom]

ROW_NUMBER() is an SQL function that sequentially numbers records in a query on the fly. It can have an Order By applied but the sequence number is allocated randomly in the case of an ordering tie so the result is not "deterministic", meaning you won't get the same result every time even if no new data is added into the middle of the ordering.

It really wouldn't be a safe way to add a key directly to the results in a View. You could write the records to another table with an extra column as the PK. Values for that column could generated in the Insert query using ROW_NUMBER or simply use an IDENTITY (autonumbered) column in the destination SQL table.

BTW, ROW_NUMBER can also "Partition" the records. This is like a Group By on columns where the sequence numbers restart for each group.

Be aware that Access will act very strangely even displaying records if you allocate an invalid key during the import. When it hits the same key again it will simply display the original record again even if the other columns of the second record with the same key have different values. Allocating a valid key is imperative even if you are not updating.

 

[craigachan]

Thank you for all for your input. If I populate a listbox thru a select query in its rowsource and then want to update a particular field in a record, how do make sure the correct row is updated in the original sql database? I'm not sure how the update will update the correct record if many records may have similar field data.

 

[Galaxiom]

Like I said before. Access won't even display the records correctly, let alone update them reliably unless you designate a valid primary key.

You must select a combination of fields during the table import that their combination of values will uniquely identify the records in all circumstances or add a unique key column to the SQL table. There is no other way around it.

 

[CJ_London]

As already stated, you need to be able to identify the record uniquely - i.e. absolutely no duplicates. This is usually achieved with a PK, but otherwise it is possible with a combination of fields. Either way, you have said the data is in sql server (presume that is what you meant by sql database) - so you will need a PK because that is how sql server does it.

As a perhaps not very good analogy. You are told to go collect a green car from the car park. When you get there you find hundreds of green cars. How do you tell which one? You either need to know the parking bay number (the PK) or a sufficiently detailed description to eliminate all but one car.

 

[craigachan]

Thanks for all of your suggestions. I'll look at the sql field to see if I can combine fields to make them unique.

 

[CJ_London]

easy enough to do - just use the query wizard to find duplicates. Note that this still may not be sufficient to update from Access but worth a try