Set rst = db.OpenRecordset issue with Access 2007 (1 Viewer)

Dear all,

I have a wierd problem. I developed an App in 2003 and converted to 2007. when I try to open a form, I get a Type Mismatch error. I debugged and found it was on the following statement;

Set rst = db.OpenRecordset("18_SvrTns")

"18_SvrTns" is my server transaction table. can you not use this code in 2007 since the app works perfectly in 2003?

The preceeding code is:

Private Sub S_CpStat_GotFocus()
On Error GoTo Err_Reorder_Message
' CPU Cores Reorder Message
Dim db As Database
Dim rst As Recordset
Dim T_CoreTot As Double
Dim T_CoreCalc As Double
Dim T_CoreRe As Double
Dim T_MemTot As Double
Dim T_MemCalc As Double
Dim T_MemRe As Double



' Check Transactoin File and Add up all related Records to get the utilization
Set db = CurrentDb()
Set rst = db.OpenRecordset("18_SvrTns")
If Not rst.EOF Then
rst.MoveFirst ' Set Cursor to 1st record
Me.SubCoreTot = 0
Me.SubMemTot = 0
End If
Do While Not rst.EOF
If [Forms]![Frm_SvrInq].[Form]![S_SerialNum] = rst![S_SerialNum] Then
Me.SubCoreTot = Me.SubCoreTot + rst![L_CoreAlloc]
Me.SubMemTot = Me.SubMemTot + rst![S_MemAlloc]
End If
rst.MoveNext
Loop

There shouldn't be a problem using the recordset as 2007, though the fact that name starts with a number concerns me somewhat. (Just to rule it out, omit the "18_" and see if it changes anything. I doubt it would but I need to be certain.)

What's your "18_SvrTns"'s SQL?

Thanks Banana

18_SvrTns is the name of my Transaction Table. In hindsight I should probably not have started it with a number. Unfortunately all my tables begin with numbers. This means I have to make major changes to rename everything from Queries, to Forms to modules. Are you certain this is the issue? It works find in Access 97 and 2003.

Thanks again.

Only test it once by renaming only to verify that it's not the cause of the problem because I don't know for sure. If it still pops up same errors, then we can rule out the name as a cause, which is the point.


So 18_SvrTns is a table, not a query? Is it linked? Can you open it directly?

One more possibility we should try and rule out:

change the line


to


and report if it helped.

Hi Banana,

I just copied the Table, renamed it and tried and I still get the same error. The Table is not linked and I am able to open it directly. I have other forms referencing the table with no issue, but this is the only one that uses the particular failing code. How can I double check that there are no links on this table just in case I inadvertently created one?

Thanks

Banana,

You are the Top Banana, the King of all Banana's.

Dim rst As DAO.Recordset

Worked a dream. Thanks a lot. You just saved me hours of searching...

Well, that was not my expected solution, but I guess it was.


Here's what happened.

In various Access versions, Microsoft went forth and back between two libraries, Data Access Objects (DAO) and ActiveX Data Object (ADO). It happens that both are very similar as they have same function (e.g. providing high-level access to data sources) and of course, they both share same objects (e.g. Recordsets) which are same in name only (e.g. you can't set a DAO recordset to a ADO recordset).

Your code, "CurrentDb.OpenRecordset(...)" assumes that the rst variable it's being set to is a DAO recordset. However, if it's ADO recordset, you get that error.

What puzzles me is that 2003 had DAO as the default, and as did the 2007. To find out, you would go to VBA Editor, click Tools -> References.

You should see some checked references, something like this:

Visual Basic For Applications
Microsoft Access Object Library
OLE Automation
Data Access Object
ActiveX Data Object

Click to expand...

If it happened that your "ActiveX Data Object" was higher in the list than "Data Access Object", then whenever your code calls for a recordset, it assumes ADO recordset first.

This is why we have to disambiguate to explicitly specify we want so and so recordset, which is what we did with this:

In some of my projects, I use both libraries (as they have their separate uses), so I disambiguate all of my references to either libraries so I can flip-flop between a ADO recordset or DAO recordset as the situation deems it appropriate.

Hope that helps.

Thanks for the clear explanation. That was one of the first places I went to look. What I didnt know as you explain is that if the version of the ActiveX Data Object is higher than the Data Access Object it would take precedence. Well we certainly live and learn. I guess I will pay more attention to this going forward.

I am an RPG programmers by birth just trying to get down with the OO programming.. in my days we wrote everything ourselves. Sometimes the whole concept of Object Libraries confuses the hell out of me. The closest I have come to this sort of programming is using RPG ILE which is some kind of OO programming on Acid... I guess one day I will become a real .NET programmer

Cheers

No problem. We all learn.

Not to sound nitpicky, but just want to be 100% sure we're clear-

It's not the version that affect precedence, only the position in the reference list so in the list I shown above, DAO had precedence over to ADO but yields to three other libraries, especially if those three libraries had objects that shared same name (I don't think they do but I can't think of one off the top of my head).

Also, VBA is not strictly a OO programming. Some has described it as "Object-based" language, so we're missing out on more lofty concepts of programming such as polymorphism, inheritance, and reflection. (Well, some would argue that VBA does support such in different manner, but even so it's quite not full-on OOP compared to say, .NET programming).

Not that this is bad thing; not having to worry about such things frees us to concentrate on high-level concepts and getting the application up and running fast as possible, which is the whole point of Access, as opposed to doing it in a IDE such as Visual Studio where you would have to literally build from scratch (well, not exactly true as some will re-use various libraries and snippets for common tasks but the plumbing job is still there) while Access already does the 70% and it's up to us to provide the other 30% so to speak.

(I have to admit, it's weird as I generally believe that Access should move forward onto .NET soon as possible, but there's reason for everything. )

Thanks for the clarification.

Best wishes and I am sure you will be hearing from me in the not too distant future