Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
OpenRecordset
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
OpenRecordset
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
OpenRecordset
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/Imp...Excel/427.html I would recommend using ADO over DAO. Tim. "Fred" <leavemealone@home wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
OpenRecordset
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. "TK" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
OpenRecordset
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 "TK" wrote in message ... Hi Fred: |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
OpenRecordset
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. does that look anything like the example! lets get connected then we will work on th rs TK |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
OpenRecordset
"Fred" <leavemealone@home wrote ...
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 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. -- |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
OpenRecordset
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 "TK" wrote in message ... 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. does that look anything like the example! lets get connected then we will work on th rs TK |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
OpenRecordset
Fred,
What do you mean "my database is not Access" ? You first post contained the line: Set dbsNorthwind = wrkJet.OpenDatabase("Northwind.mdb") NickHK "fred" wrote in message ... 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 "TK" wrote in message ... 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. does that look anything like the example! lets get connected then we will work on th rs TK |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Newbie Q: OpenRecordset from Excel | Excel Programming |