how can i create an automated import?

[rabamas]

I use Access97 presently for my business' database system. Everyday, I download datafiles from a business partner's FTP site that need to be imported into a table in my mdb file.

So, every day, I go through this laborious routine of going to the file menu, choosing import external data, selecting my import mask, clicking through the successive series of windows, selecting the table to append the records to, and finally getting it imported.

Is there an easier way?!?

Can I create some sort of script that will execute every day or every hour, check and see if a file is in a directory and if so, import it to a predetermined table, all automatically? If so, how could this be done?

Any ideas?

Rabamas

 

[rabamas]

is this all in VBA?

I've never used VBA, but I have a book on it that I've been meaning to read.

Is this the language I would use to write all this, including the part that checks to see if a file is present, as well as the part that executes the "transfertext" method?

Do you think a quick learner with no actual programming experience (but ive written some mean calculated fields, buddy) could hit the books and figure out how to do this?

 

[sambo]

If you are scared of the programming side, then I would try to create a situation where you need as little code as possible.

The way to do this would be to create the Macro to AutoExec On Open, but if it should happen to fail (because the file doesn't exist, or is already open, or whatever) then simply trap the error in the VB Code and exit the Procedure without completing.

So...

Private Sub Form_Open(Cancel As Integer)
On Error GoTo OpenErr

DoCmd.RunMacro "YourAutoExexMacro"

OpenErr_Exit:
Exit Sub

OpenErr:
MsgBox "There Was An Error!!" & vbCrLf & "Try Again Later"
GoTo OpenErr_Exit

End Sub

This simply quits trying if any error occurs. The more efficient method would obviously be to automate the program to do different things based on different errors, but this method will work assuming the environment is right for performing the process you described.

Good Luck

 

[sambo]

P.H.
I appreciate your poignant reply ... (such tact).
But... I wasn't talking about trapping the actual error itself. The method I suggested simply traps the fact that there was an error. That way, if it works, then it works. But if not, then the user is prompted with an error message to try again later. I haven't actually ever used macros, but if you could come up with the right macro, then possibly this method may work for you.
I believe the original problem stated simply that the user was looking for a quick solution.

 

[simongallop]

Another way is if the file that you are downloading has the same name each day then bring it in to the database as a Link file. As you are importing the data into a table then create a query that takes the data from the link file and appends it to your permanent table. Next, goto your permanent table and make sure that the primary keys have been set. This will prevent duplicate data from being stored. For the primary keys if you have 20 records for a given date , then you will need to set the date as a PK but also something else which is different in each of the records such as a customer ID (if there is only 1 record / customer) etc.

Once the primary keys have been set then create a macro which opens the appendquery. Now you do not need to worry if the file has been updated or not because anything that has already been added won't be added again due to the Primary keys.

HTH

 

[rabamas]

Thanks everybody. This has all been very helpful. I did not know of the existence of the TransferText method, and I have been practicing with it all day now. Looks like it will solve most of my problem.

New Problem: TransferText method wants to know the filename up front, but there are actually several files each day, all with different names. Is it possible to tell that Method to get all files in a certain directory?

And can this event happen on a timed basis - in other words, every 15 minutes it checks the directory for new files and loads them in if it finds any?

It sounds far fetched to me, but then again, so did my original question which has since been shown to be quite elementary.

rabamas

 

[rabamas]

awesome. this forum is going to totally open doors for me. i only wish i had found it 3 years ago!

looks like i need to explore and learn how to use this "archive" thing. i'll try to look there 1st before i post in the future.

rabamas

 

[gfierro]

I have code that does what you want

Dear "I hate computers"

I don't know if you have solved your "import" problem, but I have code that automates the import of text files off an FTP site and populates 10 tables - all at a touch of a button. I use the Dir command to check what files are availabel then I loop through the list.

Let me know if this would be usefull to you, or maybe you have already solved your problem...

...greg...

 

[rabamas]

Greg,

That sounds like EXACTLY what I need. I would love to set-up something like that on my database.

Could you help me? Let me know how to contact you!

Thanks in advance,

rabamas

 

[MSAccessUser]

Can someone please post the code? It would help me also and I believe others...

 

[TylerTand]

I have a similar issue that I am trying to do. I have daily downloads of data that come in separate excel spreadsheets. I would like to import them all to a single table daily and then update the table with today's numbers from each of the spreadsheets. If this code that you spoke about would do such a thing I would greatly appreciate it if you could post it. Thank You.