Connect to MS SQL server through Excel VBA

[lroca]

I am confused on how to set up my credentials in order to connect and extract data from MS SQL Server using Excel VBA. I know this is possible with an ADO connection, I am not sure of how to setup my code to access my sql database.

Can someone please send me a simple code to access my SQL database and place the information in cell "A1"?

Here are my credentials and SQL statement:

Server Name: dol-sql\sqldatabase
Database: test
Username: john
Password: smith

SQL:
Select GL_Balance.Fiscal_Year, GL_Balance.Main_Account, GL_Balance.Sub_Account,
Sum(GL_Balance.Year_Start_Value)
From GL_Balance
Group By GL_Balance.Fiscal_Year, GL_Balance.Main_Account, GL_Balance.Sub_Account
Order By GL_Balance.Fiscal_Year, GL_Balance.Main_Account, GL_Balance.Sub_Account

Thanks!

 

[jrikhraj]

I understand that this is very late but maybe some one else can benefit from my reply

Have you tried using an ODBC Connection. I use it to pull in data from SQL DB into Excel without any problems. Attached is a code snipped that can get you started

'--------------------------------------------------------------------
'Setting the network ODBC connections
Dim sConn As String
Dim oQt As QueryTable
Dim sSql As String

'defining the connection string
sConn = "ODBC;DSN=<ODBC DSN NAME>;UID=<User ID>;PWD=<Password>;APP=Microsoft Office 2003;"
sConn = sConn & "WSID=<Workstation name>;DATABASE=<SQL database name>;Network=DBMSSOCN"

sSql = "Write your SQL query here"

Set oQt = Sheet1.QueryTables.Add( _
Connection:=sConn, _
Destination:=Sheet1.Range("A1"), _
Sql:=sSql)

With oQt
.Name = "Query from"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

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

Jaspreet

 

[jrikhraj]

The WSID setting should be something like this.. for some reason I am not able to edit my reply there
WSID=<Machine Name>

 

[jrikhraj]

Okay. For some reason I am not able to write "Machine Name" in the 'WSID' setting. Also, I am not able to delete my posts. Moderator, can you help?

 

[Jon von der Heyden]

Hi

Try placing the string in code tags (see bottom of my signature).

 

[lroca]

I found an excellent code that enabled me to conect to MySQL database and paste the report on any cell.


Sub GetDataFromADO()

'Declare variables
Set objMyConn = New ADODB.Connection
Set objMyCmd = New ADODB.Command
Set objMyRecordset = New ADODB.Recordset

'Open Connection
objMyConn.ConnectionString = "Provider=SQLOLEDB.1;Password=1234;Persist Security Info=True;User ID=Test;Initial Catalog=TestData;Data Source=m:\testserver;"
objMyConn.Open

'Set and Excecute SQL Command
Set objMyCmd.ActiveConnection = objMyConn
objMyCmd.CommandText = "Select Account.Main_Account, Account.Sub_Account, Account.Name From Account"
objMyCmd.CommandType = adCmdText
objMyCmd.Execute

'Open Recordset
Set objMyRecordset.ActiveConnection = objMyConn
objMyRecordset.Open objMyCmd

'Copy Data to Excel
ActiveSheet.Range("A1").CopyFromRecordset (objMyRecordset)

'Close Connection
objMyConn.Close

End Sub

• If you get a user defined error, make sure you have the correct refferences checked:
  
  Menu: Tools/References (Microsoft ActiveX Data Objects 2.8 Library)