File trigger

[SandySun]

Does anyone know of how to automate the uploading of a file into access using
a trigger or monitoring method?.... instead of windows scheduler. Or know of
a third party software that i can use?

I would like Access to upload a file whenever it is greater than 30kb.
Thanks!

 

[Jeff Boyce]

Does the file actually have to be uploaded? Could you link to the file from
Access and see the data no matter how much was there?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[SandySun]

Well i can create an .exe or .mam....however i need something to trigger it.
Today I am using windows schedular...however whenever the file is under 30kb
it causes problems for me.

If I linked the data, eventually I will need to bring it into the db....as
updated information overwrites prior information....and whenever the file is
under 30kb, that means that the download was not completed correctly.

 

[Jeff Boyce]

You can purchase commercial products to help automate this kind of activity.

One approach I've used is to use the "first launch" of each day to trigger a
job. How frequently does this external file "fill up"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[youngwolf]

Sandy,
If you link the files, Access can treat them as a table. No need to copy the
data into access unless you are planning to slice and dice with it

 

[SandySun]

Jeff do you know of any commercial products that would work? files are
generally available periodically through out the day. they areuploaded into
a temp table.

 

[SandySun]

Young, the data is sliced and diced....the formating is changed using the
update function of Access. I also have different files uploaded into several
temp tables with a union to create one complete table. Also, the files are
csv and txt.

 

[Jeff Boyce]

Check the FMS, Inc. website ... I believe they have a "Total Access ***"
product that does that.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[DumbWithData]

Jeff:
Would you mind on giving some insight on how you code the 'first lauch'. I
was thinking of simply using AutoExec, but then everytime I open the database
it will perform this function...

Any help would be appreciated.

Thank you

 

[Douglas J. Steele]

Have a table in which you can store the details of when the function was
last performed. Have the AutoExec macro look up that value and compare it to
the current date. If the stored value is earlier than the current date,
perform the function (and update the table). If the stored value is the same
as the current date, don't perform the function.

 

[Jeff Boyce]

Thanks, Doug. That's pretty close to how I do it ... have you been watching
over my shoulder?<g>

Jeff B.

 

[Douglas J. Steele]

Check the code in your timer event. You'll see it sends me e-mails notifying
me of any code changes you've made! <g>

 

[DumbWithData]

Thanks Doug

Have a table in which you can store the details of when the function was
last performed. Have the AutoExec macro look up that value and compare it to
the current date. If the stored value is earlier than the current date,
perform the function (and update the table). If the stored value is the same
as the current date, don't perform the function.

 

[Jeff Boyce]

So it wasn't just me slowing down...

Jeff

Check the code in your timer event. You'll see it sends me e-mails
notifying me of any code changes you've made! <g>

 

[gllincoln]

Hi Sandy,

You could create a vbe that uses the filesystemobject - that will return the
size of the file to you, compare that with 30k, then trigger the import if
the file is greater than 30k. You could run the vbe using windows
scheduler.


Dim fs, f
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile("C:\myFolder\myFileName")
If f.Size > 32768
'do the import
'insert the code here
End If

While I haven't done much with it myself - you can create an ADO object
inside of VBScript - I assume one could reference the target table with the
ADO object and insert the file contents (parsing if need be) and do the
whole thing within the vbe. Or, you could pass a command line argument to
Access that triggers a transfertext (or whatever appropriate) import and
then exits the application object.

Hope this helps,
Gordon