Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Make-table Query - Destination DB question

Discussion in 'Information Technology' started by Scott, Jul 28, 2006.

  1. Scott

    Scott
    Expand Collapse
    Guest

    Hi,

    Is there an easy way of changing the output database (i.e. Destination
    DB) in several make-table queries that export data to a separate DB? I
    ask as we are in the process of migrating an Access 2000 DB onto a
    number of new office servers which will have different path names, so
    what was along the lines of g:\..\Database could become j:\..\Database,
    but we don't want to have to go in and manually change the SQL code for
    all the queries... is there a quick way of doing this, or could we set
    the destination DB as a field that could be changed once and replicated
    through all the queries?

    Sorry if this is a little vague, but these boards have helped me out on
    several occasions before!

    Scott
     
  2. Loading...

    Similar Threads Forum Date
    Query about Jhatka Meat by Shooting in Head Sikh Sikhi Sikhism Aug 26, 2011
    Who is a sikh? A non sikh friend's query!! Sikh Sikhi Sikhism Apr 30, 2010
    General Query Hard Talk Sep 4, 2008
    Power of pauri's in Japji Sahib query Sikh Sikhi Sikhism Aug 17, 2006
    Sikhism a query Book Reviews & Editorials Aug 2, 2005

  3. Rick Wannall

    Rick Wannall
    Expand Collapse
    Guest

    There is no way to vary the destination DB specification in a query other
    than rewriting the SQl for the query on the fly.

    That said, you can use vb to get to the sql of your query and do anything
    you want. If your export process is run by a stream of vb code, you can use
    a database object (as DAO.database) to get a querydef object (as
    DAO.querydef) and in code get to the sql of the querydef. From there you
    can parse to replace the database path with a new one, or you can store the
    core SQL as a constant in the vb code with a placeholder that you replace.
    For example:

    const MYSQL as string = "Select.....Into [mydatabasepath]..."

    dim db as dao.database
    dim qd as dao.querydef
    dim s a sql
    set db = currentdb
    set qd = db.querydefs("myquery")
    qd.sqls = replace(MYSQL, "[mydatabasepath]", "d:\xxxx\yyy\abcd.mdb")

    Ready to go.
     
  4. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Scott, you can read the SQL property of a querydef, Replace() any occurrance
    of the string, and assign it back to the QueryDef.

    This kind of thing:
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Set db = CurrentDb()
    For each qdf in db.QueryDefs
    If Instr(qdf.SQL, "g:\..\Database") > 0 Then
    qdf.SQL = Replace(qdf.SQL, "g:\..\Database", "j:\..\Database")
    End If
    Next
    Set db = Nothing

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Scott" <scottsilverthorn@gmail.com> wrote in message
    news:1148387169.940150.108190@u72g2000cwu.googlegroups.com...
    >
    > Is there an easy way of changing the output database (i.e. Destination
    > DB) in several make-table queries that export data to a separate DB? I
    > ask as we are in the process of migrating an Access 2000 DB onto a
    > number of new office servers which will have different path names, so
    > what was along the lines of g:\..\Database could become j:\..\Database,
    > but we don't want to have to go in and manually change the SQL code for
    > all the queries... is there a quick way of doing this, or could we set
    > the destination DB as a field that could be changed once and replicated
    > through all the queries?
    >
    > Sorry if this is a little vague, but these boards have helped me out on
    > several occasions before!
    >
    > Scott
     
  5. (PeteCresswell)

    (PeteCresswell)
    Expand Collapse
    Guest

    Per Scott:
    >Is there an easy way of changing the output database (i.e. Destination
    >DB) in several make-table queries that export data to a separate DB?


    If you find an easy way, let me know....-)

    When I converted to doing work tables the proper way (storing them under
    [UserName]....) I gave up and just replaced the few MakeTables with a generic
    routine "CreateWorkTable" that copies a model into the work TB and creates a
    link to it... and then used Append queries.

    Maybe somebody can elucidate, but I can't think of any advantage to using
    MakeTable except for PivotTable presentations where the column names vary
    depending on data.
    --
    PeteCresswell
     
  6. Rick Wannall

    Rick Wannall
    Expand Collapse
    Guest

    Copying a model table to a work DB is simple and clean.

    Seconded.
     

Share This Page