Download and Import

[AvGuy]

I want to download an mdb file and then import tables from it programmatically. I've finally got Access to wait for the download to complete, but I've run into a new wrinkle. I can't access the downloaded database because the file is locked until I close the workspace where the code is running. Catch 22. Can anyone suggest a scheme that will get me around this tarpit?
Thanks,
AvGuy

 

[Flippertje]

One (dirrrrrrty) way is to let the download be executed by another program. E.g. a VBA compilation or build a VBA code in (eg) Excel.
Another way should ofcourse be possible.. can you post the code?

 

[AvGuy]

Well obviously I had considered shelling out to another application such as a compiled VB program, but that has its own set of complications. I want to stay within the Access application if I can. What I'm trying to do now is use a separate workspace to invoke an mdb file that performs the download and then closes which then releases the lock on the downloaded file. I have the workspace created and the mdb that does the download, I now need to figure a way to run the code in the download mdb from the parent mdb.

What code are you wanting to see? I used the InetTransferlib code(

http://www.hammerdata.com/Newsgrp/modules/mdl0037.htm)

for the download and a timing loop that calls a WinAPI that checks every 3 seconds for the presence of the file being download to force Access to pause until the download completes.

Creation of the new workspace and opening the download mdb are easy:

Dim wrkJet As Workspace
Dim dbs2 As Database
DefaultType = dbUseJet
Set wrkJet = CreateWorkspace("JetWorkspace", "admin", "")
Set dbs2 = wrkJet.OpenDatabase("Download.mdb", True)

On task remaining is launching the code in the Download.mdb from the parent file.

Thanks for looking in

AvGuy

 

[JerryKlmns]

AvGuy

you said you need to import tables from the downloaded mdb. Can't you link to that and get what you need?

 

[AvGuy]

Thanks for your suggestion. Linking is a separate function that depends on server side configuration. Most ISP's don't provide the necessary tools that permit linking and Excel/Text transfer through the Microsoft ISAM's. It's the persistent failure of these functions that's driven me to try and develop a download process.

AvGuy

 

[JerryKlmns]

What I meant was to download the mdb and then link to the database. Not openning it!

 

[AvGuy]

Oh, I see. No, that's not possible, because the file is locked until the workspace where it was created is closed. No difference between linking and importing in that regard.

 

[JerryKlmns]

Using automation?

Dim objAccess As Object

Set objAccess = CreateObject("Access.Application")
objAccess.NewCurrentDatabase "C:\Downloaded.mdb"
objAccess.Run ("YourSubOrFunction")
...
objAccess.Quit
Set objAccess = Nothing

 

[AvGuy]

Probably this won't work - I haven't tried it yet, but I have my doubts. Usually this code is invoked from another application such as Excel or Word. Running it from within Access will, I suspect, simply create the database within the default workspace so the file will still be locked. The creating session (workspace) has to be closed before the lock is removed from the file. But, as I said, I haven't tried it yet so I'm just guessing. It's on my plate for today however.

 

[AvGuy]

Actually this technique does work. However, since I'm trying to do this on client machines that have only runtime Access, it fails; even Microsoft says it won't work with runtime. So back to square one, create a new workspace, start an .mdb file that has the download code in it within the new workspace and activate the code that performs the download and then, finally, close the new workspace. So far all I've found for controlling one Access application from another has to do with retrieving data or running non-action queries. I need someone to fire the code in the other database.

Anyone, please.

 

[evalesthy]

I'm on the run - but saw this post - does this link help you out?

http://www.trigeminal.com/lang/1033/utility.asp?ItemID=8#8

 

[AvGuy]

Evalesthy:
I had a look at his SOON add-in and it is a creative bit of programming. Unfortunatley, he uses the CreateObject method to fire the code in the other application and this method won't work with runtime Access. M/S says to use the GetObject function, but this only permits SQL functions to return data. I had the routine working well using CreateObject and was ready to deploy it before finding that it bombed with runtime Access.