Web service to call Access Function

[TKnight]

Hi,

I have an Access DB which is essentially a form with a timer event which runs code every few minutes to get data in "near" real time from a web service. It then processes the data a bit and feeds it into SQL server tables before waiting for the next timer event.

It all works fine but it's not exactly an elegant solution because I have to leave that DB open on a machine that runs 24/7 (a crude server basically) and over time it eats up the virtual memory and other little problems appear. We have a file server that I can use so what I would like to do is come up with a script that can be called from windows scheduler which will open up a DB object (hidden), call some code in there, and then close afterwards. I know I can do this via a shortcut which opens the DB and it can close itself down afterwards but i'd rather it was done at object level if possible. The problem is i'm pretty good with VBA but I don't know any other langauges and i'm struggling to find a good example.

I took the code below from a post I found on another forum but I don't even know what language it's in or how the script would start. Once I get to the access object I should be ok from there but I just don't know how to get going. I guess VBscript would be my preferred choice of language if possible just because of its similarities to VBA. If anyone could help me (or suggest another alternative) i'd be really grateful.

Thanks,

Tom

Microsoft.Office.Interop.Access.ApplicationClass ObjAccess =  New                                                                  Microsoft.Office.Interop.Access.ApplicationClass();

ObjAccess.Visible = true;
ObjAccess.OpenCurrentDatabase(@"\\server\public\EBtest.mdb", false, "Admin"); *open the database

 

[MarkK]

Get windows scheduler to open a database. That database can then run code immediately either using the AutoExec macro, or the Open or Load events of the StartupForm. That database can then close itself.
This should be possible using only Windows scheduler and an MDB file.

 

[TKnight]

Thanks Lagbolt,

I have done that before but I thought there were some issues around repeatedly opening the Access.exe? I could potentially be opening it up to 500 times a day.

If there are no issues around that then it is the simplest way. I think i'm suffering from a classic Access inferiority complex where any solution in anything other than Access appears to be better - damn our IT department, they're obviously starting to rub off on me!

Cheers,

Tom