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

[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

 

[Bob Phillips]

You need a different connection string. See
http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForSQLServer

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[matthijsdezwart]

You need a different connection string. Seehttp://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForSQL...

Hi Bob,

thanks. I'll look into it!
regards,

Matthijs