Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Access : Refresh imported table

Posted on 2006-11-01
10
Medium Priority
?
1,249 Views
Last Modified: 2012-06-27
i have a table that i created by importing the data via ODBC.  how do i refresh the data?
0
Comment
Question by:zephyr_hex (Megan)
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 17853642
Hi zephyr_hex,

There is no refresh concept for  imported data.
You have to import it again.

Pete
0
 
LVL 44

Author Comment

by:zephyr_hex (Megan)
ID: 17853797
ok.
is there a way to automate the import so that it does it itself every so often?
0
 
LVL 2

Expert Comment

by:Deb8stud
ID: 17853847
You could link the table rather than importing it.  That may save you some trouble.

Go to File - Get External Data - Link Tables.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 44

Author Comment

by:zephyr_hex (Megan)
ID: 17854513
i need to have it imported because of how i'm using it.  it won't work as a linked table.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 17856191
What do you have in mind with 'every so often'
Once a week? Once a day? Once an hour? Every 5 minutes?

Pete
0
 
LVL 44

Author Comment

by:zephyr_hex (Megan)
ID: 17859060
once an hour would do.  or even once a day.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 17866825
But you wouldn't want Access to start messing around with a table while someone was using it would you?

This sound like it ought to be run on user request, pressing a button.

Docmd.DeleteObject actable, "mytable"
'your import procedure here

Pete
0
 
LVL 44

Author Comment

by:zephyr_hex (Megan)
ID: 17892168
the data pull is only a READ, not a write.  there will never be a case where something in Access gets written to the main database.  i am only using Access for reporting purposes, and i need the data to be relatively current.  i could import the data once a day, but the maintenance of that is going to be a hassle.  i was hoping to automate that import.

a button would not work because the user will not actually be opening up Access to see the data.  i am viewing the Access query/table in Sharepoint.  so the user will bring up a web page, which will make a call to the Access query/table and display the contents.  i have this process working fine on imported data in Access.  so if i could automate the import, then the user would be viewing recent information without someone having to go in and re-import that table in Access every morning.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 2000 total points
ID: 18051043
zephyr_hex,

You could try using Windows Task Scheduler to run the code once per day.

Something like this in the "Run" box:
"C:\Documents and Settings\All Users.WINNT\Start Menu\Programs\Microsoft Access.lnk" "c:\db2.mdb" /x mcrMyImport

"/x" is the switch that tell access to run a macro
"mcrMyImport" is the name of the Macro

Your macro would in turn run your code

So your code would look something like this:

Function MyImportCode()
    Docmd.DeleteObject actable, "mytable"
    'your import procedure here
End Function

You would put this code into a module

Then create a macro that calls the function:
Macro Name: mcrMyImport
Action: RunCode
Function Name: MyImportCode()

Theoretically, this should work. However, there are a few things you should watch out for.
<Peter57r>
<But you wouldn't want Access to start messing around with a table while someone was using it would you?>

Yes, I know you say that the data pull is read not write.
I am not a big fan of having Access do *anything* automatically or behind the scenes while someone could be accessing the same data.

For example:
1. You set the task to run at 7AM. But, Mr. Early Bird gets in at 6:50 and starts working on the DB. Firstly, he thinks he is working on the new data, but in reality he is not.
Also, the first line of code is to delete the old table! Now what happen while the user is in it???
2. Ok, so you set it to run at 3AM, what happens if the network is doing a backup of the Drive the DB is on?
What happens if the machine is defragging?

3. What if the function crashes midway during the import? The data may be corrupted. How would you know?
4. What if the scheduled task fails to run for some reason? (someone deletes or modifies it). The only external indication would be checking the Task scehduler itself. Users could be working with old (or worse, corrupted) data an not know it.

Again,  probably 95% of the time this will work fine. Depending on your setup you might never encounter any problems.

I just thought I would bring these things up.
:)

Good luck with you project!

Hope this helps as well

Jeffc
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 18125855
Thanks!
:)

Jeffrey Coachman
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

879 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question