Edit Access tables

[ClulessChris]

I use the following code trying to update / edit two Access tables:

Dim db as database
Dim rs as recordset
Dim sSql as string

sSql = "SELECT Feild1, Feild2, tbl2.Feild3 "
      "FROM tbl1, tbl2 "
Set db = OpenDatabase(FilePath)
Set rs = db.openrecordset(sSql, dbOpenDynaset)

With rs
    .Edit
    'Do Stuff
    .Update
End With
' Close Connections

But as I hit the .Edit i get err: "Object or Database is read only"
I've solved this by editing the tables seperatly, But I thinks to myself there must be a better way. Is there a better way or is this the way it is?



Everybody body is somebodys Nutter.

 

[baderms]

Dear ClulessChris,

You have to the sql statement joining the tables. Here's an example.

SELECT tbl_Volunteers.Prefix, tbl_Volunteers.LNAME, tbl_Volunteers.FNAME, tbl_Volunteers.Suffix, Table1.Address
FROM tbl_Volunteers INNER JOIN Table1 ON tbl_Volunteers.VolunteerID = Table1.VolID;

 

[gwar2k1]

Silly question, do you have the database open already when you try searching it from your VB app?

Ive found out recently that DAO is the way to go when creating a database but Id stick to my personal favorite ADO for accessing, editing a current database.

db as ADOX.catalogue
etc.

Check the FAQs for excellent ADO how to's =D

~*Gwar3k1*~
"To the pressure, everything's just like: an illusion. I'll be losing you before long..."

 

[ClulessChris]

Baderms
Many thanks I had not tried INNER JOIN's, I'll do that now.

gwar2k1
many thanks for your interest. no the db was not open elsewhere.

I've been given to understand that although ADO is generaly the best this is not so when connecting to MS Access (Access seems to get along better with DAO)?

Everybody body is somebodys Nutter.