MS Access to Excel

tsgdes

New Member
Joined
Aug 8, 2006
Messages
31
Hi,
I'm trying to use DAO in execl to pull some records from a database. I can do this no probs using ADO but when I use DAO (3.6) I get a data mis-match error. The code I'm using is below and the problem occurs at the populate recordset line.

Anybody help?

Thanx

Sub dbquery()

Dim myDatabase As Database
Dim strSQL As String
Dim rstFromQuery As Recordset

'open database
myDatabase = OpenDatabase("C:mydatabase.mdb")

' build query
strSQL = "SELECT * from myTable"

'populate recordset
Set rstFromQuery = myDatabase.OpenRecordset(strSQL, dbOpenSnapshot)

'display records
MsgBox "there are " & rstFromQuery.RecordCount & _
" records that were returned"


rstFromQuery.MoveLast


MsgBox "there are " & rstFromQuery.RecordCount & _
" records that were returned"

rstFromQuery.Close
End Sub
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,918
Office Version
  1. 365
Platform
  1. Windows
Perhaps you need to declare the Recordset/Database as DAO objects?
Code:
Dim myDatabase As DAO.Database
Dim rstFromQuery As DAO.Recordset
By the way if you can do it with ADO why do you want to do it with DAO?
 

tsgdes

New Member
Joined
Aug 8, 2006
Messages
31
has worked now I needed to put the priorty in the references of DAO above ADO. I'm using DAO becuase it easier to move through the recordset

Cheers Des
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,918
Office Version
  1. 365
Platform
  1. Windows
Des

Glad you've got it sorted.:)

But I'm still wondering why DAO instead of ADO, as far as I'm aware don't they have basically the same methods for moving through recordsets?
 

Watch MrExcel Video

Forum statistics

Threads
1,114,052
Messages
5,545,735
Members
410,703
Latest member
yaronjoseph
Top