-
April 25th, 2011, 03:12 PM
#1
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.
-
April 25th, 2011, 09:24 PM
#2
Re: MySQL connection through a DLL
As soon as the function ends, the object is destroyed. Just GET the credentials from the DLL, and open it normally
-
April 26th, 2011, 12:46 AM
#3
Re: MySQL connection through a DLL
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)
-
April 26th, 2011, 07:43 AM
#4
Re: MySQL connection through a DLL
Look up STORED PROCEDURES. That might be what you are trying to do. Create the DB actions on the DB Server and execute them.
-
April 26th, 2011, 01:56 PM
#5
Re: MySQL connection through a DLL
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
-
April 26th, 2011, 02:08 PM
#6
Re: MySQL connection through a DLL
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.
-
April 28th, 2011, 12:20 AM
#7
Re: MySQL connection through a DLL
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
-
April 29th, 2011, 10:41 AM
#8
Re: MySQL connection through a DLL
Store the CREDENTIALS in the DLL, but make the connection when you need it.
-
May 3rd, 2011, 11:55 PM
#9
Re: MySQL connection through a DLL
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.
Always use [code][/code] tags when posting code.
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|