Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Examples of Using VBA, ODBC, and mysql
Hi,
I would appreciate very much if someone could provide some examples on how I can capture, manipulate the data once I have made a connection to a mysql database from Excel using VBA using ODBC. I used a macro recorder and I have made a successful connection to my database but I am not really that familiar with how to manipulate the data. Right now I have the data going to the cell A1 in my worksheet. I am interested in capturing the data into a variable or an array and then subsequently feeding the result select statement into another sql statement. I think it would really help if someone could provide some examples o data manipulation after the connection. thanks -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Examples of Using VBA, ODBC, and mysql
Do you get a recordset? If so, then
ary = oRS.GetRows() puts it into an array -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "bwreath " wrote in message ... Hi, I would appreciate very much if someone could provide some examples on how I can capture, manipulate the data once I have made a connection to a mysql database from Excel using VBA using ODBC. I used a macro recorder and I have made a successful connection to my database but I am not really that familiar with how to manipulate the data. Right now I have the data going to the cell A1 in my worksheet. I am interested in capturing the data into a variable or an array and then subsequently feeding the result select statement into another sql statement. I think it would really help if someone could provide some examples of data manipulation after the connection. thanks! --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Examples of Using VBA, ODBC, and mysql
Hi,
thanks for the suggestion. I have two questions actually. 1) This is the code that I have so far and I was wondering how I would involve the concept of recordset. 2) My other question is that I want to use ODBC and not ADO. I would like to know in the Tools References part of Excel what reference should I check off to be able to use all of the ODBC objects and references? Below is the code that I am using so far. thanks very much. With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _ "ODBC;DSN=myquery;DESC=MySQL ODBC 3.51 Drive DSN;DATABASE=mydatabase;SERVER=123.456.78.90;UID=m yacc;PASSWORD=mypasswd;PORT=3306;O _ ), Array("PTION=3;STMT=;")), Destination:=Range("A1")) .CommandText = Array( _ "Select acc_num FROM mytable WHERE acc_id=12121" _ ) .Name = "Query from myquery" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End Wit -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Examples of Using VBA, ODBC, and mysql
Why do you not want to use ADO, it is simple
Dim oConn As Object Dim oRS As Object Dim sSQL As String Dim myArray Set oConn = CreateObject("ADODB.Connection") oConn.CursorLocation = adUseClient oConn.Open "DRIVER={MySQL ODBC 3.51 Driver};" & _ "SERVER=123.456.78.90;" & _ "DATABASE=myDatabase;" & _ "USER=myAcc;" & _ "PASSWORD=myPassword;" Set oRS = CreateObject("ADODB.Recordset") sSQL = "Select acc_num FROM mytable WHERE acc_id=12121" oRS.Open sSQL, oConn, adOpenStatic, adLockOptimistic 'load a worksheet 'load an array my = oRS.GetRows() oRS.Close Set oRS = Nothing oConn.Close Set oConn = Nothing -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "bwreath " wrote in message ... Hi, thanks for the suggestion. I have two questions actually. 1) This is the code that I have so far and I was wondering how I would involve the concept of recordset. 2) My other question is that I want to use ODBC and not ADO. I would like to know in the Tools References part of Excel what reference should I check off to be able to use all of the ODBC objects and references? Below is the code that I am using so far. thanks very much. With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _ "ODBC;DSN=myquery;DESC=MySQL ODBC 3.51 Driver DSN;DATABASE=mydatabase;SERVER=123.456.78.90;UID=m yacc;PASSWORD=mypasswd;POR T=3306;O" _ ), Array("PTION=3;STMT=;")), Destination:=Range("A1")) CommandText = Array( _ "Select acc_num FROM mytable WHERE acc_id=12121" _ ) Name = "Query from myquery" FieldNames = True RowNumbers = False FillAdjacentFormulas = False PreserveFormatting = True RefreshOnFileOpen = False BackgroundQuery = True RefreshStyle = xlInsertDeleteCells SavePassword = True SaveData = True AdjustColumnWidth = True RefreshPeriod = 0 PreserveColumnInfo = True Refresh BackgroundQuery:=False End With --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Examples of Using VBA, ODBC, and mysql
bwreath wrote ...
I would appreciate very much if someone could provide some examples on how I can capture, manipulate the data once I have made a connection to a mysql database from Excel using VBA using ODBC. From your code, it seems you have been successful in connecting and capturing, but how exactly do you want to manipulate your data? Once it's in Excel you can manipulate the data using, well, Excel (copy+paste, cell formulas, VBA code, etc). You can manipulate the data before it gets to Excel by changing the SQL query. subsequently feeding the result select statement into another sql statement. This sounds like it could be achieved using a JOIN or perhaps a sub-query in SQL. Post some more details. Jamie. -- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Examples of Using VBA, ODBC, and mysql
thanks for the replies.
I will tidy up the sql statement but first I want to make a successful connection with ADO I copied and pasted the code and got an automation error, unspecified error -2147467259(80004005). I have narrowed it down to the line oRS.Open sSQL, oConn, adOpenStatic, adLockOptimistic which seems to be causing the error? I would like to what the disadvantages and advantages are between using ODBC and ADO. It's not really clear to me if you use one or the other or if ADO is used in conjunction with ODBC. Also, I would like to know if there are some references with use ADO and mysql. thanks very much -- Message posted from http://www.ExcelForum.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Examples of Using VBA, ODBC, and mysql
ADO is just an access layer between the client and the database server. In
the example I gave, the ADO is using ODBC. MySQL also supports an OLEDB driver, which will most likely be more efficient than the ODBC driver, and more flexible As for you error, just try oRS.Open sSQL, oConn -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "bwreath " wrote in message ... thanks for the replies. I will tidy up the sql statement but first I want to make a successful connection with ADO I copied and pasted the code and got an automation error, unspecified error -2147467259(80004005). I have narrowed it down to the line oRS.Open sSQL, oConn, adOpenStatic, adLockOptimistic which seems to be causing the error? I would like to what the disadvantages and advantages are between using ODBC and ADO. It's not really clear to me if you use one or the other or if ADO is used in conjunction with ODBC. Also, I would like to know if there are some references with use ADO and mysql. thanks very much. --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
xls to mysql | Excel Discussion (Misc queries) | |||
Changing ODBC server (MySQL) | Excel Worksheet Functions | |||
MySQL and excel | Excel Discussion (Misc queries) | |||
mysql backend | Excel Programming | |||
How to skip MySQL ODBC Driver Promp-up window? | Excel Programming |