insufficient base table information

[spiveygb]

I'm unable to update a field in the recordset and I receive the "insufficient base table information" error. I need to be able to update the "bridges_pts_threat.threatco"
field. I'm using a client side cursor with the following sql statement. Any suggestions? Thanks.

	strSQL =          "SELECT usgsdata.current_ht, usgsdata.site_id, bridges_pts.uid, bridges_pts.bank_full, bridges_pts.ref_id,"
	strSQL = strSQL & " bridges_pts.margin, bridges_pts_threat.threatco FROM usgsdata.dbf INNER JOIN bridges_pts.dbf"
	strSQL = strSQL & " ON usgsdata.site_id = bridges_pts.ref_id INNER JOIN bridges_pts_threat.dbf"
	strSQL = strSQL & " ON bridges_pts.uid = bridges_pts_threat.uid;"
	'strSQL = "SELECT * From bridges_pts_threat.dbf"
	openRS rs,strSQL,oConn,adOpenStatic,adLockOptimistic,adCmdUnknown

 

[NoCoolHandle]

What sort of database are you using?

Also some db's don't allow updates via joins...

I tend to avoid as a practice the idea of doing updates via recordsets and work more with storedprocs or update statements.. Faster and more secure.. Also no flakey issues.

1 other thought, do you have primary keys in the sql statement. a reason to fail is if it can't identify the record to uddate..

For what it is worth


Rob

 

[spiveygb]

I'm using standalone .dbf files so stored procedures aren't an option. Primary keys are in my sql statement. One solution I could do is perform all the calculations using data from the first two tables, read them into an array, and then open a separate recordset of the third table and populate with the results. Seems like a crooked way of doing it but it will probably work.

 

[NoCoolHandle]

Why not leave the recordset alone, but have the update done explicitly when a user clicks ok..

It will probably requre you write some extra code to handle the loading of controls and updates, but you might find it worthwhile.. No accidental updates and you can keep your joins...

Rob