CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2011
    Posts
    4

    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.

  2. #2
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    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
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  3. #3
    Join Date
    Apr 2011
    Posts
    4

    Re: MySQL connection through a DLL

    Quote Originally Posted by dglienna View Post
    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)

  4. #4
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    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.
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  5. #5
    Join Date
    Apr 2011
    Posts
    4

    Re: MySQL connection through a DLL

    Quote Originally Posted by dglienna View Post
    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

  6. #6
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    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.
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  7. #7
    Join Date
    Apr 2011
    Posts
    4

    Re: MySQL connection through a DLL

    Quote Originally Posted by dglienna View Post
    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

  8. #8
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: MySQL connection through a DLL

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

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  9. #9
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    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
  •  





Click Here to Expand Forum to Full Width

Featured