Use DAO to run a query in Access and return results to excel 1

[xlbo]

Well, I reckon I've got it about covered in the title. This has to use DAO I'm afraid and I've not used it much

I have an Access database on D:\Home\Access
called GB_Universe

I have a select query on that database called Q3
This is based on a query (Q2) which in turn is based on a query (Q1) which is based on several tables

What I need to do is connect to the database and run Q3 (I would assume I can use the DoCmd.openquery "Q3" syntax that is native to Access ?
I then need to return the results of Q3 into an excel worksheet (wkbk is called "Master.xls" and wksht is called "DataReturn&quot starting in A1

TIA Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[SkipVought]

Geoff,
I've got an appointment this AM, so it you don't have an answer by the time I return, I'll get you one.

Use DAO to access Access all the time.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[xlbo]

You beaut !
cheers Skip Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[SkipVought]

Geoff,
Here's a snippet I extracted from one of my projects...

Sub GetJobList()
    Dim dbs As Database, rst As Recordset, wks As Workspace, ws As Worksheet
    Dim sQuery As String
    
    On Error Resume Next
    
    Set wks = DBEngine.CreateWorkspace("", "admin", "", dbUseJet)
' JobList is a stored query in Access
    sQuery = "Select * From JobList"
    
    Set dbs = wks.OpenDatabase("C:\My Documents\vbaProjects\snkPlumbing\TEST\snkDatabaseTEST.mdb", True)

    Set rst = dbs.OpenRecordset(sQuery)
    If Err.Number < 0 Then GoTo ErrorExit
        Set ws = Worksheets(&quot;qrJobList&quot;)
        With ws
            .Activate
            .Range(&quot;qrJobList&quot;).CurrentRegion.Clear
    
            For iCol = 1 To rst.Fields.Count
                .Cells(1, iCol).Value = rst.Fields(iCol - 1).Name
            Next
            
            .Cells(1, 1).CurrentRegion.Font.Bold = True
            .Cells(2, 1).CopyFromRecordset rst
        End With
    End If
    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing
    Set wks = Nothing
    Set ws = Nothing
    Exit Sub
ErrorExit:
' error trapping...
End Sub

You can treat a stored query like any other table as far as SQL code goes. Hence,

"Select * From

TheQueryOfYourChoice"

Let me know if there's anything else you need. Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[xlbo]

Skip - superb - does exactly what I needed it to and I'll be keeping this for reference
Have a star Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[xlbo]

Skip - a folowup question if I may

After running the codee (which works fine) access doesn't seem to be released until the workbook with the code has been closed ie can't close it manually - just minimises to the taskbar. Have tried adding a wks.close line but this seems to make no difference. I have little experience of DAO so I was wondering if this was normal ?? Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[SkipVought]

What resources are not released?

How are you determining that resources are not released?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[xlbo]

It's just the access shell as far as I can tell - like when you open up access without opening a database - just sits there and can't close it with the x. Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[SkipVought]

Geez, I have not experienced that. Of course, operationally, Access never opens when accessing Access from Excel. I can't tell what's happnin. I can open and shut the shell at will. You might try reinstalling. Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[xlbo]

Weird - I definately see an Access "shell" appear but no db opens up inside it
will go and see what line causes it.... Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[xlbo]

Hmmm - gonna have to put this down to MS / NT randomness. Today, when I run the code, I don't see Access at all.
Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde