Solved

Access : Refresh imported table

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

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 42

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 42

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 42

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

770 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