OpenRecordset

F

Fred

I am trying to get access to an ODBC database but finding it extremely
frustrating.
I am running Excel '97.
I copied an example program from the microsoft help on OpenRecordset. The
example is listed below.
The problem is it does not even compile. It stops on "Set rstTemp2 =
rstTemp.OpenRecordset()" with OpenRecordset highlighted and the message
"Method or data member not found"

Currently I have the following references checked:
Visual Basic for Applications
Microsoft Excel 8.0 Object Library
OLE Automation
Microsoft Forms 2.0 Object Library
Microsoft ActiveX Data Objects 2.5 Library
Microsoft DAO 3.6 Object Library

I have tried adding various other libraries but none seem to get past this
compiler error.
Can someone please tell me what library I am missing if any or is this
example flawed?

Thanks
Fred


Sub OpenRecordsetX()

Dim wrkJet As Workspace
Dim wrkODBC As Workspace
Dim dbsNorthwind As Database
Dim conPubs As Connection
Dim rstTemp As Recordset
Dim rstTemp2 As Recordset

' Open Microsoft Jet and ODBCDirect workspaces, Microsoft
' Jet database, and ODBCDirect connection.
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set wrkODBC = CreateWorkspace("", "admin", "", dbUseODBC)
Set dbsNorthwind = wrkJet.OpenDatabase("Northwind.mdb")

Set conPubs = wrkODBC.OpenConnection("", , , _
"ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers")

' Open five different Recordset objects and display the
' contents of each.

Debug.Print "Opening forward-only-type recordset " & _
"where the source is a QueryDef object..."
Set rstTemp = dbsNorthwind.OpenRecordset( _
"Ten Most Expensive Products", dbOpenForwardOnly)
OpenRecordsetOutput rstTemp

Debug.Print "Opening read-only dynaset-type " & _

"recordset where the source is an SQL statement..."
Set rstTemp = dbsNorthwind.OpenRecordset( _
"SELECT * FROM Employees", dbOpenDynaset, dbReadOnly)
OpenRecordsetOutput rstTemp

' Use the Filter property to retrieve only certain
' records with the next OpenRecordset call.
Debug.Print "Opening recordset from existing " & _
"Recordset object to filter records..."
rstTemp.Filter = "LastName >= 'M'"
Set rstTemp2 = rstTemp.OpenRecordset()

OpenRecordsetOutput rstTemp2

Debug.Print "Opening dynamic-type recordset from " & _
"an ODBC connection..."
Set rstTemp = conPubs.OpenRecordset( _
"SELECT * FROM stores", dbOpenDynamic)
OpenRecordsetOutput rstTemp

' Use the StillExecuting property to determine when the
' Recordset is ready for manipulation.
Debug.Print "Opening snapshot-type recordset based " & _
"on asynchronous query to ODBC connection..."
Set rstTemp = conPubs.OpenRecordset("publishers", _

dbOpenSnapshot, dbRunAsync)
Do While rstTemp.StillExecuting
Debug.Print " [still executing...]"
Loop
OpenRecordsetOutput rstTemp

rstTemp.Close
dbsNorthwind.Close
conPubs.Close
wrkJet.Close
wrkODBC.Close

End Sub

Sub OpenRecordsetOutput(rstOutput As Recordset)

' Enumerate the specified Recordset object.
With rstOutput
Do While Not .EOF
Debug.Print , .Fields(0), .Fields(1)
.MoveNext
Loop
End With

End Sub
 
T

TK

Hi Fred:

Slow down son it's not that tough.

That example is DAO if your just learning ADO is the reconmended
route

copy and paste the following and pay attenshion
to the notes ' / notes

Private Sub CommandButton4_Click()

On Error GoTo ErrHandler

Dim Rg As Range
Set Rg = ThisWorkbook.Worksheets(2).Range("a1")

'//To use ADO objects in an application add a reference
'//to the ADO component. From the VBA window select
'>Tools/References< check the box
' "Microsoft ActiveX Data Objects 2.x Library"

'You should fully quality the path to your file

Dim db_Name As String

'// this is where Access lives on this computer
db_Name = ("C:\Program Files\Microsoft Visual Studio\VB98\NWind.mdb")
'// where does it live on yours (
)

Dim DB_CONNECT_STRING As String

DB_CONNECT_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"data Source=" & db_Name & ";" & ", , , adConnectAsync;"

'Create the connection
Dim cnn As New ADODB.Connection
Set cnn = New Connection
cnn.Open DB_CONNECT_STRING

'Create the recordset
Dim Rs As ADODB.Recordset
Set Rs = New Recordset

'Determines what records to show
Dim strSql As String
strSql = "SELECT CompanyName, ContactName, City, Country " & _
"FROM Customers ORDER BY CompanyName"

'Retreive the records
Rs.CursorLocation = adUseClient
Rs.Open strSql, cnn, adOpenStatic, adLockBatchOptimistic

'Test to see if we are connected and have records
Dim num As Integer
num = Rs.RecordCount

If cnn.State = adStateOpen Then
MsgBox "Welcome to! " & db_Name & " Records = " & num,
vbInformation, _
"Good Luck TK"
Else
MsgBox "Sorry. No Data today."
End If

'Copy recordset to the range
Rs.MoveLast
Rs.MoveFirst
Rg.CopyFromRecordset Rs
Rg.CurrentRegion.Columns.AutoFit

'close connection
cnn.Close
Set cnn = Nothing
Set Rs = Nothing

Exit Sub

ErrHandler:
MsgBox "Sorry, an error occured. " & Err.Description, vbOKOnly
End Sub


Good Luck
TK
 
T

Tim Williams

Fred,

Here's a couple of simple examples using ADO
http://www.vb-helper.com/howto_access_to_excel_2.html

http://www.exceltip.com/show_tip/Im...l_(ADO)_using_VBA_in_Microsoft_Excel/427.html

I would recommend using ADO over DAO.

Tim.




Fred said:
I am trying to get access to an ODBC database but finding it
extremely
frustrating.
I am running Excel '97.
I copied an example program from the microsoft help on
OpenRecordset. The
example is listed below.
The problem is it does not even compile. It stops on "Set rstTemp2 =
rstTemp.OpenRecordset()" with OpenRecordset highlighted and the
message
"Method or data member not found"

Currently I have the following references checked:
Visual Basic for Applications
Microsoft Excel 8.0 Object Library
OLE Automation
Microsoft Forms 2.0 Object Library
Microsoft ActiveX Data Objects 2.5 Library
Microsoft DAO 3.6 Object Library

I have tried adding various other libraries but none seem to get
past this
compiler error.
Can someone please tell me what library I am missing if any or is
this
example flawed?

Thanks
Fred


Sub OpenRecordsetX()

Dim wrkJet As Workspace
Dim wrkODBC As Workspace
Dim dbsNorthwind As Database
Dim conPubs As Connection
Dim rstTemp As Recordset
Dim rstTemp2 As Recordset

' Open Microsoft Jet and ODBCDirect workspaces, Microsoft
' Jet database, and ODBCDirect connection.
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set wrkODBC = CreateWorkspace("", "admin", "", dbUseODBC)
Set dbsNorthwind = wrkJet.OpenDatabase("Northwind.mdb")

Set conPubs = wrkODBC.OpenConnection("", , , _
"ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers")

' Open five different Recordset objects and display the
' contents of each.

Debug.Print "Opening forward-only-type recordset " & _
"where the source is a QueryDef object..."
Set rstTemp = dbsNorthwind.OpenRecordset( _
"Ten Most Expensive Products", dbOpenForwardOnly)
OpenRecordsetOutput rstTemp

Debug.Print "Opening read-only dynaset-type " & _

"recordset where the source is an SQL statement..."
Set rstTemp = dbsNorthwind.OpenRecordset( _
"SELECT * FROM Employees", dbOpenDynaset, dbReadOnly)
OpenRecordsetOutput rstTemp

' Use the Filter property to retrieve only certain
' records with the next OpenRecordset call.
Debug.Print "Opening recordset from existing " & _
"Recordset object to filter records..."
rstTemp.Filter = "LastName >= 'M'"
Set rstTemp2 = rstTemp.OpenRecordset()

OpenRecordsetOutput rstTemp2

Debug.Print "Opening dynamic-type recordset from " & _
"an ODBC connection..."
Set rstTemp = conPubs.OpenRecordset( _
"SELECT * FROM stores", dbOpenDynamic)
OpenRecordsetOutput rstTemp

' Use the StillExecuting property to determine when the
' Recordset is ready for manipulation.
Debug.Print "Opening snapshot-type recordset based " & _
"on asynchronous query to ODBC connection..."
Set rstTemp = conPubs.OpenRecordset("publishers", _

dbOpenSnapshot, dbRunAsync)
Do While rstTemp.StillExecuting
Debug.Print " [still executing...]"
Loop
OpenRecordsetOutput rstTemp

rstTemp.Close
dbsNorthwind.Close
conPubs.Close
wrkJet.Close
wrkODBC.Close

End Sub

Sub OpenRecordsetOutput(rstOutput As Recordset)

' Enumerate the specified Recordset object.
With rstOutput
Do While Not .EOF
Debug.Print , .Fields(0), .Fields(1)
.MoveNext
Loop
End With

End Sub
Click to expand...
 
F

Fred

Thanks for help TK but it doesn't work.
I'm not connecting to a MS Access database.
When using the Data:Get External Data:Create New Query options from the
Excel menu it connects and retreives data fine.
I have recorded a macro when I do this and it is listed below and when I run
it, it works fine.
How can I now get that data into a recordset instead of returning it to the
worksheet?
I tried your routine by setting the DB_CONNECT_STRING to "ODBC;DSN=TIMS.udd"
but it doesn't connect.
 
F

Fred

Thanks for help TK but it doesn't work.
I'm not connecting to a MS Access database.
When using the Data:Get External Data:Create New Query options from the
Excel menu it connects and retreives data fine.
I have recorded a macro when I do this and it is listed below and when I run
it, it works fine.
How can I now get that data into a recordset instead of returning it to the
worksheet?
I tried your routine by setting the DB_CONNECT_STRING to "ODBC;DSN=TIMS.udd"
but it doesn't connect.

Thanks Fred

Sub Macro2()
With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=TIMS.udd;", _
Destination:=Range("A1"))
.Sql = Array("SELECT CLNDR.DATE_, CLNDR.FILLER1 FROM root.CLNDR
CLNDR")
.FieldNames = True
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = True
.SaveData = True
End With
End Sub
 
T

TK

Fred

help me out here

I said

'You should fully quality the path to your file

Dim db_Name As String

'// this is where Access lives on this computer
db_Name = ("C:\Program Files\Microsoft Visual Studio\VB98\NWind.mdb")
'// where does it live on yours ( )
tried your routine by setting the DB_CONNECT_STRING to "ODBC;DSN=TIMS.udd"
but it doesn't connect.
Click to expand...

does that look anything like the example!
lets get connected then we will work on th rs
TK
 
J

Jamie Collins

Fred said:
I copied an example program from the microsoft help on OpenRecordset.
The problem is it does not even compile. It stops on "Set rstTemp2 =
rstTemp.OpenRecordset()" with OpenRecordset highlighted and the message
"Method or data member not found"

Currently I have the following references checked:
Visual Basic for Applications
Microsoft Excel 8.0 Object Library
OLE Automation
Microsoft Forms 2.0 Object Library
Microsoft ActiveX Data Objects 2.5 Library
Microsoft DAO 3.6 Object Library
Click to expand...

In the list, ADO is of higher priority than DAO. Therefore, without
qualifying your objects with their respective class names, the line

Dim rstTemp As Recordset

is assumed to be an ADO recordset which doesn't have an OpenRecordset
method.

Try adding the DAO class name for this and all other relevant lines
e.g.

Dim rstTemp As DAO.Recordset

But TK is right, ADO is a better bet for someone just starting out.

Jamie.

--
 
F

fred

TK, my database is not Access and is not a single file. In fact it is a
client server application (U/SQL) that has a client ODBC driver. So you see
I cannot fully qualify the path because there is no path as such.

Obviously it works from within the Microsoft Query with just the string
"ODBC;DSN=TIMS.udd" so I hope I can connect a Recordset using this same
information?

Regards,
Fred
 
N

NickHK

Fred,
What do you mean "my database is not Access" ? You first post contained the
line:
Set dbsNorthwind = wrkJet.OpenDatabase("Northwind.mdb")

NickHK
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel ODBCDIRECT 1
Why doesn't this code from the Access Help menu work?? 1
ADO Recordset Issues on Open Method 0
DAO connection problem 0
insert data ODBC 3
Problem creating PivotTable from ADO recordset using VBA 0
ADO vs DAO How to get the dialog to add/select ODBC DSN Connect in 1
ADO vs DAO in Access 2007 8

Top