Error with code 2

[gj0519]

Need some assistance with my code below. All I am trying to do is export data from a table into excel. I receive a type mismatch error that I can't figure out on this line of code.
Any ideas?
Thanks,
GJ

Set rs = db.OpenRecordset(strSh, dbOpenSnapshot)

Function Export()

    Dim xlobj As Object
    Dim objWKB As Object, objSHT As Object
    Dim rs As Recordset
    Dim db As Database
    Dim strSh As String
    
    Const conSHEET1 = "SHARES"
    
    Const conBK = "C:\Documents and Settings\Workstation\Desktop\Shares.xls"
    Set xlobj = CreateObject("excel.application")
    Set db = CurrentDb
    xlobj.Application.Visible = True
    With xlobj
       .workbooks.Open filename:=conBK
    
    strSh = ("SELECT D1NAME, MEMBER_NBR FROM tblShares")
    Set rs = db.OpenRecordset(strSh, dbOpenSnapshot)
    Set objSHT = .worksheets(conSHEET1)
    With objSHT
       .rnge("A1:H65000").copyRecordset rs
    End With
    .activeworkbook.Close savechanges:=True
    xlobj.Quit
    
    End With
    Set xlobj = Nothing
          
    
End Function

 

[MajP]

Always explicitly identify

dim rs as dao.recordset
or
dim rs as adodb.recordset

make sure your have proper references to DAO in your case.

 

[gj0519]

That is what the issue was. I also had to change one other part of my code that was spelled wrong, otherwise it works.

Thanks,

-GJ

 

[MajP]

This problem happens all the time on this site. Prior to A2k the default reference was DAO, in A2k it then was ADO, and in A2003 it is both. But if you do not explicitly identify this is what happens. It looks through the list until it finds a library, and it does this from the top down. So even if you have both references it stops when it finds the library with a recordset object. So if you have ADO listed first that is what the variable ends up being declared.
This is one of my gripes with VB. There are so many duplicate objects with the same name not just in the DAO and ADO libraries. Why could they not just name it an "ADORecordset and a DAORecordset"?
Another important thing is that a form/report recordset property or recordsetclone is always a DAO rs. So
Me.recordset
or
me.recordsetclone
always returns a DAO recordset
And in your case
currentdb.openrecordset
always returns DAO since it is a DAO method.
Oh yeah here is another good example, that you should also fix.
dim db as DAO.database
or
dim db as ADODB.database

 

[1DMF]

Sorry if I sound a bit dim, what is DAO vs ADO
What difference is there between the two, why are there two types of record set.

ADO - w3c says "The ADO Command object is used to execute a single query against a database. The query can perform actions like creating, adding, retrieving, deleting or updating records."

DAO - webopedia says "(1) Short for data access objects, objects that work with the Jet database engine. DAO objects are generally created with Visual Basic. Once created, a DAO object can be accessed and manipulated by any application that can use the Jet engine. This includes all of the applications in Microsoft Office, such as MS-Word, MS-Access, and Excel. "

but SUN says "Core J2EE Patterns - Data Access Object"

So is DAO specific to MS products or not and what's the difference between the two?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[MajP]

These are two VB object models that:

provide a framework for using code to create and manipulate components of your database system. Objects and collections have properties that describe the characteristics of database components and methods that you use to manipulate them. Together these objects and collections form a hierarchical model of your database structure, which you can control programmatically

DAO (Data Access Object) was the original model up until, A2K, then came ADO (Active X Data Object). The thought was that ADO would replace DAO, but there are still things that work well in DAO that do not work as well in ADO. It appears that DAO will not go away, but ADO has more functionality and will get increased functionality. ADO can work with more external databases then DAO can.

 

[1DMF]

so they are both object layers used to give connectivity to your data and has an API for manipulating the data.

MS has developed ADO based on their Active-X component, where as DAO is a standard that is used cross-platfrom, by various vendors.

Is that what your saying?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[MajP]

I am more a user, so I know how to use these objects but do not really understand the differences. ADO is a HUGE topic, but I believe that the big difference is that older libraries such as RDO and DAO focused on retrieving data from databases containing sets of records in tables. ADO is part of this larger Universal Data Access (UDA)

DAO began as an interface to the jet database engine and grew in complexity through A97. Although DAO is still supported, the new way to access data is with ADO. ADO is part of Microsoft's Universal Data Access strategy, which has the basic premise of being able to access any kind of data wherever it may exist, whether in a database, a directory structure, or some custom type of data storage

 

[1DMF]

thanx for the informative overview. I thought MS's Universal Data Access was ODBC, i get confused with all these anocronyms.

I guess I'll just learn which ever one as needed, i take it , it doesn't matter which one you use as long as it works for the application you are using it for.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[PHV]

Simple rule:
use DAO for any Jet or ISAM database
use ADODB for any other database

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886