• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 423
  • Last Modified:

Accessing Access Table and Copying to Excel

I am a novice and got some help with code that accesses and copies data from an Access Database/Table.  The code (attached) opens a new Excel workbook before copying.  Can someone tell me why and how to make the code copy to the current worksheet?
Sub GatherDBData()
Set dbconn = Workbooks.OpenDatabase("c:\Test.accdb")
    
    
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    
    Set cn = New ADODB.Connection
    
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=c:\Test.accdb;Persist Security Info=False;Jet OLEDB:Database Password="
    
    Set rs = New ADODB.Recordset
    
    rs.Open "Select * from [users]", cn
    While Not rs.EOF
        fname = rs.Fields("Fname")
        lname = rs.Fields("Lname")
        rs.MoveNext
    Wend

End Sub

Open in new window

0
BBlu
Asked:
BBlu
  • 7
  • 5
  • 2
  • +1
2 Solutions
 
TomSchreinerCommented:
Place this after your recordset has its data.
    Range("A1").CopyFromRecordset (rs)
    rs.Close
0
 
BBluAuthor Commented:
at the end?

Set rs = New ADODB.Recordset
   
    rs.Open "Select * from [users]", cn
    While Not rs.EOF
        fname = rs.Fields("Fname")
        lname = rs.Fields("Lname")
        rs.MoveNext
    Wend

Range("A1").CopyFromRecordset (rs)
   rs.Close
0
 
TomSchreinerCommented:
Yes.  Are you running this procedure from Excel?  
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
BBluAuthor Commented:
yes.
0
 
BBluAuthor Commented:
I get the following error:

Run-Time Error '430'

Class does not support Automation or does not support expected interface.

But it still opened and copied the data to a new workbook
0
 
TomSchreinerCommented:
Get rid of this:
Set dbconn = Workbooks.OpenDatabase("c:\Test.accdb")
And Replace it with this:
Workbooks.Add
0
 
TomSchreinerCommented:
This code should not error out on you...

Sub GatherDBData()
     
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
     
    Set cn = New ADODB.Connection
     
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=c:\Test.accdb;Persist Security Info=False;Jet OLEDB:Database Password="
     
    Set rs = New ADODB.Recordset
     
    rs.Open "Select * from [users]", cn
    While Not rs.EOF
        fname = rs.Fields("Fname")
        lname = rs.Fields("Lname")
        rs.MoveNext
    Wend
    
    Workbooks.Add
    Range("A1").CopyFromRecordset (rs)
    rs.Close
 
End Sub

Open in new window

0
 
BBluAuthor Commented:
hmm.. I get the same error
0
 
BBluAuthor Commented:
Thanks for your help, Tom.  I'm going to log off for the night now..but will check back tomorrow.  I'm a novice and trying to learn a lot of this stuff on my own.  This forum has been invaluable.
0
 
TomSchreinerCommented:
Ok.
fname = rs.Fields("Fname")
 lname = rs.Fields("Lname")
 
Assigning  multiple values to the same variable?  I don't understand this.  I wil or someone else will help you clear things up when you get back.  Have a good one!
0
 
Rory ArchibaldCommented:
(Not for points, since Tom did the hard yards!)

In addition to removing the loop, there should not be parentheses round the rs in the copyfromrecordset line:


Sub GatherDBData()
     
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
     
    Set cn = New ADODB.Connection
     
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=c:\Test.accdb;Persist Security Info=False;Jet OLEDB:Database Password="
     
    Set rs = New ADODB.Recordset
     
    rs.Open "Select * from [users]", cn
    
    Workbooks.Add
    Range("A1").CopyFromRecordset rs
    rs.Close
 
End Sub

Open in new window

0
 
hitsdoshi1Commented:
Nice and neat code....I was looking something like this as well......is there anyway I can get Field Name as column header as well ?

Thank you.
0
 
BBluAuthor Commented:
Does yours still open a new workbook?
0
 
hitsdoshi1Commented:
Yes, it does...but changed it to Worksheets.Add (instead of workbooks.add), so its just adds new worksheet to my current file...
0
 
Rory ArchibaldCommented:
To get the field names, use something like this:
 

Sub GatherDBData() 
      
    Dim cn As ADODB.Connection 
    Dim rs As ADODB.Recordset 
      
    Set cn = New ADODB.Connection 
      
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=c:\Test.accdb;Persist Security Info=False;Jet OLEDB:Database Password=" 
      
    Set rs = New ADODB.Recordset 
      
    With rs
       .Open "Select * from [users]", cn 
       If Not (.EOF And .BOF) Then
         'Populate field names
         For i = 1 To .Fields.Count
            Cells(1, i) = .Fields(i - 1).Name
         Next i
         ' Copy data to A2
         Cells(2, 1).CopyFromRecordset rs
      End If
      .Close
   End With
  
End Sub

Open in new window

0
 
BBluAuthor Commented:
Thanks to all of you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 7
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now