Welcome to SPN

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

Sign Up Now!

changing table name after queries created

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

  1. pjm

    pjm
    Expand Collapse
    Guest

    I have a table with multiple queries that uses this table. I need to change
    the table name and want the queries to use this new table. I have tried but
    am missing something very basic I assume.
     
  2. Loading...

    Similar Threads Forum Date
    S Asia Thinking about the big move: The Sikh way of life changing in Khyber-Pakhtunkhwa Breaking News Mar 22, 2014
    Opinion Haanji's Quick Takes on a Changing World (September 23, 2013) Breaking News Sep 22, 2013
    Changing my last name to Kaur... Questions and Answers May 15, 2013
    Life-changing Inspirational Stories Mar 31, 2013
    USA Sikhism and the Changing Electoral Demographic Breaking News Mar 8, 2013

  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Access does not do this well for you.

    One approach is to read the SQL property of the QueryDef, perform a Replace
    somewhere (e.g. in Notepad), and then assign the changed SQL statement back
    to the QueryDef.

    You can read the SQL statement for Query1 by opening the Immediate Window
    (Ctrl+G), and entering:
    ? CurrentDb.QueryDefs("Query1").SQL
    Then after changing the SQL statement, you can paste it back with this kind
    of thing:
    CurrentDb.QueryDefs("Query1").SQL = "SELECT * FROM Table2;"

    --
    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.

    "pjm" <pjm@discussions.microsoft.com> wrote in message
    news:FAEA7131-6898-476A-BDFC-40038E4B401D@microsoft.com...
    >I have a table with multiple queries that uses this table. I need to change
    > the table name and want the queries to use this new table. I have tried
    > but
    > am missing something very basic I assume.
     
  4. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Just to be clear, you don't need the code approach unless you are wanting to
    automate it.

    You could just use this sequence:
    1. Open the query in design view
    2. Switch to SQL View.
    3. Copy the query statement to clipboard.
    4. Open Notepad, and paste the query statement in.
    5. Change the table name in Access.
    6. Perform the Replace in Notepad (Edit menu) to change the old table name
    into the new one.
    7. Paste the result back into the query, and save.

    --
    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.

    "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    news:%238wjgC5gGHA.5088@TK2MSFTNGP02.phx.gbl...
    > Access does not do this well for you.
    >
    > One approach is to read the SQL property of the QueryDef, perform a
    > Replace somewhere (e.g. in Notepad), and then assign the changed SQL
    > statement back to the QueryDef.
    >
    > You can read the SQL statement for Query1 by opening the Immediate Window
    > (Ctrl+G), and entering:
    > ? CurrentDb.QueryDefs("Query1").SQL
    > Then after changing the SQL statement, you can paste it back with this
    > kind of thing:
    > CurrentDb.QueryDefs("Query1").SQL = "SELECT * FROM Table2;"
    >
    > "pjm" <pjm@discussions.microsoft.com> wrote in message
    > news:FAEA7131-6898-476A-BDFC-40038E4B401D@microsoft.com...
    >>I have a table with multiple queries that uses this table. I need to
    >>change
    >> the table name and want the queries to use this new table. I have tried
    >> but
    >> am missing something very basic I assume.
     
  5. pjm

    pjm
    Expand Collapse
    Guest

    Thanks - I think this will work!

    "Allen Browne" wrote:

    > Just to be clear, you don't need the code approach unless you are wanting to
    > automate it.
    >
    > You could just use this sequence:
    > 1. Open the query in design view
    > 2. Switch to SQL View.
    > 3. Copy the query statement to clipboard.
    > 4. Open Notepad, and paste the query statement in.
    > 5. Change the table name in Access.
    > 6. Perform the Replace in Notepad (Edit menu) to change the old table name
    > into the new one.
    > 7. Paste the result back into the query, and save.
    >
    > --
    > 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.
    >
    > "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    > news:%238wjgC5gGHA.5088@TK2MSFTNGP02.phx.gbl...
    > > Access does not do this well for you.
    > >
    > > One approach is to read the SQL property of the QueryDef, perform a
    > > Replace somewhere (e.g. in Notepad), and then assign the changed SQL
    > > statement back to the QueryDef.
    > >
    > > You can read the SQL statement for Query1 by opening the Immediate Window
    > > (Ctrl+G), and entering:
    > > ? CurrentDb.QueryDefs("Query1").SQL
    > > Then after changing the SQL statement, you can paste it back with this
    > > kind of thing:
    > > CurrentDb.QueryDefs("Query1").SQL = "SELECT * FROM Table2;"
    > >
    > > "pjm" <pjm@discussions.microsoft.com> wrote in message
    > > news:FAEA7131-6898-476A-BDFC-40038E4B401D@microsoft.com...
    > >>I have a table with multiple queries that uses this table. I need to
    > >>change
    > >> the table name and want the queries to use this new table. I have tried
    > >> but
    > >> am missing something very basic I assume.

    >
    >
    >
     

Share This Page