DAO to ADO Export to Excel

[earthsci]

I am trying to convert some code in DAO to ADO in Access and I am having trouble with creating something similar to the CreateQueryDef command.
Here is the code using DAO:

Sub ExportExcel()
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim sqlstr As String
Dim xlapp As Object

Set dbs = CurrentDb

sqlstr = "SELECT * FROM bids WHERE leasenum = 'G04377'"

Set qdf = dbs.CreateQueryDef("Temp", sqlstr)
Set rst = qdf.OpenRecordset()

If rst.RecordCount > 0 Then
DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel5, "Temp", "c:\DRA1"
Set xlapp = CreateObject("excel.Application&quot
xlapp.Visible = True
xlapp.Workbooks.Open "c:\DRA1"
Else
MsgBox "No records selected", vbInformation, "Export
to Excel"
Exit Sub
End If

DoCmd.DeleteObject acQuery, "Temp"

End Sub

Here is what I have so far using ADO:

Sub ExportExcelADO()
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sqlstr As String

Set cnn = CurrentProject.Connection
sqlstr = "SELECT * FROM bids WHERE leasenum = 'G04377'"
rs.Open sqlstr, cnn
Debug.Print rs.RecordCount

End Sub

Any help or tips greatly appreciated

 

[cmmrfrds]

On the ADO recordset open method there are 5 parameters.

'-this uses the defaults on 3 parameters and the default locktype (4th parm) is read only and will not return a record count. So if you want to use recordcount also use a client side static cursor (3rd parm). The 5th parameter indicates whether the variable (slqstr) is an sql statement, which is the default and will be fine here.

rs.Open sqlstr, cnn
Debug.Print rs.RecordCount

You can iterate through the View Collection in Access to work with stored queries. The View Collection is part of the ADOX library and will need a reference set.

Here is a basic example.
Dim cg As New ADOX.Catalog
Set cg.ActiveConnection = CurrentProject.Connection

Dim v As View
Dim vn As View
'iterate view collection
For Each v In cg.Views
Debug.Print "views = "; v.Name
If v.Name = "query1" Then
Set vn = v
End If
Next
'- can also set to specific view name
Set vn = cg.Views("query1&quot

rs.Open vn.Name, connString, adOpenForwardOnly, adLockReadOnly

Dim fl As ADODB.Field
'- display fields in query
For Each fl In rs.Fields
Debug.Print "Field Name = "; fl.Name
Debug.Print "Field Value = "; fl.Value
Next
Set rs = Nothing

 

[p27br]

Hi

thought this link may be helpful to you

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/daotoado.asp

 

[earthsci]

Thanks for the example and link - I was not to clear on ADOX, but example made sense and I will try it