access 2003 to 2007 conversion: "Method or data member not found" 1

[aldi07]

Hi,
I converted an access 2003 mdb into an access 2007 accdb. The references checked are:
- Visual Basic For Application
- Microsoft Access 12.0 Object Library
- OLE Automation
- Microsoft ActiveX Data Objects 2.8 Library
- Microsoft Visual Basic for Applications Extensibility 5.3
- Microsoft ActiveX Data Objects Recordset 2.8 Library
- Microsoft Office 12.0 Authorization Control 1.0 Type Library
- Microsoft Office 12.0 Access database engine Object Library

I get the "Method or data member not found" error on two statements:
<< If MyTable1.NoMatch Then ....>> .NoMatch is the highlighted error here. I removed that statement just to see if it was the only one, but I got the same error on:
<< MyTable1.Edit >> .Edit is the highlighted error this time.

*** It was working perfectly well in access 2003, with the reference << Microsoft DAO 3.6 Object Library >> that cannot be used in access 2007 of course ***

Thank you in advance for your help.

 

[dhookom]

This reference should work in place of DAO
Microsoft Office 12.0 Access database engine Object Library
Please share more of your code. I expect you have made the mistake of not explicitly specifying your objects.



Duane
Hook'D on Access
MS Access MVP

 

[aldi07]

Thank you for your answer dhookom.
What I finally did, is open a new access database 2007 (accdb of course).
I then imported everything from the old mdb database as is, into the new empty accdb one. I added one reference, modified all the strings mestionning ".mdb" to ".accdb", and it works like a charm.
So my advice to people using VBA and converting from mdb into accdb, is not to convert using the convert button, but process the same way I did, and they will avoid a lot of trouble.

 

[dhookom]

Again, I think your issue was
1) not being explicit with your declarations and
2) the order the references are listed:
- Microsoft ActiveX Data Objects Recordset 2.8 Library
- Microsoft Office 12.0 Authorization Control 1.0 Type Library
- Microsoft Office 12.0 Access database engine Object Library
If the ActiveX Data Objects was after the Access database engine, you might have been ok. Either way, IMO, always be explicit with your declarations.

Duane
Hook'D on Access
MS Access MVP

 

[aldi07]

<<Again, I think your issue was
1) not being explicit with your declarations and
2) the order the references are listed:>>

You probably have a point concerning the references' order. Actually, I eliminated two of them:
- Microsoft ActiveX Data Objects Recordset 2.8 Library, and
- Microsoft Office 12.0 Authorization Control 1.0 Type Library
I realized I didn't need them.

As for the declarations, there is an 'Option Explicit' at the top for all the different VBAs, and all my variables are declared in 'Dim' statements. Is this what you meant?

For example:

Option Compare Database
Option Explicit

Private Sub NetworkPath()
On Error GoTo Err_NetworkPath
Dim MyDB As Database
Dim MyTable1 As Recordset
Dim frmCurrentForm As Form
Set frmCurrentForm = Screen.ActiveForm

Set MyDB = CurrentDb
Set MyTable1 = MyDB.OpenRecordset("tblPath")
Me![MyPathDB] = MyTable1![MyPath]
MyTable1.CLOSE
MyDB.CLOSE

Exit_NetworkPath:
Exit Sub

Err_NetworkPath:
MsgBox Err.Description
Resume Exit_NetworkPath

End Sub

Private Sub btn001_Click()
On Error GoTo Err_btn001_Click

DoCmd.SetWarnings False
DoCmd.OpenQuery "qry_00_tblLPCS_Del"
DoCmd.OpenQuery "qry_01_LightingPowerAndConsumptionSavings_App"
DoCmd.OpenQuery "qry_02_DelampingPower_Upd"
DoCmd.OpenQuery "qry_03_ConversionPower_Upd"
DoCmd.OpenQuery "qry_04_ConversionAndPowerSavings_UPD"
DoCmd.OpenQuery "qry_05_YearEndCalculation_UPD"
DoCmd.OpenQuery "qry_06_AvgDailyOp_Upd"
DoCmd.OpenQuery "qry_07_MSAvgDailyOp_Upd"
DoCmd.OpenQuery "qry_08_ConsumptionSavings_UPD"
DoCmd.OpenQuery "qry_09_ConsumptionSavings_UPD"
DoCmd.SetWarnings True

Me.btn002.Visible = True
Me.btn003.Visible = True
Me.Box21.Visible = True

etc... etc...

 

[dhookom]

should be

    Dim MyDB As DAO.Database
    Dim MyTable1 As DAO.Recordset

Duane
Hook'D on Access
MS Access MVP