VBA connection string to google Cloud SQL

[khelza]

Hi guys,

So I have a macro that pulls data from a mySQL table from a database on localhost server.

I have another mySQL server which has been successfully connected to a cloud SQL instance in the Google Developers Console.

I would like to take the existing macro I have and rather than pull data from the localhost table, I would like it to pull from the Google server.

The macro code is as follows:

Public cn As ADODB.Connection

Sub ADOExcelSQLServer()


    Dim Server_Name As String
    Dim Database_Name As String
    Dim User_ID As String
    Dim Password As String
    Dim SQLStr As String
    Server_Name = "localhost" ' Enter your server name here
    Database_Name = "materialsDB" ' Enter your database name here
    User_ID = "rickastley" ' enter your user ID here
    Password = "nevergunnagiveyouup" ' Enter your password here
    SQLStr = "SELECT * FROM materials" ' Enter your SQL here
    Set cn = New ADODB.Connection
    cn.Open "Driver={MySQL ODBC 5.3 Unicode Driver};Server=" & Server_Name & ";Database=" & Database_Name & _
    ";Uid=" & User_ID & ";Pwd=" & Password & ";"
    
End Sub

It is not as simple as changing Server_Name = to GoogleServer (as I've named it in mySQL) or the direct IP address
If I do, I get an error on the cn.Open line saying cannot find server.

For the localhost configuration, I had to check off [Microsoft Active X Data Objects 2.x library] in VBA Reference.
If there a specific one I need to check off to connect to the Google server? ...something about JDBC?

Any help would be appreciated!

 

[khelza]

So I realized that I did not configure my ODBC driver for the GoogleServer connection. Now that it's properly configured to the right server address, my code now errors out complaining about "unknown database" where before it was saying unknown server.

I think it's syntax in the way I'm specifying the server or database

I have it now as:
<code style="font-family: monospace, monospace; margin: 0px 2px; border: 0px; border-radius: 2px; display: block; font-size: 1em; line-height: 1.42857142857143em; padding: 0px !important; background-color: transparent;">

[FONT=Segoe UI]Public cn As ADODB.Connection[/FONT]</code><code style="font-size: 1em; font-family: monospace, monospace; margin: 0px 2px; border: 0px; border-radius: 2px; display: block; line-height: 1.42857142857143em; padding: 0px !important; background-color: transparent;">Sub ADOExcelSQLServer()
' >Tools References and check Microsoft Active X Data Objects 2.x library

Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim SQLStr As String
Server_Name = "xxx.xxx.xxx.xxx" ' IP Address for GoogleServer goes here
Database_Name = "GoogleDB" ' Enter your database name here
User_ID = "rickastley" ' enter your user ID here
Password = "nevergunnagiveyouup" ' Enter your password here
SQLStr = "SELECT * FROM materials" ' Enter your SQL here
Set cn = New ADODB.Connection
cn.Open "Driver={MySQL ODBC 5.3 Unicode Driver};Server=" & Server_Name & ";Database=" & Database_Name & _
";Uid=" & User_ID & ";Pwd=" & Password & ";"

</code><code style="font-family: monospace, monospace; margin: 0px 2px; border: 0px; border-radius: 2px; display: block; font-size: 1em; line-height: 1.42857142857143em; padding: 0px !important; background-color: transparent;">End Sub

</code>According to SQL Server connection strings - ConnectionStrings.com
I think it's supposed to be "Connection to a SQL Server Instance"

<code style="font-family: monospace, monospace; margin: 0px 2px; border: 0px; border-radius: 2px; display: block; font-size: 1em; line-height: 1.42857142857143em; padding: 0px !important; background-color: transparent;">

Server=myServerName\myInstanceName;Database=myDataBase;User Id=myUsername;Password=myPassword;

</code><code style="font-family: monospace, monospace; margin: 0px 2px; border: 0px; border-radius: 2px; display: block; font-size: 1em; line-height: 1.42857142857143em; padding: 0px !important; background-color: transparent;">
</code>Because on google developers console, my database is listed on a Cloud SQL instance.

I have been unable to modify the connection string so that it accepts my instance "mix-845:test-db" (mix-845 is Authorized App Engine Application)
Such as:

[FONT=monospace]Server_Name = "xxx.xxx.xxx.xxx\[/FONT][FONT=Segoe UI]mix-845:test-db[/FONT][FONT=monospace]"[/FONT]

My info is as follows:
Server: GoogleServer (xxx.xxx.xxx.xxx)
Instance: mix-845:test-db
Database: GoogleDB

 

[khelza]

Turns out it was a matter of case-sensitivity.

My database in GoogleServer is actually googleDB, not GoogleDB.

Once fixed, the code works.

 

[JannieM]

Turns out it was a matter of case-sensitivity.

My database in GoogleServer is actually googleDB, not GoogleDB.

Once fixed, the code works.

Hi Khelza,exactly how did you configure your ODBC driver. I have exactly the same problem.