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:
I have left out the code for the recordset for now as on the error VBA highlights "con.Open" and obviously does not proceed.
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;" & _
con.CursorLocation = adUseClient
Sorry for the long post.
Thanks alot for any suggestions & help!