Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access to Excel (2000)

    I am writing some code in Excel that gets data from an Access database. What I'd like to do is reference the database (that has a secured.mdw file) and change the SQL of some of the queries at run time. I then want to dump the records into my Excel spreadsheet. I don't think I can use ADO to do this. How do I
    1. Connect to the database?
    2. Return the records of a query once I have altered the queries sql?

    I have to use a saved query as the query is based on another query, unless there is a way to create temporary tables or something.

    Many thanks in advance


    Amanda

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access to Excel (2000)

    1. See MSKB article How To Open a Secured Access Database in ADO Through OLE DB for methods to connect to a secured database using ADO (the first part is about a password=protected database, the second part about a database with user-level security).

    2. You state "I have to use a saved query as the query is based on another query". I'm not quite sure what exactly you mean. If the query you want to modify is the query to be executed, you only need the SQL statement. If the query you want to modify is the underlying query, you will have to change the stored query. You'd need to use ADOX for that.

    Note: you can also use DAO to connect to the database and to manipulate queries.

  3. #3
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access to Excel (2000)

    Hans thanks for your reply.

    Is there any difference in connecting to my secured database through ado and adox?

    I already have an ADO connection - if it is the same as adox - how do I refer to the queries in the underlying database?

    Thanks for the reference to the article.

    Amanda

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access to Excel (2000)

    You connect to the database using ADO. But ADO does not let you modify the design of a query, you need to use the Microsoft ADO Extensions for DDL and Security (ADOX) library for that. The idea is like this (this will change the SQL command for the query named qryTest):

    Dim cnn As New ADODB.Connection
    Dim cat As New ADOX.Catalog

    cnn.Open "Provider=..."
    ...
    Set cat.ActiveConnection = cnn
    cat.Views("qryTest").Command = "SELECT * FROM Customers"
    ...
    cnn.Close
    Set cat = Nothing
    Set cnn = Nothing

    To use ADOX, set a reference in Tools | References... to Microsoft ADO Ext. 2.8 for DDL and Security (the version number might be different)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •