Solved

Access : Refresh imported table

Posted on 2006-11-01
10
1,162 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)
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 43

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 43

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 43

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 43

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

617 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