DAO.Database not defined error (1 Viewer)

[RayH]

I'm trying to use :
Private Sub Form_Open(Cancel As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.openrecordset("Tablename")
etc...

to add a record before anything else happens. The table I'm trying to add a record to is not used in the form at all.

But I get an error on the 'dim db DAO.Database' line stating:
"Compile error: User-defined type not defined"

The code I'm using is based on code found in these forums.
I don't understand why I'm getting the error as it seems to work for other members. Should it be defined somewhere? I guess I'm missing something.
I'm using Access 2000.

Thanks

 

[fuzzygeek]

DAO.Database

You have to select the dao reference. Go to tools on the menu then select reference. Go down until you see the dao reference (Microsoft Dao <version number> Object Library).

 

[NJudson]

Not sure if this is the fix but while in the module goto Tools>References and make sure that you have the correct references for DAO, Active X etc. selected.

HTH

 

[RayH]

Thanks guys it worked a treat, would never have thought of that.
Anyway, now thats working I have come across another problem.

I need to be able read a record and update it.
I've managed to retrieve the record using OpenRecordSet(SQL) but can't update it as its read only., understandably, I suppose.
Using OpenRecordSet(tablename), I think, will allows access to all the records in the table. How can I narrow it down to just the record I want and update it?

Thanks

 

[fuzzygeek]

The following is an extract from a white paper from MS

Updating Existing Records
The following code demonstrates how to open a scrollable, updatable Recordset and modify the data in a record.

DAO

Sub DAOUpdateRecord()

Dim db As DAO.Database
Dim rst As DAO.Recordset

' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")

' Open the Recordset
Set rst = db.OpenRecordset _
("SELECT * FROM Customers WHERE CustomerId = 'LAZYK'", _
dbOpenDynaset)

' Put the Recordset in Edit Mode
rst.Edit

' Update the Contact name of the
' first record
rst.Fields("ContactName").Value = "New Name"

' Save the changes you made to the
' current record in the Recordset
rst.Update

' Close the recordset
rst.Close

End Sub

ADO

Sub ADOUpdateRecord()

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset

' Open the connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=.\NorthWind.mdb;"

' Open the recordset
rst.Open _
"SELECT * FROM Customers WHERE CustomerId = 'LAZYK'", _
cnn, adOpenKeyset, adLockOptimistic

' Update the Contact name of the
' first record
rst.Fields("ContactName").Value = "New Name"

' Save the changes you made to the
' current record in the Recordset
rst.Update

' Close the recordset
rst.Close

End Sub

Alternatively, in both the DAO and ADO code examples the explicit syntax:

rst.Fields("ContactName").Value = "New Name"

can be shortened to:

rst!ContactName = "New Name"

The ADO and DAO code for updating data in a Recordset is very similar. The major difference between the two previous examples is that DAO requires you to put the Recordset into an editable state with the Edit method. ADO does not require you to explicitly indicate that you want to be in edit mode. With both DAO and ADO you can verify the edit status of the current record by using the EditMode property.

One difference between DAO and ADO is the behavior when updating a record and then moving to another record without calling the Update method. With DAO any changes made to the current record are lost when moving to another record without first calling Update. ADO automatically commits the changes to the current record when moving to a new record. You can explicitly discard changes to the current record with both DAO and ADO by using the CancelUpdate method.

The paper is at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/daotoadoupdate.asp

This paper contains a lot of examples for accomplishing things in ADO and DAO

 

[RayH]

Fuzzy,
Using the DAO example you gave, my code still states that 'Database or object is read-only".
It gets as far as rst.edit.
I did not use the opendatabase line as the database is already open (I assume) instead I'm using Set db=CurrentDb.

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT Tbl_MyTable.Field1, Tbl_MyTable.LastUsedDate, Max(Tbl_MyTable.FirstUsedDate) AS MaxOfFirstUsedDate FROM Tbl_MyTable GROUP BY Tbl_MyTable.Field1,Tbl_MyTable.LastUsedDate;", dbOpenDynaset)

rst.Edit ' gets this far

rst.Fields("LastUsedDate").Value = Now

Whats wrong with this picture?

 

[fuzzygeek]

Problem is the query

Your query is not updatable. It is an aggregate not a specific record, but a grouping of records.

 

[RayH]

The results of the query gives me the record I want. If this record type cannot be updated how can I get the record I want without using aggregates?
I don't know the LastUsedDate so I can't implicitly use it.
Sorry but I just don't get it.

 

[fuzzygeek]

Experiment

Go to the query itself, the results and see if you can update there (outside of code). If so, it's not the problem. If you can't then it is the problem. ... I'll be in meetings the rest of the day. If someone else has ideas, please jump in.