?
Solved

Access : Refresh imported table

Posted on 2006-11-01
10
Medium Priority
?
1,312 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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 …
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

601 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