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

 

[Pat Hartman]

If you hate computers, I would suggest a career change

Hidden in the import process that you have been doing is the ability to create an import spec. You'll need to import the file yet again manually. This time however, when you're on the last form of the wizard, press the advanced button so you can get to the option to save the spec. To automate the import process, you'll need code that determines if a file is present. If it is, your code would then run the TransferText Method, referencing the import spec that you created.

I would run the code when the database opens if once a day is sufficent. You can do that by adding the code to your opening menu or by creating a macro named specificially "Autoexec". The autoexec macro can run your code and then open your main form.

If you search the archives, you'll probably find several more detailed descriptions of creating this type of process.

 

[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

 

[Pat Hartman]

You can't trap errors that occur in macros. That's why programmers don't use them.

rabamas,
You can start small.
1. create the import spec
2. create a macro that executes the TransferText Action
3. create a command button to run the macro. The wizard will build the code for you.
4. use the menu option to convert the TransferText macro to code

This automates part of the process with the minimum amount of work on your part and NO code that you'd actually need to write yourself. Of course, you'll need to push the button occasionally. Once you have that part working, you can work on automatic methods of "pushing" the button.

Programming is as much a state of mind as it is learning language syntax. Many people teach themselves VBA by building macros to do what they want and then converting the macros to code. This gives them small segments of code to analyze and the process is made easier because they know exactly what the code is supposed to do. I'm not saying that the wizards generate good code. In fact, in some cases they generate really poor code so make sure that you have an alternative reasource for your learning. The Access Cookbook is a really good choice for learning VBA.

 

[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

 

[Pat Hartman]

Sambo, sorry I wasn't taking a jab at you. That's not my style. It was more of a poor explaination for why I couldn't offer a macro solution. But now that I read your profile, you are a programmer and you don't use macros either. I don't have time to test this out but, I believe your solution won't work either since the macro will abend (mainframe word for stop dead and puke) before your code would trap it.

rabamas, there is code in the archives here that does exactly what you are asking. You should be able (if you hit on the right search words) to find code to read all the files in a directory and do something with them. Try searching for "directory". As to having code run at intervals during the day, you can use the timer event of a form. Look up "timer event".

 

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