Results 1 to 10 of 10
  1. #1
    rmoreno is offline Advanced Beginner
    Windows XP Access 2000
    Join Date
    Apr 2013
    Posts
    51

    Copy Access Query Data to Specific Excel Worksheet


    OK. I have some test code that can collect Access query data and copy the data into an Excel spreadsheet.
    I have one problem though, the Excel VBA code will not work if the Access database is open.

    I get the following error message:
    Run-Time Error '-2147467259 (80004005)'
    Could not use "; file already in use."

    What do I need to change in my code to allow me to collect my Access query data even if the database is open?

    Code:
         Sub GetAccessData_Test() 
         ' Sample demonstrating how to return a recordset from an Access db
         ' late bound so does not require a reference to the Microsoft ActiveX Data Objects Library.
         
        Dim wks As Worksheet 
        Dim cnn As Object, strQuery As String, rst As Object 
        Dim strPathToDB As String, strFormula As String, i As Long 
         
         
         ' output to specific worksheet
        Set wks = Sheets("Access2Excel_Test") 
        wks.Range("A5:M60000").ClearContents 
         
         ' Path to database
        strPathToDB = "c:\Data\Access2Excel_Test.mdb" 
         
        Set cnn = CreateObject("ADODB.Connection") 
         ' open connection to database
        With cnn 
            .ConnectionTimeout = 500 
            .Provider = "Microsoft.Jet.OLEDB.4.0" 
            .ConnectionString = "Data Source=" & strPathToDB & ";" 
            .Open 
            .CommandTimeout = 500 
        End With 
         
         ' SQL query string - change to suit
        strQuery = "qry_Access2Excel_TestData" 
         
         ' create new recordset
        Set rst = CreateObject("ADODB.Recordset") 
         
         ' open recordset using query string and connection
        With rst 
            .Open strQuery, cnn 
             ' check for records returned
            If Not (.EOF And .BOF) Then 
                 
                 ' Copy data starting from A5
                wks.Cells(5, 1).CopyFromRecordset rst 
                 
            End If 
            .Close 
        End With 
         ' clean up
        Set rst = Nothing 
        cnn.Close 
        Set cnn = Nothing 
         
    End Sub
    

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I know this is possible because I do it. Here is excerpt from my procedure, uses early binding and ADODB, requires VBA reference to Microsoft ActiveX Data Objects 2.8 Library:

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    cn.Open gconConnection
    rs.Open "SELECT * FROM tablename;", cn, adOpenStatic, adLockReadOnly

    gconConnection is global constant declared and set in a general module:
    Public Const gconConnection = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source='\\servername\path\databasename.accdb'"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    rmoreno is offline Advanced Beginner
    Windows XP Access 2000
    Join Date
    Apr 2013
    Posts
    51
    Thanks for responding to my thread. I was beginning to think no one would respond.
    I entered your code, but when I tried to run it I got an error:
    Compile error: User-defined type not defined
    on the "Dim cn As ADODB.Connection". What am I doing wrong?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Did you set the VBA reference noted in my post? Maybe I added that after you first read post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    rmoreno is offline Advanced Beginner
    Windows XP Access 2000
    Join Date
    Apr 2013
    Posts
    51
    Are you referencing the gconConnection statement. If so I didn't understand what I was to do with it. I'm a bit of a novice coder, could you expain it to me please?

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    No, not referencing the gconConnection code. You don't have to use a constant to hold the connection string value. You can use a variable within your GetAccessData_Test procedure

    With my code structure, need to from the VBA editor menu:

    Tools > References > scroll down until you find the library I noted in earlier post and check it
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    rmoreno is offline Advanced Beginner
    Windows XP Access 2000
    Join Date
    Apr 2013
    Posts
    51
    OK. Done. Now the error I'm getting is this:
    Run-Time error - '[Microsoft][ODBC Drive Manager]data source name not found and no default driver specified'

    What does this mean?

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I just noticed you are using Access 2000.

    My code: Provider=Microsoft.ACE.OLEDB.12.0
    is for Access 2007/2010

    Your original code shows 4.0. Is that what you are still referencing?

    Make sure you have mdb in the file name instead of accdb from my example.

    Post your exact revised code for analysis.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    rmoreno is offline Advanced Beginner
    Windows XP Access 2000
    Join Date
    Apr 2013
    Posts
    51
    Here is the code that you requested.
    Code:
    Public Const gconConnection = "Provider=Microsoft.JET.OLEDB.4.0; Data Source='c:\Data\Access2Excel_Test.mdb'"
    
    Sub GetAccessData_WPDestructive_Test2()
        ' Sample demonstrating how to return a recordset from an Access db
        ' late bound so does not require a reference to the Microsoft ActiveX Data Objects Library.
        
        Dim wks As Worksheet
        Dim strPathToDB As String, strFormula As String, i As Long
        
        Dim cn As ADODB.Connection
        Dim rs As ADODB.Recordset
        Set rs = New ADODB.Recordset
        
        ' output to specific worksheet
        Set wks = Sheets("Access2Excel_Test")
        wks.Range("A5:M60000").ClearContents
       
        ' Path to database
        strPathToDB = "c:\Data\Access2Excel_Test.mdb"
              
        Set cn = New ADODB.Connection
        
        ' SQL query string - change to suit
        strQuery = "qry_Access2Excel_TestData"
       
        ' create new recordset
        Set rs = New ADODB.Recordset
        
        cn.Open gconConnection
        rs.Open "SELECT * FROM strPathToDB;", cn, adOpenStatic, adLockReadOnly
        
        ' open recordset using query string and connection
        With rs
            .Open strQuery
            ' check for records returned
            If Not (.EOF And .BOF) Then
                   ' Copy data starting from A5
                wks.Cells(5, 1).CopyFromRecordset rs
       
         End If
            .Close
        End With
        ' clean up
        Set rst = Nothing
        cnn.Close
        Set cnn = Nothing
       End Sub

  10. #10
    rmoreno is offline Advanced Beginner
    Windows XP Access 2000
    Join Date
    Apr 2013
    Posts
    51
    After working with the above code this is the simplified code I got. Don't know why, but this version of the code seems to work with the database open or closed.

    [CODE]
    Sub GetAccessData_4Test()

    Dim con As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim wks As Worksheet

    'open connection to database
    con.ConnectionString = "\\c:\Data\Access2Excel.mdb"
    con.Provider = "Microsoft.Jet.OLEDB.4.0"
    con.Open

    'open recordset
    rst.ActiveConnection = con

    'open target query
    rst.Open "qry_Access2Excel_TestData"

    'set/clear/output to target worksheet
    Set wks = Sheets("Access2Excel_Test")
    wks.Range("A5:M60000").ClearContents
    wks.Range("A5").CopyFromRecordset rst
    Range("A4").Select

    'clean up
    Set rst = Nothing
    con.Close
    Set con = Nothing

    End Sub

    [\CODE]

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Open specific worksheet from Access VBA
    By SltPhx in forum Programming
    Replies: 5
    Last Post: 09-17-2012, 01:09 PM
  2. Replies: 3
    Last Post: 08-22-2012, 06:28 AM
  3. Copy data from excel to access
    By aluksnietis2 in forum Programming
    Replies: 6
    Last Post: 12-01-2011, 07:22 AM
  4. Replies: 6
    Last Post: 11-05-2011, 09:01 AM
  5. Replies: 1
    Last Post: 11-21-2010, 10:26 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums