1

My sql 2008 linked query data has no primary key column - because I need to bulk update duplicate rows (that are duplicate in 2 or more columns).

When I link the query (I have it as a sql view for now) - the records load in the form ok.

Is there a way to override the form to alow me to edit the data in the textboxes and then click a button to run the custom code to update the linked database tables?

Hope that makes sense.

thanks,

KS

Perplexed
  • 877
  • 2
  • 19
  • 32
  • I would not recommend using bound forms against tables with no primary key, it needs to be able to uniquely identify the rows in order to perform the updates, you would be better with an unbound approach and update the table via an ADO connection to the SQL server. – Matt Donnan Jan 30 '12 at 18:50
  • Ok, how do I set up the form controls to the recordset from ado? How do I assign the recordset fields to the controls so that all the records are populated in the list. ie, Loop records and assign values to controls? – Perplexed Jan 30 '12 at 19:11
  • 1
    The issue which puzzles me is, without a primary key, how will you identify which row in the linked data source is the one to be updated? If you already have a strategy to deal with that, you should tell us about it because it could be important information for anyone attempting to give you an answer. – HansUp Jan 30 '12 at 20:35
  • @Perplexed I agree with HansUp, could you provide a little more clarity please as to why to have your data structured in this way; also I'm surprised Access did not ask you to identify the primary column whilst you were linking the table, this is a big hint – Matt Donnan Jan 31 '12 at 11:45

1 Answers1

0

You can write custom code to update rows in a linked table, the absence of a primary key should not be a problem as long as you can identify the rows you wish to update.

currentdb.execute "UPDATE LinkedTable SET thisField='Yes' WHERE conditionsThatIdentifyTheRows"
ChrisPadgham
  • 860
  • 5
  • 4