Importing a TEXT file automatically at certain times without opening ACCESS databas.

[AshikHusein]

I thought my work was finished but now we have decided on a little more automation for the ACCESS project that I am working on. The task has many stages to it but my question is for the first step.

How can you set up ACCESS so that it automatically imports a certain TEXT file from the server into the ACCESS databse.? The TEXT file has to be imported automatically at the end of every week and at then at the end of every month. As mentioned before this should be done automatically even if the ACCESS database is not open.

As before I know this forum has the potential to help me with such tasks and I await a reply. The Microsoft KNowledge Network wasnt much of a help so far. Thanks.

 

[Pat Hartman]

1. Create a macro that does the import or calls a sub that does it.
2. Create a batch file that opens your db and passes the macro as a parameter.
3. Use the Windows task scheduler to schedule the batch file.

Since you have two processes, make two macros, two batch files, and schedule them as necesssary.

 

[AshikHusein]

Problem in running a batch file

I have been trying to make a batch file. The way I am doing this is going to MsWord opening a new file typing in
C:\Program Files\Microsoft Office\Office\MSACCESS.exe. (since that is where the MSACEESS program is stored)

I am then saving this file as exp.bat in file type MS Dos Text. I do successfully save that file as batch file (icon has a small "gear" like object on it and says MS Dos batch file in the properties)

I keep on getting an error which says that C:\Program Files (etc) is not a recognizable internal or external command, operable batch or program file.

Now the thing is that when I create my batch file, it is created in the directory C:\Documents and settings\husienas\Documents.

For some reason when the batch file is double cliked on (and I use "pause" after the first statement to see what errors I am getting) I can see that it tries to run the command

C:\Documents and Setting\husienas\Docummnets>C:\Program Files\Microsoft Office\Office\MSACCESS and that is the reason it could be giving me an error.

Is there a better way to do this or a better way to make a .bat file?

Thanks.

 

[ozinm]

Re: Problem in running a batch file

OK,
first of all you might find it better practice to use notepad rather than word as it won't autocorrect (read that as screw up) your text.

second your execution path in the batch file contains spaces.

There's a few ways round this.

1. use the non-8dot3 path names instead.
if you go to the DOS prompt and type dir /x you'll see the short name for each file and directory.
You'll probably be end up with something like this:
c:\PROGRA~1\MICROS~2\office\MSACCESS.exe

2. you could get the batch file to change to the right directory first like this:
c:
cd \
cd program files
cd Microsoft Office
cd office
msaccess.exe

3. you could add the office directory to your PATH in your batch file.
Your batch file should look something like this:
set OLDPATH=%PATH%
set PATH=%PATH%;C:\Program Files\Microsoft Office\Office
MSaccess.exe
set PATH=%OLDPATH%

4. you could add the office directory to your permanent PATH
From your previous post I'm guessing your using Windows 2000.
Right click: My computer
Click: Properties > Advanced > Environment Varables
select Path from the System Varables box
Click: Edit
Add the following text to the Varable value box: C:\Program Files\Microsoft Office\Office

Then the noly text you'll need in your batch file is:
msaccess.exe



Hope this helps

TTFN

Marc

 

[AshikHusein]

It works!!

That works fabulously! Thanks a lot!!!

 

[ozinm]

No Problemo