ADO & updating

[waheed]

Hi there!,

I wonder if anyone could help me! plz.

I have opened up an ADO recordset using an SQL query.
However when I try to change the textfields in anyway i get
the error message

Run-time error '-2147467259(80004005)': Insufficient base table information for updating or refreshing.

Now i know this because of the query, but I also tried updateing the database via another adorecordset. And when i go back 2 the original recordset to move it to the next record i get the same error message.

How do i save 2 the database or make any other changes?.

the sql query i have used is

SELECT Suppliers.ContactName, Suppliers.Address,Suppliers.City,Suppliers.PostalCode,Suppliers.Phone,Suppliers.Fax,Suppliers.Compan yName, Product.ProductName ,Product.Price,Product.[Batch Number] FROM Suppliers INNER JOIN Product ON Suppliers.SupplierID = Product.Supplier;

plz help!

[chicho_chicho]

Are you sure you can modify a Joined query? I though that was not possible.
Chicho

[JHausmann]

It isn't possible, with one exception. If the DBMS you use supports views (and Access does not), you can update the information in the view ***as long as*** you do it one table at a time.

[Clunietp]

Try using this SQL instead:

SELECT Suppliers.ContactName, Suppliers.Address,Suppliers.City,Suppliers.PostalCode,Suppliers.Phone,Suppliers.Fax,Suppliers.Compan yName, Product.ProductName ,Product.Price,Product.[Batch Number] FROM Suppliers, Product where Suppliers.SupplierID = Product.Supplier;

[Harrild]

i've had the same problem for a program i was writing (and still creating). Do everything in ADO dont try and mix the code up cos its bloody hard to get it right.

this is my code for add a new record just modify it for updating.

(all under a button of course!)

Dim db As Database
Dim RecDB As New ADODB.Recordset
Dim ConnectDB As New ADODB.Connection

ConnectDB.Open ("Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=DATABASENAME.mdb")

RecDB.Open "TABLE NAME", ConnectDB, adOpenKeyset, adLockOptimistic

RecDB.AddNew
RecDB.Fields("Field 1") = textbox1.Text
RecDB.Fields("Field 3") = textbox2.Text

RecDB.Update
RecDB.Close
ConnectDB.Close

end sub

this code works i use all the time for adding and some where i do have the update code but i cant find it at the moment.
ADO good.
DAO bad.

[JHausmann]

Waheed will just need to do it twice because he has _two_ tables.