Direct Connect to mysql

[dcanham]

I find myself in an odd position. I'm on a Win2K install under Citrix. I'm trying to make a connection to a mysql database that I have put into place. For various reasons I'm not able to install the ODBC driver. I'm trying to make a data connection through VBA (Excel). All the examples out there for this involve using a DSN. Is there a way to kludge a direct connection to mysql through VBA? At best I was hoping there was a mysql library, similar to what dhRichClient (sqlite) provides, that I could reference through Excel. Or, at worst a way to manually load the ODBC driver outside of a windows installation. I am able to run mysqld. Any thoughts out there? Thanks in advance for any help.

[Bob Phillips]

See http://www.carlprothman.net/Default.aspx?tabid=90#ODBCDriverForMySQL

[stanl]

For various reasons I'm not able to install the ODBC driver.

Does this mean you get an error when you try? Or, is it a rights issue? What about ADO and the MySQL Provider?

[dcanham]

Thanks for responding xld, I assume you're referring to this:



To connect to a remote database
oConn.Open "Driver={mySQL};" & _
"Server=db1.database.com;" & _
"Port=3306;" & _
"Option=131072;" & _
"Stmt=;" & _
"Database=mydb;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"

...this still requires an ODBC driver be installed for this connection string to work.

[dcanham]

Does this mean you get an error when you try? Or, is it a rights issue? What about ADO and the MySQL Provider?

Hi stanl, the issue is a hostile IT department at corporate, and me at another plant receiving directives from my plant manager not caring that the IT department doesn't like me. So, I have no admin privileges allowing me to install a MySQL driver for all the DSN connection options I've been able to find. What I was hoping for, was that someone knew about a preexisting MySQL library I could reference for Excel. Similar to the SQLite library created called dhRichClient3.dll Or, maybe a way to "Direct Connect" to the database without having to first install a driver. Thanks for taking the time to reply.

[stanl]

Hi stanl, the issue is a hostile IT department at corporate, and me at another plant receiving directives from my plant manager not caring that the IT department doesn't like me. So, I have no admin privileges allowing me to install a MySQL driver for all the DSN connection options I've been able to find. What I was hoping for, was that someone knew about a preexisting MySQL library I could reference for Excel. Similar to the SQLite library created called dhRichClient3.dll Or, maybe a way to "Direct Connect" to the database without having to first install a driver. Thanks for taking the time to reply.

So this assumes you have the drivers installed locally. Not sure how it works in the Citrix environment, but, again assuming the corporate site has a firewall, you would need port 3306 opened for your plant IP. Where I am we use SQL Server and open port 1433 for remote IP's -

Of course, that begs the question of a hostile IT department and makes it a political not a technical issue.

Sorry I cannot be of more help, but I think this clears up your issue for XLD.

[dcanham]

So this assumes you have the drivers installed locally. Not sure how it works in the Citrix environment, but, again assuming the corporate site has a firewall, you would need port 3306 opened for your plant IP. Where I am we use SQL Server and open port 1433 for remote IP's - The main problem again is the driver, the port is open and the test database is in place at a common area. At my plant, we log into a VMWare instance of windows hosted remotely at corporate through Citrix. So, I have no power for anything that needs admin privileges aka: installing drivers. Even if I did, the drivers would be erased the next time the VMWare instance was "rebooted", so every instance would have to be updated with the driver. There are a lot of people using the Excel apps I've put together (only programmable interface I have access to), including people at other plants. Many of the sheets use an .mdb Access file as a data backend right now. There are limitations to that like performance!!, data centralization, server side triggers, unfriendly multiuser setup, etc.
Of course, that begs the question of a hostile IT department and makes it a political not a technical issue.
Sorry I cannot be of more help, but I think this clears up your issue for XLD.I'm told they are hostile because people from the other plants are asking IT why they aren't doing the things we are doing at our plant. They don't want anything on the system that wasn't done by them, but they refuse to do anything. So, people usually end up doing an end-run around them. I'm not the only heretic, but that doesn't make it any easier on me though. Sorry for the long rambling post, but I feel the occasional need to vent. :)

[stanl]

the drivers would be erased the next time the VMWare instance was "rebooted", so every instance would have to be updated with the driver.

I think there is a way around that, more of a permanent link. I read somewhere you could use port 8081 and bind the mysql server to the localhost on VmWare:dunno dunno, but I have a couple of experts I can ask on Monday.

[dcanham]

Perhaps there would be a way to put the driver files locally in the common area and force a reference to them manually rather than having the driver loaded from the "environment" level?

[stanl]

Perhaps there would be a way to put the driver files locally in the common area and force a reference to them manually rather than having the driver loaded from the "environment" level?

Again, correct me if I'm not seeing the picture you are describing - but I assume this 'common area' is a user mapping on a server where MySQL is installed, so this assumes the drivers are already loaded on that server. If that is the case, then all the VMWare needs is a permanent link - a mapping to the common area with full rights to a file (Excel or Access) that links to MySQL via the drivers already loaded on the Server. That particular file would utilize a DNS-less connection which may or may not be port specific.:bug:

[dcanham]

Again, correct me if I'm not seeing the picture you are describing - but I assume this 'common area' is a user mapping on a server where MySQL is installed, so this assumes the drivers are already loaded on that server. If that is the case, then all the VMWare needs is a permanent link - a mapping to the common area with full rights to a file (Excel or Access) that links to MySQL via the drivers already loaded on the Server. That particular file would utilize a DNS-less connection which may or may not be port specific.:bug:
Sorry, didn't mean to be confusing. The common area is just a large network drive where everyone can host files for everyone to use. The mysql database has an no-install option where you just copy the files and tools needed. However, There is no mysql ODBC driver installed on VMWare and I can't install one because I have no admin privileges. Every connection option I've seen for Excel to mysql requires an ODBC driver be installed. I'm looking for a way around that.

[SWE321]

I have a similar problem. Is it possible to use a range of ports instead?


Thanks for responding xld, I assume you're referring to this:



To connect to a remote database
oConn.Open "Driver={mySQL};" & _
"Server=db1.database.com;" & _
"Port=3306;" & _
"Option=131072;" & _
"Stmt=;" & _
"Database=mydb;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"

...this still requires an ODBC driver be installed for this connection string to work.

[stanl]

The mysql database has an no-install option where you just copy the files and tools needed.

So MySQL is not really a database, just files and tools:banghead: . Sounds fubar.

[dcanham]

I have a similar problem. Is it possible to use a range of ports instead?
Microsoft built all the mdb functionality into the windows drivers and the sqlite code I use has the driver functionality built into the dhRichClient3.dll library the very kind developers provided. Otherwise, to the best of my knowledge, you need to have a driver installed to use any of those mysql connection strings. The example you linked to is unclear to me what back-end they are using to retrieve the data. It appears to be generic ADO code that refers to unknown functions. I have working code for .mdb and SQLite databases under Excel so I have worked with this before. I'm just trying to find a way to link to mysql without having to install a driver.

[dcanham]

So MySQL is not really a database, just files and tools:banghead: . Sounds fubar.
uhhhh.....mysql is an enterprise level open source database. It is a close competitor to Oracle and SQLServer. It is a very effective alternative for those not wanting to pay the ridiculous $$$ associated with either of those other choices. If you do any database work, you may want to become familiar with it. There are major companies that use it as their database of choice.

http://www.mysql.com/

[stanl]

uhhhh.....mysql is an enterprise level open source database. It is a close competitor to Oracle and SQLServer. It is a very effective alternative for those not wanting to pay the ridiculous $$$ associated with either of those other choices. If you do any database work, you may want to become familiar with it. There are major companies that use it as their database of choice.

You missed my point. You wrote that a NO-Install option was used, so I questioned whether MySQL was really installed or just the files (like you wrote). If MySQL is installed, the drivers are installed. It sounds like your IT dept does not want anyone accessing the db from a remote location, maybe with good reason. Generally a web-interface is preferred (we use either IIS or Ruby on Linux to get data from either our SQL Server or PostGRE db's) for remote access. If you are after read-only queries to create Excel reports... etc. then perhaps you should see if the IT folks will permit disconnected recordsets.

[dcanham]

You missed my point. You wrote that a NO-Install option was used, so I questioned whether MySQL was really installed or just the files (like you wrote). If MySQL is installed, the drivers are installed. It sounds like your IT dept does not want anyone accessing the db from a remote location, maybe with good reason. Generally a web-interface is preferred (we use either IIS or Ruby on Linux to get data from either our SQL Server or PostGRE db's) for remote access. If you are after read-only queries to create Excel reports... etc. then perhaps you should see if the IT folks will permit disconnected recordsets. I thought you were questioning how I could "install" mysql and not be able to install drivers. I need full fledged read/write capabilities. You will have to accept my word, having done computer consulting in the past, that the IT situation is as described.

[SWE321]

The best thing to do is to get the odbc connection to work. This might be a long shoot but you could test record a connection and view the code that is generated and tweak it for your purpose. (If you want to make alot of connections to mysql php is the way to go.)