interaction with excel and ms SQL (retrieval of records from sql intoa sheet)

  • Thread starter matthijsdezwart
  • Start date
M

matthijsdezwart

Hi,

I used to use this vba script in excel to get data from a mySQL
Database.

=======================================================

Sub databaseophalen()

'-------------------------------------------------------------------------
' Connection variables
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

' Table action variables
Dim sqlstr As String ' SQL to perform various actions
Dim rs As ADODB.Recordset

'----------------------------------------------------------------------
' Establish connection to the database
server_name = "nameOfTheServer" ' Enter your server name here - if
running from a local computer use 127.0.0.1
database_name = "nameOfTheDatabase" ' Enter your database name here
user_id = "userID" ' enter your user ID here
password = "Password" ' Enter your password here

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

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

Set rs = New ADODB.Recordset
sqlstr = "select * from database"
rs.Open sqlstr, Conn, adOpenStatic
With Worksheets("Current_previous_settings").Cells(2, 1) ' Enter your
sheet name and range here
..ClearContents
..CopyFromRecordset rs
End With

'-----------------------------------------------------------------------
' Close connections
On Error Resume Next
rs.Close
Set rs = Nothing
Conn.Close
Set Conn = Nothing
On Error GoTo 0

MsgBox ("done")

End Sub


=======================================================



Now I need to interact with a MS SQL database, but I would like to do
that in a similar way, so that the results are outputted in a cell
(actually it start at that cell and fills all cells below till
finished).

Can someone help me out how to connect to MS SQL with VBA / EXCEL. I
can't seem to find a tutorial.
Regards,

Matthijs
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top