Excel and MYSQL

[White_Nova]

Hi All

I have become pretty good at VBA and access.

I have now migrated my database over to MySQL.
I am however having a little problem with the SQL statement in VBA:

Please advise what im doing wrong?
I was instructed to download a driver to connect to Mysql (MySQL ODBC 3.51 Driver)

My code looks as follows:

[VBA]Sub excelmysql()
'-------------------------------------------------------------------------

Dim conn As New ADODB.Connection
Dim server_name As String
Dim Database_Name As String
Dim User_id As String
Dim password As String
Dim sqlstr As String ' SQL to perform various actions
Dim rs As ADODB.Recordset


'----------------------------------------------------------------------
' Establish connection to the database
server_name = "127.0.0.1"
Database_Name = "Store" ' Enter your database name here
User_id = "UserID" ' enter your user ID here
password = "PASSWORD" ' Enter your password here

Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
conn.Open "DRIVER={MySQL ODBC 3.51 Driver}" _
& ";SERVER=" & server_name _
& ";DATABASE=" & Database_Name _
& ";UID=" & User_id _
& ";PWD=" & password _
& ";OPTION=16427" ' Option 16427 = Convert LongLong to Int: This just helps makes sure that large numeric results get properly interpreted


Sheets("Sheet1").Select
Range("BK2:BL500").ClearContents

sqlstr = "'SELECT * FROM [Bookings]" & _
" WHERE [Bookings].[Team]='" & Range("A2").Value & "'"

rs.Open sqlstr, conn


Dim xlSht As Excel.Worksheet
Set xlSht = Sheets("Sheet1")
xlSht.Range("A10").CopyFromRecordset rs


rs.Close
conn.Close
Set rs = Nothing
Set adoconn = Nothing
Set xlSht = Nothing

End Sub
[/VBA]

Please tell me what im doing wrong.

[White_Nova]

Alright, i have managed to sort it out to pull the data back to excel.
The next problem im having is that it is not arranging the info underneath each other.
It arranges it with huge gaps in between each lines of data.
Is there a way to arrange them so they are underneat each other? (my guess would be in the SQL statement?)

[VBA]Sub excelmysql()
'-------------------------------------------------------------------------

Dim conn As New ADODB.Connection
Dim server_name As String
Dim Database_Name As String
Dim User_id As String
Dim password As String
Dim sqlstr As String ' SQL to perform various actions
Dim rs As ADODB.Recordset


'----------------------------------------------------------------------
' Establish connection to the database
server_name = "127.0.0.1"
Database_Name = "Store" ' Enter your database name here
User_id = "Russelld" ' enter your user ID here
password = "mainframe" ' Enter your password here

Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

conn.Open "DRIVER={MySQL ODBC 3.51 Driver};" & _
"SERVER=127.0.0.1;" & _
"DATABASE=Store;" & _
"USER=Russelld;" & _
"PASSWORD=mainframe;"


Sheets("Sheet1").Select
Range("BK2:BL500").ClearContents

'sqlstr = "SELECT * FROM [Bookings]" & _
'"WHERE ([Bookings.[Team]='FLC')"


sqlstr = "SELECT * FROM Bookings WHERE (Store.Bookings.Team= '" & Range("A2").Value & "')"

rs.Open sqlstr, conn


Dim xlSht As Excel.Worksheet
Set xlSht = Sheets("Sheet1")
xlSht.Range("A10").CopyFromRecordset rs


rs.Close
conn.Close
Set rs = Nothing
Set adoconn = Nothing
Set xlSht = Nothing

End Sub[/VBA]

[White_Nova]

Seems i should open my own forum cuase i find the answer quicker than people post in this place ;-)

Here is the answer:

[VBA]Sub excelmysql()
'-------------------------------------------------------------------------

Dim odjDB As New ADODB.Connection
Dim server_name As String
Dim Database_Name As String
Dim User_id As String
Dim password As String
Dim sqlstr As String
Dim rs As ADODB.Recordset
Dim oRS, nRec, oFld
Dim Row


'----------------------------------------------------------------------

Set odjDB = New ADODB.Connection

Sheets("Sheet1").Select

odjDB.Open "DRIVER={MySQL ODBC 3.51 Driver};" & _
"SERVER=127.0.0.1;" & _
"DATABASE=Store;" & _
"USER=Russelld;" & _
"PASSWORD=mainframe;"

If



Set oRS = odjDB.Execute("SELECT bookings.team,sum(CountOfLeadno) as Leadcount FROM Store.Bookings WHERE (Store.Bookings.Team= '" & Range("A2").Value & "') and store.bookings.SesDate between ('" & Range("A1").Value & "') and ('" & Range("B1").Value & "') and store.bookings.TMCode = ('" & Range("B2").Value & "') Group By store.bookings.Team")

nRec = 0
Row = 4

Do While Not oRS.EOF
For Each oFld In oRS.Fields

Worksheets("Sheet1").Cells(Row, 3).Value = oRS("Team")
Worksheets("Sheet1").Cells(Row, 4).Value = oRS("Leadcount")

Row = Row + 1
On Error Resume Next
oRS.MoveNext

Next
Loop

oRS.Close
odjDB.Close

End Sub
[/VBA]

[XLGibbs]

Sorry..the primary purpose of the forum is VBA and such...I am normally scoping out the SQL forum....you could have posted this in Excel forum (or al ink to this post in the excel forum and maybe gotten more "hits"
.

You got the hang of it, nice work!