Examples of Using VBA, ODBC, and mysql

  • Thread starter Thread starter bwreath
  • Start date Start date
B

bwreath

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
 
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)
 
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(Array( _
"ODBC;DSN=myquery;DESC=MySQL ODBC 3.51 Drive
DSN;DATABASE=mydatabase;SERVER=123.456.78.90;UID=myacc;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
 
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 ...
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.
Click to expand...

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.
Click to expand...

This sounds like it could be achieved using a JOIN or perhaps a
sub-query in SQL. Post some more details.

Jamie.

--
 
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
 
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)
 
Back
Top