Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Accessing Access Table and Copying to Excel

Posted on 2010-01-04
16
Medium Priority
?
421 Views
Last Modified: 2012-05-08
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
Comment
Question by:BBlu
  • 7
  • 5
  • 2
  • +1
16 Comments
 
LVL 6

Expert Comment

by:TomSchreiner
ID: 26177903
Place this after your recordset has its data.
    Range("A1").CopyFromRecordset (rs)
    rs.Close
0
 

Author Comment

by:BBlu
ID: 26177942
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
 
LVL 6

Expert Comment

by:TomSchreiner
ID: 26177956
Yes.  Are you running this procedure from Excel?  
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:BBlu
ID: 26177958
yes.
0
 

Author Comment

by:BBlu
ID: 26177975
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
 
LVL 6

Expert Comment

by:TomSchreiner
ID: 26177993
Get rid of this:
Set dbconn = Workbooks.OpenDatabase("c:\Test.accdb")
And Replace it with this:
Workbooks.Add
0
 
LVL 6

Accepted Solution

by:
TomSchreiner earned 880 total points
ID: 26178002
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
 

Author Comment

by:BBlu
ID: 26178004
hmm.. I get the same error
0
 

Author Comment

by:BBlu
ID: 26178018
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
 
LVL 6

Expert Comment

by:TomSchreiner
ID: 26178049
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 26178550
(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
 
LVL 9

Expert Comment

by:hitsdoshi1
ID: 26183641
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
 

Author Comment

by:BBlu
ID: 26183651
Does yours still open a new workbook?
0
 
LVL 9

Assisted Solution

by:hitsdoshi1
hitsdoshi1 earned 120 total points
ID: 26183722
Yes, it does...but changed it to Worksheets.Add (instead of workbooks.add), so its just adds new worksheet to my current file...
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 26184616
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
 

Author Closing Comment

by:BBlu
ID: 31672786
Thanks to all of you
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Implementing simple internal controls in the Microsoft Access application.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question