Results 1 to 11 of 11
  1. #1
    Sck is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2010
    Posts
    82

    PULL Access query results into Excel

    I have a Excel file that i am trying to use to pull an Access query results into. the query uses a couple functions in the database for some of its results fields so i cant rebuild it in excel easily. I found the code below but it errors out with a Error 3031 Not a valid Password at the .opendatabase line. I tried removing the password from the database and it still gives me the same error. I need the password on the database but i cant figure out where to add it in the code or why it gives me the error when there is no password set.

    I am using Excel to pull the data because not all the users with the spreadsheet have access to the Access tool but they need the results. Normally, i would just push the results to Excel from Access but that is not an option this time.

    Const cstrPath As String = "C:\Users\sck\Documents\SalesWork\Sales\DR_2.accdb "
    Const cstrQuery As String = "qrySales_Export"
    Dim dbe As Object 'DAO.DBEngine '
    Dim rs As Object 'DAO.Recordset '
    Dim ws As Worksheet



    Application.DisplayAlerts = True 'leave alerts on during testing '
    Set dbe = CreateObject("DAO.DBEngine.120")
    Set rs = dbe.OpenDatabase(cstrPath).OpenRecordset(cstrQuery )

    If Not rs.EOF Then
    Set ws = ThisWorkbook.Sheets("Sheet1")
    ws.Range("A1").CopyFromRecordset rs
    End If

    rs.Close
    Application.DisplayAlerts = True


    Any assistance is greatly appreciated.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Code:
    Dim sConnStr As String
    Dim conn As New ADODB.Connection
    dim vDriver, vSvr, vDB
    dim rst
    
    
    vDriver = "{SQL Server}"
    vSvr  = "abcMySvr"
    vDB = "MyDB"
    sConnStr = "Driver=" & vDriver & ";Server=" & vSvr & ";Database=" & pvDb & ";Trusted_connection=Yes;ConnectionTimeOut = 5"
    
    
    conn.ConnectionString = sConnStr
    conn.Open
    Set rst = conn.Execute(sSql)
    Range("A1").CopyFromRecordset rst

  3. #3
    Sck is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2010
    Posts
    82
    Ranman, your post looks like it is a connection to SqlServer Db. I am trying to pull from an Access Db

  4. #4
    Sck is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2010
    Posts
    82
    Lets try again:

    I have a password protected 2016 Access database (set to legacy encryption) that contains a query that I need to PULL the results of into Excel. The query needs to stay in Access because it utilizes a couple functions that are specific to the database. I also need to pull the data to Excel because some of the spreadsheet users do not have Access and can not use the associated Access frontend.

    I have searched for a code sample that would work but have not been successful. The current code that I am using connects to the database but errors out on the rs.open “qrySalesExport”, con the error is “Invalid SQL statement; Expected Delete, Insert, Procedure, Select, or Update” the qrySalesExport is the name of the query and it runs fine inside of Access.

    Any help is greatly appreciated as i have spent WAY too much time trying to get this to work


    Below is the code sample I started with (my actual code has been updated to match my database location, name, and the query name) Have double checked the names several times.

    'Declaring the necessary variables.
    Dim con As Object
    Dim rs As Object
    Dim AccessFile As String
    Dim strQuery As String
    Dim i As Integer

    'Disable screen flickering.
    Application.ScreenUpdating = False

    'Specify the file path of the accdb file. You can also use the full path of the file like:
    'AccessFile = "C:\Users\Christos\Desktop\Sample.accdb"
    AccessFile = ThisWorkbook.Path & "" & "Sample.accdb"

    'Set the name of the query you want to run and retrieve the data.
    strQuery = "qrRegions"

    On Error Resume Next
    'Create the ADODB connection object.
    Set con = CreateObject("ADODB.connection")
    'Check if the object was created.
    If Err.Number <> 0 Then
    MsgBox "Connection was not created!", vbCritical, "Connection Error"
    Exit Sub
    End If
    On Error GoTo 0

    'Open the connection.
    con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & AccessFile

    On Error Resume Next
    'Create the ADODB recordset object.
    Set rs = CreateObject("ADODB.Recordset")
    'Check if the object was created.
    If Err.Number <> 0 Then
    'Error! Release the objects and exit.
    Set rs = Nothing
    Set con = Nothing
    'Display an error message to the user.
    MsgBox "Recordset was not created!", vbCritical, "Recordset Error"
    Exit Sub
    End If
    On Error GoTo 0

    'Set thee cursor location.
    rs.CursorLocation = 3 'adUseClient on early binding
    rs.CursorType = 1 'adOpenKeyset on early binding

    'Open the recordset.
    rs.Open strQuery, con THIS IS THE LINE IT IS ERRORING ON

    'Check if the recordset is empty.
    If rs.EOF And rs.BOF Then
    'Close the recordset and the connection.
    rs.Close
    con.Close
    'Release the objects.
    Set rs = Nothing
    Set con = Nothing
    'Enable the screen.
    Application.ScreenUpdating = True
    'In case of an empty recordset display an error.
    MsgBox "There are no records in the recordset!", vbCritical, "No Records"
    Exit Sub
    End If

    'Copy the recordset headers.
    For i = 0 To rs.Fields.Count - 1
    Sheets("Existing Access Query").Cells(1, i + 1) = rs.Fields(i).Name
    Next i

    'Write the query values in the sheet.
    Sheets("Existing Access Query").Range("A2").CopyFromRecordset rs

    'Close the recordset and the connection.
    rs.Close
    con.Close

    'Release the objects.
    Set rs = Nothing
    Set con = Nothing

    'Adjust the columns' width.
    Columns("A:B").AutoFit

    'Enable the screen.
    Application.ScreenUpdating = True

    'Inform the user that the macro was executed successfully.
    MsgBox "All data were successfully retrieved from the '" & strQuery & "' query!", vbInformation, "Done"

  5. #5
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    With regard to the user not having Access - they can have Runtime for free.

    Anyway try changing your strQuery to

    "SELECT * FROM qrRegions"
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  6. #6
    Sck is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2010
    Posts
    82
    Minty, thanks that is closer but the internal Access query that I am trying to pull data from uses a couple functions that are defined in the database. when i run the Select * from qrySalesExport it still errors but now the error says Undefined function "ListOfApprovers" I know the functions work correctly as the query runs perfect inside of Access.

    thoughts?

    Mgt does not want them to have the FE but they want them to be able to pull the query results at any time. the query results are just snippet of the information in the database.

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I know it might not be a direct solution to your exact problem, but I don't know if you can make Excel "understand" the custom VBA functions you have in Access. What you can do is copy the query you currently have in Access with a new name, convert it into a make-table query and create a custom function or a macro in Access to run that make-table. Now when you need the data in Excel you just run the macro in Access then connect to the freshly created table.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    Join Date
    Apr 2017
    Posts
    1,673
    In case you have SQL Server available, port your BE to SQL Server DB, and create a view in SQL Server DB which returns same info as your query. Then create an Excel file with ODBC query in it with this view as source, and set the query to be refreshed when Excel file is opened.

    Let your network admins to create a domain users group for Excel report users, define this group as login in SQL server, and map your BE DB with this login with query rights. In case you have to create any UDF's there too, the DB containing UDF's must have execute rights for this login too. My advice is, put any UDF's into separate DB (e.g. utilities), and map this DB with your login separately - so users don't get too much rights for your BE DB.

  9. #9
    Sck is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2010
    Posts
    82
    sadly i dont have SQL Server available. going the route Vlad recommended, just have to figure out how to call a Access function from Excel. Will i need to have Excel open an instance of Access to do this? Any code help on running a sub in a password protected Access Database from Excel is appreciated.

  10. #10
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi, yes you would have to, many examples on the web like this one:
    https://social.msdn.microsoft.com/Fo...orum=accessdev

    or this:
    https://social.msdn.microsoft.com/Fo...orum=accessdev

    I think you will need to give all users the Access runtime for that.

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  11. #11
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Disclaimer: I have never used ADO - I use DAO. But it looks like there are some errors in your code.

    Quote Originally Posted by Sck View Post
    I have a password protected 2016 Access database (set to legacy encryption) that contains a query that I need to PULL the results of into Excel.
    It looks like your connection string is wonky. Looking at The Connection Strings Reference, main page, there is an option to connect to Access.
    Click on ACCESS , then, on the right side, click on the button "Access 2013". You should now be on the page Access 2013 connection strings

    I think you need the connection string in the "With database password" sub header (There is more info if you click "With database password" sub header)
    The general syntax of the connection string is
    Code:
    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Christos\Desktop\Sample.accdb;Jet OLEDB:Database Password=MyDbPassword;


    This line will give a wrong path:
    Code:
    AccessFile = ThisWorkbook.Path & "" & "Sample.accdb"       ' Missing a backslash between the path and the dB file name.
    This one should be OK.
    Code:
    'AccessFile = "C:\Users\Christos\Desktop\Sample.accdb"
    After the ADO connection is working, you could use FSO to pick the file you want.


    You first should get the ADO connection working on a dB without a password before you try to open a password protected dB.




    I found this code. Maybe it will help
    Code:
    Sub ADO_Connection()
        'Creating objects of Connection and Recordset
        Dim conn As New Connection
        Dim rec As New Recordset
        Dim DBPATH  As String
        Dim PRVD  As String
        Dim connString  As String
        Dim sQuery As String    ' Query is a reserved word, so I used sQuery
    
        'Declaring fully qualified name of database. Change it with your database's location and name.
        DBPATH = "C:\Users\Christos\Desktop\Sample.accdb"
        
        'This is the connection string that you will require when opening the the connection.
        connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBPATH & ";Jet OLEDB:Database Password=MyDbPassword;"
        'opening the connection
        conn.Open connString
        'the query I want to run on the database.
        sQuery = "qrRegions"
        'running the query on the open connection. It will get all the data in the rec object.
        rec.Open sQuery, conn
        'clearing the content of the cells
        Cells.ClearContents
        'getting data from the recordset if any and printing it in column A of excel sheet.
        If (rec.RecordCount <> 0) Then
            Do While Not rec.EOF
                Range("A" & Cells(Rows.Count, 1).End(xlUp).Row).Offset(1, 0).Value2 = _
                rec.Fields(1).Value
                rec.MoveNext
            Loop
        End If
    
        'closing the connections
        rec.Close
        conn.Close
    
    End Sub



    Also see How to connect to Access Database – ADO Connection String
    Look at the example for "Connection with Access 2007/2010 Database"

    Or maybe Connecting to a Database

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

Similar Threads

  1. Replies: 2
    Last Post: 10-08-2020, 11:33 PM
  2. Replies: 1
    Last Post: 12-05-2017, 02:13 PM
  3. Excel Addin to Pull Data from Closed Access Databases
    By zubair57 in forum Import/Export Data
    Replies: 0
    Last Post: 04-07-2016, 12:37 PM
  4. Replies: 3
    Last Post: 01-07-2014, 03:39 PM
  5. Replies: 1
    Last Post: 08-31-2012, 04:41 AM

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