Importing text files automatically into ms access

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

nursell

Thread Starter
Joined
Jul 26, 2011
Messages
143
Hi,

I have been searching for a while about importing text files automatically into my database.

I was thinking of having a hidden form with code attached the looks in a folder on a network drive and to make it more difficult i need it to look every 5 seconds.

So when my external program exports the files to Y:\Dispatch\Export\100045.out access will pick it up and import the data to my access table

I hope this can be done and someone can help me.

Thank you
 

OBP

Joined
Mar 8, 2005
Messages
19,895
First of all is "100045.out" the name of the file?
A form can be set up to do this using the Form's Timer setting and On Timer Event Procedure, but the naming of the file may be important and also the file's data structure, does it change?
Is it to be Appended to a table?
 

nursell

Thread Starter
Joined
Jul 26, 2011
Messages
143
Yes 100045.out is the file to be imported but the last two digits change every time like 45,46,47,48 and so on.

.out is a text file by the way

The file structure will be the same everytime e.g

"33587","Mr Smith","2 Test Road","Hartlepool","HT4 4EU","245665"

I want the data to be put in the same table everytime named "DPDTrackingNumbers"
 

OBP

Joined
Mar 8, 2005
Messages
19,895
I think you will need a Table containing the Names of the imported files, so that you know when a new one appears.
Then import it in to a temporary table and use an Append Query to add it to the real table.
What version of Access are you using?
 

nursell

Thread Starter
Joined
Jul 26, 2011
Messages
143
Im using Microsoft Access 2003.

That sounds good if you can help me with it? I am not very good with VBA sorry :-(

A new file will be in the folder between every 5-10 seconds
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Access 2003 has a VBA facility that allows you to load all the file names from a Folder which you can then compare to the names in the table.
Or another option is to Delete or move the files out of that folder once copied.
Your Access database will be working pretty hard if you are importing every 5-10 seconds, you might be better of importing every 10 minutes or so and importing all the files in one go.
 

nursell

Thread Starter
Joined
Jul 26, 2011
Messages
143
That will be brilliant if the files can be deleted after being imported. Every 10 minutes is fine.

Can you help me with the VBA? I'm really looking forward to learning.

Thank you so much
 

OBP

Joined
Mar 8, 2005
Messages
19,895
I would use something very similar.
Note the bit about file name extensions.
Got to go now.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Do you currently have some files in the Y:\Dispatch\Export\ folder?
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top