locked
Import Access query from another Access database using vb.net RRS feed

  • Question

  • I need the ability to import a query from one Access database into another, using VB.NET.  I have been able to find documentation on copying tables and creating views but found that the logic for copying tables doesn't appear to carry over for queries and that creating views works for simple views, but not complex ones. 

    Jeff

    Monday, September 10, 2012 2:22 PM

Answers

  • Tom  Unfortunately, none of the suggestions did the trick for us.  We found that while DAO could import many queries, it could not do so with ones that were particularly complex - giving us error messages.  Going to MS SQL is a long range project but didn't address the near term issue.  The way we're going about this due to time constraints is to have the users import all queries from another database.  That appeared to be the only way we could achieve this with consistent results.

    Jeff

    • Marked as answer by JeffWizard Tuesday, September 18, 2012 8:02 AM
    Tuesday, September 18, 2012 8:01 AM

All replies

  • Jeff

    I don't know VB.NET but maybe all you really need is the SQL for the query instead of the object itself.

    In VBA, it's done this way:

        Dim db As Object
        Dim strSQL As String
        
        Set db = OpenDatabase("C:\Databases\Toolbars.mdb")
        strSQL = db.QueryDefs("Query1").SQL
        Debug.Print strSQL


    Bill Mosca
    http://www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Monday, September 10, 2012 2:58 PM
  • Bill -Thank you.  That will not work for us in this case - some of the queries are pretty complex, called by multiple programs, and far easier to call the object from vb.net.  We have tried using create view in vb.net - that works for some, but none that are complex.

    Jeff

    Monday, September 10, 2012 3:18 PM
  • Sorry I can't be of more help, Jeff. Perhaps a vb.net group would be a better source.

    Bill Mosca
    http://www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Monday, September 10, 2012 8:23 PM
  • If your query is fairly sophisticated AND required for your business and you say you need VB.Net then the easiest fix would be to step up to a server based RDBMS like MS Sql Server.   Access is a file based RDBMS - very robust for a mini RDBMS but it is still mini and does have limitations.   Sql Server has a database engine that has 1000 times more horsepower than Access.

    Rich P

    Monday, September 10, 2012 10:00 PM
  • Nit at all sure I understand what you are trying to do, but if you want one access database to be able to run a query in another access database and have the the second database's records returned to the first database, then you can use a simple query in the first database to do it.  The simple query only needs to use a FROM clause with the second database's query name along with IN "path to second database"

    OTOH, if you only want the QueryDef so the complex query can be run against a set of tables in the first database, why can't you use Bill's suggestion of copying the QueryDef's SQL.  If that is really insufficient, then you can use DAO to the first database and create a QueryDef object and copy all of the complex QueryDef's properties from the second database.

    Monday, September 10, 2012 10:41 PM
  • Hi Jeff,

    Thanks for posting in the MSDN Forum.

    Did you solved your issue. Would you please share your solution here if you have solved it.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, September 18, 2012 7:20 AM
  • Tom  Unfortunately, none of the suggestions did the trick for us.  We found that while DAO could import many queries, it could not do so with ones that were particularly complex - giving us error messages.  Going to MS SQL is a long range project but didn't address the near term issue.  The way we're going about this due to time constraints is to have the users import all queries from another database.  That appeared to be the only way we could achieve this with consistent results.

    Jeff

    • Marked as answer by JeffWizard Tuesday, September 18, 2012 8:02 AM
    Tuesday, September 18, 2012 8:01 AM