current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I'm trying to query a MS access (2007-2010) database using microsoft excel and print the qeury to my spreadsheet.

The below code prints only the field header into a specified cell and none of the other data in the selected field. Where am I going wrong? hints etc welcome.

     Option Explicit

    ' Add reference to Microsoft ActiveX Data Objects Lib

    Public Sub main(): On Error GoTo Err_handler

        Dim cn As ADODB.Connection
        Set cn = New ADODB.Connection


    'open DB connection

        cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Databaselocation
        cn.Open

        Dim rs As ADODB.Recordset
        Set rs = New ADODB.Recordset

      'Query Stuff

        rs.ActiveConnection = cn
        rs.Open "SQL Query here;"



    'does something
        Dim fld As ADODB.Field
        Dim rng As Range
        Set rng = [a2]

        For Each fld In rs.Fields
            rng.Value = fld.Name
            Set rng = rng.Offset(0, 2)
        Next fld

        Set rng = rng.Offset(2, -rs.Fields.Count)
        rng.CopyFromRecordset rs



    ' closes db connection

        rs.Close
        cn.Close
        Set rs = Nothing
        Set cn = Nothing

        Exit Sub

        Err_handler:
            MsgBox Err.Description

        End Sub
share|improve this question
    
First comment: in the line Set rng = rng.Offset(0, 2) do you mean to skip right by two columns every time you print a value? Second: CopyFromRecordset "might" only work if the worksheet is active - what happens if you add a WorkSheet.Activate line? Of course I am assuming you have confirmed that your query returns valid data... –  Floris Feb 26 '13 at 22:59
    
Yes, the query returns valid data in MS access. It returns one column with the field name and one row of data. The current code prints the fieldname to the specified cell (A2). When i set the offset to (0,0) it errors out. I want it to just print the column from the query. –  user2022302 Feb 26 '13 at 23:07
add comment

2 Answers

up vote 0 down vote accepted

You want to skip over one column at a time when displaying column names, not two, because the rng.CopyFromRecordset method will output the recordset with no gaps.

Set rng = rng.Offset(0, 1)

I tested your code and it does work with that change.

share|improve this answer
    
With your changes it still acts as previously described. it only prints column names. Are you saying you can get it to print values in the columns as well? Not just the column names. –  user2022302 Feb 27 '13 at 15:42
    
Yes, I get the data under the column names. –  Dan Metheus Feb 27 '13 at 15:46
    
Tha'ts frustrating, i'll try some different queries and mabye paste it into a new spreadsheet. If you dont mind me asking, Which references do you have enabled in VBA? –  user2022302 Feb 27 '13 at 16:50
    
I have the regular 4 + Microsoft ActiveX Data Objects 6.1 Library, but it should work on any version of the ADO. –  Dan Metheus Feb 28 '13 at 17:19
    
I've isolated the issue, it doesn't recognize (or print?) "where" statments. Any query that doesn't use a where statement works flawlessly. Any ideas? –  user2022302 Feb 28 '13 at 18:27
show 1 more comment
up vote 0 down vote

You must loop also in Rows in your rs (ADODB.Recordset) object. Use MoveNext method to move to next row.

share|improve this answer
    
Using this link msdn.microsoft.com/en-us/library/office/… I placed a rs.movenext line of code after "rng.CopyFromRecordset rs". it didn't work, am I interpreting you/ the article wrong? –  user2022302 Feb 26 '13 at 23:14
    
From msdn.microsoft.com/en-us/library/office/ff839240.aspx : "Copies the contents of an ADO or DAO Recordset object onto a worksheet, beginning at the upper-left corner of the specified range. If the Recordset object contains fields with OLE objects in them, this method fails" Could that be the problem? What data is being returned? –  Floris Feb 26 '13 at 23:21
    
Possibly, The data being returned is the column title, or field name. This current codeprints to the specfied cell as expected but I need the data from the rows not the title :) Would i need a different parsing method altogether? –  user2022302 Feb 26 '13 at 23:24
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.