Refreshing Imported Text File Data Every X Minutes

[acarella610]

Hi All. I am hoping someone here can help me.

I have an external text file that is updated every 15 minutes from an Oracle database. I would like this file imported into an Access database table, but I would like the table refreshed every 30 or so minutes so that any changes made to the text file will eventually show up in the table.

I know that there is a link-to-data-source option, but it seems like that holds up whatever text file you are trying to link to in that the file cannot be modified while the access file is open and referring to it. Being that the text file that I will be linking to is updated every 15 minutes from an Oracle database, that would present a problem.

Is there any way to refresh imported data at a specified interval of time in Access? Microsoft Excel has this function so I figured that this would be easy to do in Access as well. I am new to Access and still trying to figure things out.

Thanks in advance,

Anthony

[rpeare]

Doing time intervals in access is not clean and you would likely have to have a database that was always open while you wanted this to occur on a machine where nothing else was happening.

here's some code to help you do it:

http://www.fmsinc.com/MicrosoftAcces...idDoEvents.asp

Personally, I would, if you have access to it, use a SQL back end. It's very easy to create and run a SSIS job to transform your data every 15 minutes that would be much quicker and more efficient than trying to do it all in MS Access. If you do not have that option available to you you are likely going to want a dedicated database that just does nothing but checks the time and performs the import/transformation. This database would have to reside on a computer where it could be open all day and not be interrupted by other users.

[acarella610]

Hm. Thank you for the response.

I guess my follow-up question would be: how bad would it be for this text file with data that I need from it if I link it by creating a linked table in Access? As mentioned before, the file is updated every 15 minutes by a mechanism-query (for lack of a better term because I do not know exactly how it works) in the oracle database that the text file data comes from.

The text file is never actually opened. It is updated every 15 minutes, and is also linked to an excel file via the "Get External Data" text import wizard which creates a datafeed that is updated every 5 minutes in the file.

I tested this out, and noticed that as long as I do not open the table in Access, the text file can be updated. Once I open it in Access, however, I cannot even open the text file in windows, import it into Excel, etc... Is it safe to say that as long as this text file linked data table is not opened in Access, I should be fine using it as a linked table which will update whenever the Oracle database updates it every 15 minutes and the Excel file imports it every 5 minutes?

Thanks again. Much appreciated. I am completely new to Access and think it definitely suits my needs, but I have some learning to do.

[acarella610]

And I guess I should have asked this in the beginning. Is there any way (other than importing the data and using it as a linked table) to get data onto an Access table from an external text file and update the table at certain intervals without disabling or reducing access to updating the text file from other programs?

[rpeare]

I'm going to ask a couple of really basic question about your process.

Is there a reason you can't create a query in oracle itself and link to that directly without using the text file intermediary?
Have you considered, when you want to run things in your access database creating a copy of this text file and linking to the copy?

For instance if your normal file is called OracleExport.txt
You can, with vba code, create a copy of that file (say AccessOracleExport.txt) that copies the original file during your run time then linking to the copy of the file.

Here's how you copy/create a file with vba:

http://easyprograming.com/index.php/...e-in-vb-script

[acarella610]

To answer your first question; I do not have the access to the Oracle database that way. The text files are query results that are made at my (and other peoples) request by a systems administrator.

To answer your second question; I have not considered that at all in that I am fairly new to programming. I have written extensive VBA code in Excel (and have run some fairly complicated queries in SQL), but I have not touched VBA in Access and was under the impression that it was fairly different. I am still learning the program Access itself, let alone modifying it with VBA code.

However, if the second option of copying the text files is the best solution to my problem, then I guess I should tackle it that way. I like the linked table option that Access offers, but I guess that I am now confused as to why it is even available if it will just hold up the text file that it is linking to and now allow any other applications to update it. But that's a tangent.

I really appreciate the help. I will take the time to learn the VBA code that will copy my text files and see if that works in my favor. Thanks.