Auto Append (1 Viewer)

TallMan · Dec 16, 2008

Hello,

I am having IT overwrite an excel file daily which then needs to be appended into my access database. Is there a way to have the file that IT downloads to me automatically append into my Access dB? My IT department does not support access so they are going to be overwriting the information daily to an excel file.

Not sure if I provided enough information.

Thank you for any help.

pbaldy · Dec 16, 2008

Man, I'm happy to be both IT Manager and Chief Access Developer so I don't have these issues with myself.

Presuming the location and structure of the Excel file don't change, you should be able to link to the file and create an append query to pull the data over. Further presuming that you can count on them completing their side by a given time, you can schedule your query to run automatically at some point after that.

Banana · Dec 16, 2008

It is certainly possible, but we will need a bit more specifics to figure out a way to make this automated.

How does the file get downloaded? Do you have to do something to get that file or does IT moves it? Is it a scheduled task?

Information on that will help us provide ideas where you can start to automate the appending process.

TallMan · Dec 16, 2008

Well a batch is run nightly for every account that has had a change to it. This information is then downloaded to the same excel file everyday. I have asked them to do this so I do not have to look for a new "dated" file everyday. So in essence IT moves the file for me from a crystal system every night at midnight and then it overwrites the previous days file. My goal is to get it to append into my database automatically.

Hope this helps clear it up. If not let me know. Thanks guys!

Banana · Dec 16, 2008

In this case, a simplest method would be as pbaldy alluded to, use Task Scheduler to run a command to open Access with -x argument to run a macro that will run the append query then close itself.

TallMan · Dec 16, 2008

Great Thanks guys!

TallMan · Feb 4, 2009

Hey Guys,

So I am looking into this a little more in detail now. I have tried to do the task scheduler going to "control panel" > Task Scheduler but then when I proceed to enter the day and time and my password it does not ask me exactly what I want done. Basically it allows me to select the database but gives me no option to do anything within the database. Does that make sense? Again this is new to me and any info you provide would be much appreciated.

Thanks again.

TallMAN

boblarson · Feb 4, 2009

What you need to do is to set an AutoExec macro that kicks things off when the database is opened and then closes it when it is done. Your scheduler should be set to just open the database.

TallMan · Feb 4, 2009

AH ha! Makes sense now!

Thank you kind sir.

pbaldy · Feb 4, 2009

I usually create an mdb that does nothing but the specified task, but if you just want to run a macro in your main db, go into the properties of the scheduled task, where it should say something like this in the Run field:

C:\Folder\File.mdb

You should be able to add the command line switch, which I think is /x macro, so

C:\Folder\File.mdb /x MacroName

TallMan · Feb 5, 2009

So I was able to create this and have it run when I need it. I have two more issues.....
1) I read on another post in this forum that it would be better to exclude the set warnings in the code rather than to set it to "low". Does anyone know the code to use?
2) I am trying to have this auto exec run when I am not at the office and I am logged out. I have made sure to not check the button on the scheduler task so that it will run when I am logged off as well but this is not happening.

Has anyone had this issue before?

Thanks Guys!

Auto Append (1 Viewer)

TallMan

Registered User.

pbaldy

Wino Moderator

Banana

split with a cherry atop.

TallMan

Registered User.

Banana

split with a cherry atop.

TallMan

Registered User.

TallMan

Registered User.

boblarson

Smeghead

TallMan

Registered User.

pbaldy

Wino Moderator

TallMan

Registered User.

Similar threads

Users who are viewing this thread