DAO and Access 97

[gavinjb]

I have just inherited an old Access 97 database, which I am making some changes to, the database connects to an SQL Server 6.5 Back End (I plan to upgrade to 2k), I am having problems writng some code in DAO to add records to a table, I have not used DAO for so long that I think the problem is that I cant remember how to get it to work.

The Problem is when I connect to the Database the recordset is readonly, below is the function that I have written, any help would be appreciated.

Sub CreateRecord()
Dim rstDAO As DAO.Recordset
Dim db As DAO.Database

Set db = DBEngine.OpenDatabase("VSVOData_Data", dbDriverNoPrompt, False, _
"ODBC;DATABASE=VSVOData_Data;UID=sa;PWD=;DSN=SQL ServerVSVOMSNSERVER3")

Set rstDAO = db.OpenRecordset("STATUSCOMMENTS", , dbOpenDynamic)

rstDAO.AddNew
rstDAO!DateUpdated = Now
rstDAO!VehicleStatus = Screen.ActiveForm!cboVehicleStatus
rstDAO!REMARKS = Screen.ActiveForm!cboRemarks
rstDAO!COMMENTS = Screen.ActiveForm!cboComments
rstDAO.Update
End Sub

Gavin,

 

[MinusM]

Not worked in Access97 for years now. So not sure of the syntax.
First of all ( atleast in Access 2k )
db.OpenRecordset("STATUSCOMMENTS", , dbOpenDynamic) should be like
db.OpenRecordset("STATUSCOMMENTS",dbOpenDynamic,, dbOptimistic)

Also the DBEngine.OpenDatabase should have the System DSN name as the first parameter.

Best of luck

 

[gavinjb]

Thanks for your help, I worked it out in the end by using the linked tables instead of trying to connect directly (not as good, but it works), had a problem but was due to forgetting to tell access which field is the key field.