Results 1 to 15 of 15
  1. #1
    Join Date
    Feb 2004
    Posts
    143

    Unanswered: Refresh a linked table with another query

    I am using Microsoft Access 2003 and have a saved query(query1) that joins 2 other already existing linked tables(say table1 and table2).
    I have another Linked table table3(which is a link to an Oracle 10.2.0.3 table) using odbc.
    The query results of query1 exactly has the same columns as the linked table table3.
    Is there a way to automatically refresh the linked table table3 with the query results from query1(for record appends,updates and deletes) ?
    Please suggest. Thanks

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You could use an update query, connect query1 to table3 by a unique identifier and then update the fields you want to update... or you could run a delete query to empty table 3 and then an append query to transfer results of query1 to table3 but it really comes down to what you really want to do... what do you mean when you say refresh?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Feb 2004
    Posts
    143
    Thank you. Will try it out...
    Sorry, when I said refresh, I meant exactly what you thought while replying (which is to delete all and append or an update)

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Good luck! If you run into troubles, feel free to post again
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Feb 2004
    Posts
    143
    Thank you, but sorry, I am still having trouble here in getting this put in place. I am poor in MS Access Queries as I come from a strong Oracle background. But I do know exactly what I needed.

    Here's what I have been doing --(And I am looking for an easier way to do this by just running one MS Access query or two)

    The setup:
    In my DB1.MDB, I have a table TABLE1 that gets updated every day.
    I have another linked table ORATAB1 (linked to an Oracle table via odbc data source) which is exactly the same layout as the TABLE1(column names are different though).

    What I am doing now everyday morning:
    1. I open DB1.MDB, open the ORATAB1 table data grid and empty it out. (By selecting all records and deleting it)
    2. I then open the TABLE1(which would have gotten updated by now) and select the entire data records and copy it to the clipboard.
    3. I open ORATAB1 again and paste it there. The records are thus in the linked oracle table.

    Can this be done all in one query and if so, how ? In other words, I need to delete all rows in the linked table, run a query on another table and insert everything I queried into the linked table.

    Thanks

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    This example was created to refresh/create an ODBC DSN connection to SQL Server (on linked tables in MSAccess) but perhaps you can modify and use it for your needs: http://www.dbforums.com/showpost.php...5&postcount=37

    It's the best I can offer.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Can you do that in one query? No.

    Did you try what I said? Run a Delete query to empty the linked table and an Append query to re-populate it.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Follow StarTrekker's advice saccskiz (not sure why I wrote what I posted before - disregard). You need do what StarTrekker says and run a delete query on the ORATAB1 table and then an append query appending Table1 to ORATAB1.
    Last edited by pkstormy; 04-28-08 at 23:27.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Feb 2004
    Posts
    143
    StarTrekker/Pkstormy,
    Thanks a lot.
    Yes, I was able to use StarTrekker's advise and now have one delete query and one append query in place. Works great.
    Here's my perhaps final question on this subject :
    Is there a way to automate the 2 queries in MS Access. In other words, can you provide me an idea if I can trigger these 2 queries one after another at a defined time everyday without user intervention. If that cannot be done, if a single button click (somewhere) which executes both queries can be done, though manually, I am interested to know how.

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You can go to Control Panel -> Scheduled Tasks and make the Access mdb run at a scheduled time. Then in the mdb file, create an Autoexec macro (or design a function in a module). If you decide to do a macro (which again, you would save as: Autoexec (if Access sees a macro called Autoexec in an mdb, it will automatically run it when the mdb opens.)) In the macro (or function, you want to set warnings to false so it doesn't prompt to run the query(s) and then set warnings to true when done. If decide to do the function route, it would look something like this:

    Function runOnStartUpFunction()
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "MyDeleteQuery"
    DoCmd.OpenQuery "MyAppendQuery"
    DoCmd.SetWarnings True
    docmd.Quit
    End Function

    and then you'd run the function either via in the OnOpen event in a form which you then make your startup form (ie. Tools -> Startup) or you can put a RunCode command in the first line of the Autoexec macro to run the function - different ways to accomplish the same thing.)

    If you want to avoid opening Access all together, you can create a vb script to execute at a scheduled time. Here's an example of code in a vb script to update an Access table:


    Set WSNet = CreateObject("WScript.Network")

    varUserName = WSNet.UserName

    Set WSNet = Nothing

    Set MyConn = CreateObject("ADODB.Connection")
    Set RS = CreateObject("ADODB.Recordset")

    MyConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\AccessExamples\CustomersXPSample.mdb"

    Set RS = MyConn.Execute("UPDATE Users SET LoginStatus ='" & "LoggedIn" & "' WHERE LoginID ='" & varUserName & "'")
    ' or put whatever SQL code in place of the statement above
    'ex: set RS = MyConn.Execute("DELETE * from MyTable")
    'ex: set RS = MyConn.Execute("INSERT INTO MyTable (Field1, Field2) SELECT MyAccessLinkedOracleTable.Field1, MyAccessLinkedOracleTable.Field2
    FROM MyAccessLinkedOracleTable")

    MyConn.Close

    Set RS = Nothing
    Set MyConn = Nothing

    You'd save the above code (using notepad) as a .vbs extension and then schedule to run the .vbs file in Windows Scheduler or just double-click on the .vbs script to run it.

    But since you're dealing with a linked Oracle table you're using to update an Access table, your best option might be to simply go with the Access autoexec script without getting too complicated.

    (Althoughhhh....if you're interested, here's vbs code you'd use/modify to tap into the Oracle table via a vbs script

    Set WSNet = CreateObject("WScript.Network")

    varUserName = WSNet.UserName
    Set WSNet = Nothing
    Set MyConn = CreateObject("ADODB.Connection")
    Set RS = CreateObject("ADODB.Recordset")

    'modify the below lines to use/tap into an Oracle or other driver
    MyConn.open = "DRIVER={SQL Server};"_
    & "SERVER=SQLSERVER;"_
    & "DATABASE=PKTime;"_
    & "OPTION=35;"

    sql_query = "UPDATE dbo.Users SET LoginStatus ='" & "LoggedIn" & "' WHERE LoginID ='" & varUserName & "'"

    MyConn.Execute sql_query
    MyConn.Close

    Set RS = Nothing
    Set MyConn = Nothing

    I may have given you a little too much info though (sorry about that - hope I haven't confused you) and again I'd probably just stick with the simple autoexec in Access and schedule the Access mdb to run.
    Last edited by pkstormy; 05-02-08 at 22:06.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  11. #11
    Join Date
    Feb 2004
    Posts
    143
    This is great info. After reading this post, I decided to go with the Access autoexec script option.
    I am getting close but not there yet.
    Here's what I did :
    Under Modules, I have a runOnStartUpFunction module which has a function (my querynames are exactly the same as mentioned below):
    Function runOnStartUpFunction()
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "MyDeleteQuery"
    DoCmd.OpenQuery "MyAppendQuery"
    DoCmd.SetWarnings True
    docmd.Quit
    End Function

    I have a module called Autoexec for which, in the design view, I have an Action called RunCode which asked me to specify a function name. I gave the function name as runOnStartUpFunction.

    Now, what happens is that thru my windows scheduled tasks, it does invoke my mdb file and attempts to run the Autoexec. It does not do anything else.
    I then tried to run the Autoexec Macro by doubleclicking and I get this error message :

    Microsoft Office Access can't find the name 'runOnStartUpFunction' you entered in the expression.
    You have specified a control that wasn't on the current object without specifying the correct form or report context..........

    What could I be missing here ?

    Thanks

  12. #12
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    1. Did you compile? (ie. when viewing code....Debug -> Compile)

    2. Make sure you have a function called runOnStartUpFunction (not a module named that.) You should name the module something else like: Startup Module. Easiest way is to copy the function name and paste it into the macro window (and use the () at the end of the function name.)
    Last edited by pkstormy; 05-05-08 at 22:19.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  13. #13
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Why not just use the AutoExec macro itself to run the queries... rather than going to code? The task is too simple to warrant coding imo.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  14. #14
    Join Date
    Feb 2004
    Posts
    143
    As you suggested, I just used the Autoexec macro itself to run the queries (without using any other code). I was able to setWarnings and run queries in the same exact order. So I am good here and exactly need what I was looking for !
    Thanks all.

  15. #15
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You're welcome

    Glad it's sorted!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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