VBA ODBC Connection through proxy

[munkee]

Hi all,

Been a long time since I asked a question on here so here goes.

I am trying to connect to a mysql database to get some data using an odbc connection. However the problem I have is that we use a corporate firewall. I have been given a username / password / IP / port that I can use to connect through the proxy however I have no clue how to set up my connection to do this.

The setup is as follows:

MS Access -> Proxy -> Internet -> MySql server

Has anyone seen any method of doing this?

[munkee]

Seems this is a none starter after some further researching

http://bytes.com/topic/access/answers/478275-need-info-books-articles-code-etc-access-vba-xml-mysql-via-http

[NeoPa]

I would say this is possible using a VPN (Virtual Private Network). However, I have found the performance of Access across any sort of WAN to be horrible. Access requires all packets to get through essentially, which is often not a level of reliability supported by WANs (and let's be fair - going via the internet has to be considered as a low-end WAN at best).

If you feel it's wise to progress this I would suggest the networking forum is where you should be asking for help. I'd be happy to move it for you if that's what you'd like.

[munkee]

I have decided purely for testing to run a tunnel similar to your suggestion using hamachi. I have setup up a mysql server on my home laptop which I can leave on to do some WAN test from work.

I am aware of the issues around WAN and I am wanting to just experiment to see its impacts to get a good idea what the possibilities are.

I have informed our company that a terminal server will be needed in the short term and likely will end up being our long term solution, but.. its been 6 months with no server =p

[NeoPa]

A Terminal Server, where you connect to another computer that is near to the database and can run from there, is a solution I often find most practicable. As the point you connect to has a better connection to the database than you can get from your own machine, the Access link from there is much more robust.

[munkee]

Thanks for the input NeoPa we have commandeered a old tower pc at work and are using this to host a mysql database that we can do some testing on within our local LAN.

I myself want to purely see how I can utilise ms access front end with mysql back end with some .aspx pages for remote users.

We are also going to try and see the performance benefits of shifting a 4.2million (over 3gb of .txt files) record database from ms access regular split db in to mysql to utilise server side queries (views) which can then be used as linked tables in our front end.

The other test will be then to remote desktop to one of our PCs running a front end to test out the whole setup in a sort of "terminal" environment.


All of this will end up much like your suggestion and hopefully run smoothly when we get the proper server set up by IT

[NeoPa]

Sounds good. The main problem is providing terminals for the number of people wanting to use it. Not a problem unless the number is substantial, depending on how many PCs (or virtual PCs) are available on site with the server.