White_Nova
02-20-2008, 12:03 AM
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:
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
Please tell me what im doing wrong.
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:
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
Please tell me what im doing wrong.