Solved

Access : Refresh imported table

Posted on 2006-11-01
10
1,073 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
  • 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 42

Author Comment

by:zephyr_hex
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
 
LVL 42

Author Comment

by:zephyr_hex
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 42

Author Comment

by:zephyr_hex
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 42

Author Comment

by:zephyr_hex
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 500 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

759 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now