Access VBA connect to Web Database?

(Help Please!)

[rccprof]

I have an Access database on a Web server that supports an e-commerce site written in ASP. Now the client wants to run (Select) queries against that Web database from within an existing Access VBA application running on local desktops. I'm fairly experienced in ASP but not in VBA -- and I cannot figure out how to connect to this Web database from within the Access VBA code. Any help from someone who has done this or references to such help would be greatly appreciated!

By the way, the Web server is not on their premises or under their control, so solutions that do not involve anything special on the Web server is preferred. The Web database is typically only accessed from ASP or through FTP.

Thanks!

[Small Website Guy]

You can FTP a copy the Access database to a computer on your local network.

You can set up a VPN so you can access the directory on the remote computer directly (and performance in that case would SUCK).

You can upgrade to a real server based database like SQL Server (and even then, if you are using some kind of shared hosting, your host won't appreciate you running remote queries like that).

To do this, you really need a server under your control that has SQL Server running on it.

This server doesn't have to be on your premises, there are plenty of hosting companies that will rent you your own private server with SQL Server installed for you. (Expect to pay around $500/month.)

[mattur]

As SWG says, FTP'ing the db onto the local network would be required before being able to open it via VBA. You could implement an overnight batch job/scheduled task to download the database every night. The client could then run queries against the database copy, albeit with data one day behind the live data.

Alternatively, you could code the queries/reports the client needs in ASP pages, accessed via a secure "management" part of the website.

HTH

[rccprof]

Sorry to hear this. I was hoping not to have to FTP first but given the options listed, it looks like I'll have to live with that. I'm dealing with office personnel used to just their local app (not to doing FTP) so I'd like to make this as transparent to them as possible. Here are a few follow up questions...

Is there a way to initiate an FTP or at least activate an ASP page from within the Access VBA code? Can you initiate an FTP from within ASP? How was Mattur thinking of getting the automated FTP to occur?

Note that the database in question is pretty small and needs to be downloaded only about every 4 or 5 days but each time it may end up at a different local pc.

Thanks again!

[mattur]

To automate the transfer, you need to do two things:
1. write an script/batch program that connects via FTP to your website and downloads the db.
2. schedule the script/batch program (e.g. using Windows' "Scheduled Tasks")

It would be best to have this run on a central server, saving the db centrally where all local pc's can access it (rather than saving on individual pc's).

A batch program is easy - just make a text file with .cmd extension with all the FTP commands you use to connect and download the db via command line "ftp".

HTH

[rccprof]

Great, that takes care of getting the data from the Web down to the local pc's, thanks.

Just one more thing though. I would also like to be able to make an update to these records on the Web after the batch has been successfully processed locally. I could write an ASP page to do this, but can it be activated from the Access VBA application on the desktop or would the staff have to launch their own browser and do that themselves? I thought it was possible to launch an instance of IE and point it to a specific URL from within VBA but I don't know the facts.

Thanks again!

[mattur]

You can use VBA's Application.FollowHyperlink method to open a URL in the user's browser. Check the docs for syntax.

[raywood]

I'm not a VBA expert, but a long time ago I remember working on a project like this. I thought there was a way to "split" the database in Access. That means putting only the data on a server and the VBA user interface on workstations. Check the Access documentation for how to "split" the database. Maybe that will help.

[rccprof]

Thank you all for your useful replies. I am doing the scheduled FTP downloads successfully and working on the application to read and process that data from within VBA.