Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
interaction with excel and ms SQL (retrieval of records from sql intoa sheet)
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
interaction with excel and ms SQL (retrieval of records from sql into a sheet)
You need a different connection string. See
http://www.carlprothman.net/Default....erForSQLServer -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
interaction with excel and ms SQL (retrieval of records from sqlinto a sheet)
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Split text file into Excel sheet and separate the final results intoa new sheet | Excel Worksheet Functions | |||
user interaction to select a sheet | Excel Programming | |||
Using vb to paste records from 1 sheet, to related records in another | Excel Programming | |||
Copy Records from Access Database table(more than 5 lakh records in this table) to Excel Sheet | Excel Programming | |||
Retrieval of details in excel sheet | Excel Programming |