[RESOLVED] Runtime error 438 Object doesn't support this property or method

[marktheman]

Hi,
I'm doing a simple excel project and part of it is updating fields in a table. Say the recordset has 500 entries. For some entries, when I update I have no issue but when I update older entries from an access database, I get the following error. "Runtime error 438 Object doesn't support this property or method". I'm using ADO connectivity and excel as a database
Example:

rs.Open "SELECT * FROM [tblfissection$] Where RequestNo = " & requestNoSubstringed, conn, adOpenStatic, adLockOptimistic, adCmdText
On Error GoTo updateFISTable
'rs.Fields(1) = ""
'rs.Fields(2) = DTEvaluationDate.Value
rs.Fields(3) = TextProposedSolOutline.Text

Any ideas

[sparbag]

Hello,
From Microsoft website:

Run Time Error 438 - Object Doesn't Support this Property or Method
The most common cause of error 438 is not maintaining binary compatibility between successive versions of your components. Each COM interface has an associated GUID that is called an interface ID (IID). Each coclass has an associated GUID that is called class ID (CLSID). When you compile an ActiveX component in Visual Basic, the CLSIDs and IIDs are compiled into the component's type library.
Example
A program that consists of a Visual Basic client and an ActiveX DLL is released to the user community. At a later time, additional functionality is to be added to the DLL component. The necessary modifications are made, and the ActiveX DLL is compiled without maintaining binary compatibility. When the DLL is released, the client that is trying to use the DLL will throw run time error 438. The reason this occurs is that when the DLL was compiled, a fresh set of GUIDs was compiled into the DLL, and the client has no reference to these new GUIDs. This is why it is important to maintain binary compatibility with the last-released version of the component when you are trying to release a newer version.

Another example for the runtime error 438 would be including the wrong version of Microsoft Data Access Components (MDAC) in the distribution package. For example, a Visual Basic 6 Service Pack 5 program is built on Microsoft Windows 2000 Service Pack 2 by using the latest version of MDAC that is present (version 2.6). When the distribution is built, an earlier version of MDAC (version 2.5) is used (unless MDAC 2.6 is downloaded), causing an error 438 to be thrown when the distributed program is run.

Another scenario would be misspelled method or property names, and this scenario is described in the following Microsoft Knowledge Base article:

This may be as simple as adding a reference for the ActiveX data Access Object that the older Access database would have used created.
For instance if you are now using Excel 2003 as a database with ADO2.8 library and the Access records were created using ADO2.4 (as an example). The differences within the newer library may not be supported.
By adding a reference to the older ADO dll that the Access database used to create the records this may fix the issue.

Kind regards
Steve

[marktheman]

This may be as simple as adding a reference for the ActiveX data Access Object that the older Access database would have used created.
For instance if you are now using Excel 2003 as a database with ADO2.8 library and the Access records were created using ADO2.4 (as an example). The differences within the newer library may not be supported.
By adding a reference to the older ADO dll that the Access database used to create the records this may fix the issue.

This is what seems relevant to me, especially since the access database I'm migrating from is '97 and I'm using excel 2003. Bit of a mystery still thought the problem lies around that:
I can use the select table method and update each field individually on one table converted to worksheet but have to use the direct update statement for each field on another worksheet otherwise it won't work. Very strange since both both tables come from the same source! Anyway thanks for your time and help.