MySQL connection through a DLL

Hello
I was using Excel-VBA to connect to a MySQL database with ADO in this way:

Dim conn As ADODB.Connection
conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" & _
"SERVER=" & myServer & ";" & _
"DATABASE=" & myDatabase & ";" & _
"UID=" & myDBuser & ";PWD=" & myDBpwd & "; OPTION=3"
conn.Open

But I wanted more privacy removing that code from Excel (and the definition of connection constants). My idea was to create a DLL (myconn.dll) with a class called MySQL.cls with this code:

Function DBConnect() As ADODB.Connection
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" & _
"SERVER=" & myServer & ";" & _
"DATABASE=" & myDatabase & ";" & _
"UID=" & myDBuser & ";PWD=" & myDBpwd & "; OPTION=3"
conn.Open
Set DBConnect = conn
End Function

From Excel, I reference myconn.dll (that is located at system32 and registered) and I use this code:
Dim conn As ADODB.Connection
Set procMy = New myconn.MySQL
Set conn = procMy.DBConnect

However, this code does not compile: it shows the error "Compile Error: Object Library Invalid or Contains References to Object Definitions that could not be found" and it marks the word DBConnect in my last line code.

Anybody has any idea about this error? I was already wearching in the web without success. What it should be the best strategy to store my constants for database connection in a safe place?

Juan C.

As soon as the function ends, the object is destroyed. Just GET the credentials from the DLL, and open it normally

Quote:

---

Originally Posted by dglienna

As soon as the function ends, the object is destroyed. Just GET the credentials from the DLL, and open it normally

---

Sorry I do not follow you. I do not know what are the "credentials from the DLL". I Google to find more info and I saw that that task is related to C or C++ DLLs that check user identity.
My idea was easier: just store the connection string in a safe place and do not ask to the Excel users for a username and password to connect to a database they don't even know where it is hosted.

Of course I am open to better ideas, but I thought it was simple enough in that way. Like I said, I cannot even reference the DLL class from my VBA: is there any error in the class code? (by the way I set MultiUse=True)

Look up STORED PROCEDURES. That might be what you are trying to do. Create the DB actions on the DB Server and execute them.

Quote:

---

Originally Posted by dglienna

Look up STORED PROCEDURES. That might be what you are trying to do. Create the DB actions on the DB Server and execute them.

---

I see your point and maybe I will use stored functions, but for now, the problem I face is different: I can not compile the VBA code with a reference to a DLL class using an ADO object. Any idea about the reason for that?

JC

It's not PUBLIC, for one. Also, a function looks like it will close the connection when it's done. The dll should take care of all functions or none.

Quote:

---

Originally Posted by dglienna

It's not PUBLIC, for one.

---

Sorry, I did not understand: what is not PUBLIC? Should the function be public?
And regarding your other point, the function returns a connection object. The purpose of this function is to be used as a single way to connect for other functions that will extract or write data in the database.

JC

Store the CREDENTIALS in the DLL, but make the connection when you need it.

If the function is not public then it is not accessable to objects outside the dll meaning you can't call it from your VBA script.