OpenRecordset error

[lcline]

Thanks in advance!

Long story short. In the process of upgrading from 97 to 03. Some users still have 97 others 03; may be several weeks until all are converted. Had a database that I could not convert, instead had to create new db in 03 and import all (could not open in 03 because of security). When imported and converted back to 97 the following that was working will not work. Please help me understand what is wrong.


Run-time error '13'
Type mismatch

Dim MyRS As Recordset

Set MyRS = CurrentDb.OpenRecordset("tblMain") 'Debug goes to this line

MyRS.AddNew
MyRS("DateEntered") = Me.txtDateEntered.Value
MyRS("ICT") = Me.cmbICT.Value
MyRS("Department") = Me.cmbDept.Value
MyRS("Station") = Me.cmbStation.Value
MyRS("LineNum") = Me.cmbLine.Value
MyRS("JobCategory") = Me.cmbJobCat.Value
MyRS("StartTime") = Me.txtStart.Value
MyRS("CompletTime") = Me.txtFinish.Value
MyRS("Equip") = Me.cmbEquip.Value
MyRS("ActualTime") = Me.txtActual.Value
MyRS("DownTime") = Me.txtDownTime.Value
MyRS("Completed") = Me.ckComplete.Value
MyRS("Problem") = Me.txtProblem.Value
MyRS("PartSolutions") = Me.txtFix.Value
MyRS("IctComment") = Me.txtComment.Value
MyRS("Shift") = Me.cmbShift.Value
MyRS("SpecificStuff") = SpecificStuff
MyRS("Sensor") = Me.cmbSensor

MyRS.Update
MyRS.Close

 

[Brian1960]

Replace

Dim MyRS As Recordset

Set MyRS = CurrentDb.OpenRecordset("tblMain")

with this

Set MyRS = New ADODB.Recordset
MyRS.Open "tblMain", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

(Recordset is not recognised)

 

[RuralGuy]

The default for ac2K3 is ADODB (ActiveX). Just set a reference to the DAO 3.6 Object Library and disambiguate the declaration:

Dim MyRS As DAO.Recordset

<ALT> F11 Tools>References scroll down to Microsoft DAO 3.6 Object Library and put a check there. You could also UnCheck the ActiveX (ADO) reference since it appears you were not using it.

There is no reason to switch to ADO. Actually, DAO is the native language of the Jet 4.0 Engine and faster in most cases than ADO.

 

[lcline]

Thanks!

Thanks!
Set MyRS = New ADODB.Recordset
MyRS.Open "tblMain", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
That did it, but why did it work before and not now? As I said, I imported the 97 stuff into 03 and then converted it back. It would not work in either the 97 or 03 after the import.
Thanks again

 

[lcline]

Spoke too soon

This fix
Set MyRS = New ADODB.Recordset
MyRS.Open "tblMain", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
works fine in 03, but when converted back to 97, it does not recognize the CurrentProject.Connection. Any suggestions as to what I can do differently?

OR the reason for the conversion is that the existing 97 database can't be opened with 03. The message is that user does not have enough rights to open or convert db. How do I change the rights?
Thanks,
Lee