4

Objective:

I'm trying to connect to a database (e.g. associated with a website hosted by goDaddy) via VBA; using MS Word. I would like to distribute the VBA code via a word template so that others can also connect to my database.

Current Understanding - Is it correct?

In order to connect to a remote mySQL database I MUST configure a ODBC Data Source using (for example) mySQL Connector/ODBC (available here)?

There seems to be a way to connect without using a DSN as suggested here.

Issue:

I have been trying to use the mySQL Connector tool and am attempting to configure it with the information I have at hand. Steps taken:

  • download connector tool from dev.mysql.com
  • Control Panel > System & Security > Administrative Tools > ODBC Data Sources 64 Bit
  • add host: www.mywebsite.com
  • add user: NameOfDataBaseUser
  • add pwrd: PWForUser

I get the impression that I am using the wrong credentials... I found some documentation that said a list of DataBases would be displayed if connection is successful. That suggests to me that I should be using credentials for a master user - which user would that be?

Disclaimer

I do plan to connect to an online DB via VBA, but suspect that it might be better to connect indirectly via a php web-page.

If anyone has thoughts on this (security, ease of deployment, other) please let me know, it would probably be a new question. Other disclaimer, I am highly INexperienced with databases but keen to learn - slowly ;-)

Improve this question
4
  • Is the database accessible outside of the server? Sometimes hosting providers configure their databases to be only avaible via (e.g.) PHP running on the same server. Commented Nov 9, 2017 at 9:11
  • @tobifasc hmmm.... well that might well be the case, how would I be able to check? Thanks Commented Nov 9, 2017 at 9:34
  • 1
    Unfortunately I've got no experience with GoDaddy but maybe this helps? codingcyber.org/access-godaddy-database-remotely-3356 it looks like you have to allow remote access when creating the database. Commented Nov 9, 2017 at 9:39
  • @tobifasc nearly, it turned out to be a setting outside of cPanel from the host. Adjusting that setting gained me access but access is only permitted from a single IP address so either get a new host or work indirectly via my website. Thanks for your help Commented Nov 9, 2017 at 10:18

4 Answers 4

Reset to default
3

I am currently working on a project with Excel where I am successfully connecting to a remote MySQL database.

I am using the DSN-less approach and this could probably work well for you, too:

Set remoteCon = New ADODB.Connection
conStr = "DRIVER={MySQL ODBC 5.3 Unicode Driver};" & _
    "SERVER=myhomepage.com;PORT=3306;DATABASE=mydb;" & _
    "UID=username;PWD=secret"
remoteCon.Open conStr
remoteCon.Execute ("USE mydb;")

In order for this to work, you also have to add a reference (in VBA backend): Tools > References > Check "Microsoft ActiveX Data Objects x.x Library".
You also need to have the MySQL ODBC Driver (in my case "MySQL ODBC 5.3 Unicode Driver") installed on your computer.

Queries can then be executed like this:

Dim rs As ADODB.Recordset
Set rs = remoteCon.Execute("SELECT * FROM table")
If Not rs.BOF And Not rs.EOF Then
    result = rs.GetRows
End If
Improve this answer
Sign up to request clarification or add additional context in comments.

12 Comments

Cool... um... noob question - how do I get the driver... google just took me to the connector that I already have
Yep, having that installed should work. I seem to remember running into some trouble with x32 and x64. I have a 64-bit computer, but ended up having to install both drivers for some reason - just something to try if you run into problems.
I'm getting Error 10060: as described here: forums.mysql.com/read.php?34,49742,239961 but the solution is not working for me (except I'm not sure I'm following the steps correctly)
Hmm, let's see. Which steps are you not sure about? Do you know if your webserver is configured to accept remote connections?
OK - actually I found the setting buried in my account config settings. I've enabled remote access and appear to have a successful connection (basic issue is solved) BUT it is limited to my IP address so won't meet my needs :-(
|
2

How to connect VBA to a Remote mySQL DataBase using ODBC

Thanks to @EVilliger & @tobifasc for your help with this, there are many 'how to configure mySQL questions' floating around but none that solved my (larger) issue.

Basic problem - my host does NOT allow remote connections to the database, except for from a single white-listed IP (this seems fairly common).

Questions Answered:

It turns out that you do NOT need to configure the mySQL connection using the connector, however you DO NEED to have an appropriate ODBC driver installed. The connector (with the driver) can be found here: https://dev.mysql.com/downloads/connector/odbc/

I uninstalled the mySQL Connector and everything seemed to continue working, until it didn't. Conclusion don't uninstall the mySQL Connector unless you have something to replace it with.

The credentials to use can be for a database user, not some elevated user.

For anyone interested in setting up and experimenting with mySQL from VBA here is a way forward:

  1. Download & install the drivers (see above) - if the install fails you may need to install vcredist_x64.exe
  2. Set-up a free mySQL database, I used HelioHost: https://www.heliohost.org/ (they will also give you a domain)
  3. Create a database & user in HelioHost cPanel
  4. Configure remote access - use wildcard % to allow all IPs
  5. Add user to database
  6. Use the following code to connect...

Code from accepted answer:

Sub connect2OnlineSQL()
    ' Note: add referecne to Microsoft ActiveX Data Objects #.# Library
    '       Tools > References > (scroll down...)
    Set remoteCon = New ADODB.Connection
    conStr = "DRIVER={MySQL ODBC 5.3 Unicode Driver};" & _
        "SERVER=something.heliohost.org;PORT=3306;DATABASE=db_name;" & _
        "UID=db_user;PWD=yourPassWordHere"
    remoteCon.Open conStr
    remoteCon.Execute ("USE db_name;")
End Sub

Thanks again :)

Improve this answer

Comments

0

Following solution worked for me

Prerequisite:

  1. Under Developer ->Tools->Reference Add the relevant Plug in for Oracle

  2. Download and install ODBC Driver version for Oracle which you are using (32bit/64 bit)

  3. Windows search -> ODBC Data Source -> Add Oracle driver under user DSN and System DSN

  4. Copy paste below code which I used for MySQL and change the parameter based on your requirement

Sub ConnectToDB()
 dbName = InputBox("Enter DB Name")
        'Connection To MySQL
        Dim oConn As ADODB.Connection
        Dim str As String
        str = "DRIVER={MySQL ODBC 8.0 Unicode Driver};SERVER=localhost;DATABASE=Employeeportal;PORT=3306;UID=root;PWD=root;"
        Set oConn = New ADODB.Connection
        oConn.Open str
        MsgBox "Connected to MySQL DB"
       
        'Exporting result set to Excel
        Dim query As String
        query = "select * from " & dbName
        Dim recordSet As New ADODB.recordSet
       
        recordSet.Open query, oConn
        Sheet1.Range("A1:D1").CopyFromRecordset recordSet
        oConn.Close
End Sub
Improve this answer

Comments

0

Check your MS Excel/ Office is 32-bit or 64-bit. Install relevant ODBC connecter and remember to include VBE reference "Microsoft ActiveX Data Objects 6.1 Library".

Improve this answer

1 Comment

This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.