[SOLVED]Excel, VBA to MySQL?-VBForums
Results 1 to 5 of 5

Thread: [SOLVED]Excel, VBA to MySQL?

  1. #1

    Thread Starter
    Member
    Join Date
    May 2010
    Posts
    33

    Resolved [SOLVED]Excel, VBA to MySQL?

    Hello Everyone,

    I have been looking the last half an hour for help & tried varies things to get this working, but no luck. So I hope I can be helped with a new thread.

    I want to say previously that I learned VBA "on the go" and thus am really a "newbie with a bit more knowledge".
    I have however worked with Excel a lot I used MySQL before and PHP and other Web Application languages.

    Well on to the actual topic:

    I am working for a company that has been running for half a year now and was using a pile of Excel Workbooks to store clients, calculate commissions, make reports and so on. Now, I am not a big fan of using Excel as a big database, however it is good for making calculations and reports. Therefore as we will be having a Server running MySQL in the next couple of Weeks I am preparing for the change of the Excel sheets and whatever is necessary.

    So, I set up on my PC a MySQL database. Which was working fine already set up the tables. Now what I wanted to try out if I can input all the client data from the excel sheet into the MySQL database and in reverse using Excel to get necessary data from the MySQL database & then calculating it for reports or whatever in Excel.

    My Questions: (finally) a) Would you say this is the right way of doing so? (As a temporary solution until we will change completely web-based app)
    b) Using ODBC-5.1.6 Connector I was unable to connect to the database using Excel 2007 with the Error that the Structure of the DSN does not match control and application (Sorry have to translate from spanish..) - any clues why that would be?
    c) Using a VBA script I tried connecting via ADO or the ActiveX library however I keep getting a runtime error..

    The code is here:
    Code:
    Option Explicit
    Dim con As ADODB.Connection
    Dim rs As ADODB.Recordset
    
    Private Sub UserForm_Initialize()
    Set con = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    con.ConnectionString = "server=localhost" & _
                          ";driver=MySQL 5.1 driver;db=astramedia;" & _
                          "uid=root;pwd=password;" & _
                           "Option=16386"
    con.CursorLocation = adUseClient
    con.Open
    end sub
    I have left out the code for the recordset for now as on the error VBA highlights "con.Open" and obviously does not proceed.

    Sorry for the long post.

    Thanks alot for any suggestions & help!
    Last edited by Fr0mi; Jun 30th, 2010 at 05:24 AM. Reason: Solved

  2. #2
    Addicted Member
    Join Date
    Aug 2004
    Location
    Itasca, IL USA
    Posts
    239

    Re: Excel, VBA to MySQL?

    I use an Oracle database and this is how I connect to our DB (except I use Msdaora for my provider)... maybe if you do some changing around it will work for you.

    vb Code:
    1. Dim Conn As ADODB.Connection
    2. Dim rsRec As ADODB.Recordset
    3.  
    4. Set Conn = New Connection
    5.  
    6. Conn.ConnectionString="Provider=MySQLProv;Database=astramedia;Uid=root;Pwd=Password;"
    7. Conn.Open
    8.  
    9. Set rsRec = New ADODB.Recordset
    10. rsRec.CursorType = adOpenKeyset
    11. rsRec.LockType = adLockOptimistic
    12. rsRec.ActiveConnection = Conn

  3. #3

    Thread Starter
    Member
    Join Date
    May 2010
    Posts
    33

    Re: Excel, VBA to MySQL?

    Thanks for the reply Chrissy!

    Unfortunately I continue to get errors. Here is the correct translation of the last error:

    Run-time error '-2147467259(80004005)':

    [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.
    I followed some help on the Internet regarding to create a System DSN and not a User DSN. However this did not change anything and the error message stays the same.

    Using the method suggested by Chrissy i get a different error:

    Run-time error '3706':

    Unable to find the specified provider. It is possible that it has not been installed correctly.
    So, I guessed changing Provider to Driver will give me different result.. And yes it throws me back to exactly the same error message as before (first one quoted)..

    I am running out of ideas..
    Could this be because I am running the Database on the same PC that I want to connect from?

    Regards, thanks for further help!

  4. #4

    Thread Starter
    Member
    Join Date
    May 2010
    Posts
    33

    Re: Excel, VBA to MySQL?

    Okay I think I just solved the problem. Thanks anyway!

    Here I share the fix that worked for me:

    I am running Windows 7 64 Bit system and thus downloaded all the ODBC Connectors for the 64 Bit system. However using this I was unable to connect Excel to the MySQL Database & I was unable to run a macro that would connect to the Database. Yet, on Administrator Tools the ODBC Driver always created a successful connection to the database - so I thought the MySQL ODBC 5.1 Driver would be fine to use.

    However it seems on a 64 Bit system you have two Connectors one 32 Bit and one 64 Bit. The file "odbcad32.exe" lies in "C:\Windows\SysWOW64\" - You will realise that there is no MySQL ODBC Driver listed.
    Now, to basically fix all this you have to also download the ODBC Connector 32Bit and install it. Then configure the driver to your Database. You will realise that the driver will not be a 5.1 instead 3.51 - Which you will need to change in your VBA script as well. Then it should work.

    Link to the ODBC Connector here:

    http://dev.mysql.com/downloads/connector/odbc/3.51.html

    Best regards, thanks for the help anyway!
    I hope the next person running into this problem will have the solution a hell of a lot faster then I did!

  5. #5
    New Member
    Join Date
    May 2012
    Posts
    1

    Re: [SOLVED]Excel, VBA to MySQL?

    Hey guys, How can i connect Excel-vba to mysql database.Plz send me suitable 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
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.