RecordSets?

[MikeB]

In the online Help for Visual Basic, there is the following example
(I've removed some of the code for brevity in posting here, but not in
the example I'm running).

Sub FieldX()

Dim dbsNorthwind As Database
Dim rstEmployees As Recordset
' removed some Dim statements
Dim fldIndex As Field
Dim prpLoop As Property

Set dbsNorthwind = OpenDatabase("Northwind.mdb")
Set rstEmployees = _
dbsNorthwind.OpenRecordset("Employees")

' removed some Assign statements
Set fldIndex = _
dbsNorthwind.TableDefs(0).Indexes(0).Fields(0)

' Print report.
' removed some output calls.
FieldOutput "Index", fldIndex

rstEmployees.Close
dbsNorthwind.Close

End Sub

I can't run this code, since I don't have the NorthWind database. So I
try to change the code as follows and get an error


Sub FieldX()

Dim dbsNorthwind As Database
Dim rstEmployees As Recordset

Dim fldIndex As Field
Dim prpLoop As Property

Set dbsNorthwind = OpenDatabase("C:\Documents and Settings\Mike\My
Documents\My MS Access Projects\FMEChessClub.mdb")
Set rstEmployees = _
dbsNorthwind.OpenRecordset("Players") '<== I get an error
here

' Assign a Field object from different Fields
' collections to object variables.

Set fldIndex = _
dbsNorthwind.TableDefs(0).Indexes(0).Fields(0)

' Print report.
FieldOutput "Index", fldIndex

rstEmployees.Close
dbsNorthwind.Close

End Sub

The error I get says: Runtime Error '13' Type mismatch. Now I believe
that the database open call worked, since I can execute the Name
method on dbsNorthWind. Perhaps I'm mistaken?

 

[Klatuu]

Northwind is shipped with all Access versions. Either you just haven't
found it or whoever installed your Access did not do a complete install.

Assuming a typical install of 2003, you might look here:

C:\Program Files\Microsoft Office\OFFICE11\SAMPLES

 

[Douglas J. Steele]

Unfortunately, the code in the Help file is often incomplete...

The reason you're getting the Error 13 is because newer versions of Access
have references set to both DAO (Data Access Objects) and ADO (ActiveX Data
Objects), both of which have a Recordset object in their models.
Unfortunately, the reference to ADO is usually higher than the one for DAO,
so declaring a variable as

Dim rstEmployess As Recordset

generaelly results in an ADO recordset. Unfortunately, you need a DAO
recordset for that code to work.

Change the declarations to:

Dim dbsNorthwind As DAO.Database
Dim rstEmployees As DAO.Recordset
Dim fldIndex As DAO.Field
Dim prpLoop As DAO.Property

Strictly speaking the "DAO.Database" isn't required, because ADO doesn't
have a Database object in it, but there's no harm in being specific.

 

[David W. Fenton]

Dim dbsNorthwind As DAO.Database

Strictly speaking the "DAO.Database" isn't required, because ADO
doesn't have a Database object in it, but there's no harm in being
specific.

I think it's a good idea for the *coder*, even though it does
nothing to make the code more correct. The reason it's good for the
coder is that it documents the parent of that object.

 

[Douglas J. Steele]

I think it's a good idea for the *coder*, even though it does
nothing to make the code more correct. The reason it's good for the
coder is that it documents the parent of that object.

I believe it can actually help when there are References problems with the
application as well. Since Access doesn't have to search through all the
references, it won't stop if one of the other references is broken. Of
course, that's usually not much consolation, since other parts of the
application will still fail to work!

 

[MikeB]

Northwind is shipped with all Access versions. Either you just haven't
found it or whoever installed your Access did not do a complete install.

That would be me.

Assuming a typical install of 2003, you might look here:

I have the older (I think 2002 or XP) installed on this machine

C:\Program Files\Microsoft Office\OFFICE11\SAMPLES

I looked, and didn't find it there, so I did a search and found two
copies of it here:

C:\Documents and Settings\Mike\My Documents\Temp\4650\XP-formatted MDB
files

C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\QuickStart\aspnet
\samples\data\App_Data

I guess those are good to play with, so thanks for the tip.

 

[MikeB]

Unfortunately, the code in the Help file is often incomplete...

The reason you're getting the Error 13 is because newer versions of Access
have references set to both DAO (Data Access Objects) and ADO (ActiveX Data
Objects), both of which have a Recordset object in their models.
Unfortunately, the reference to ADO is usually higher than the one for DAO,
so declaring a variable as

Dim rstEmployess As Recordset

generaelly results in an ADO recordset. Unfortunately, you need a DAO
recordset for that code to work.

Change the declarations to:

Dim dbsNorthwind As DAO.Database
Dim rstEmployees As DAO.Recordset
Dim fldIndex As DAO.Field
Dim prpLoop As DAO.Property

Strictly speaking the "DAO.Database" isn't required, because ADO doesn't
have a Database object in it, but there's no harm in being specific.

Rats, thanks. I *did* have to do that for one of the other references,
I just didn't think about doing it here, brain freeze, I guess.