Have a Question?

If you have any question you can ask below or enter what you are looking for!



connect sql server from vba

Can we connect SQL Server from VBA?

How many languages can be connected from SQL server?

  Nishi Singh

Hi Nishi,

You can connect VBA(Visual Basic Application) from SQL Server through connectivity code. The code is below mentioned that you need to use in VBA editor. 

'Step 1: Add Reference to Microsoft Active X Data Objects Library

'Step 2: Create the Connection String with Provider and Data Source options ad try below code to get the task done

 

Sub sbADOExample()

Dim sSQLQry As String

Dim ReturnArray

Dim Conn As New ADODB.Connection

Dim mrs As New ADODB.Recordset

Dim DBPath As String, sconnect As String

DBPath = ThisWorkbook.FullName

'You can provide the full path of your external file as shown below

'DBPath ="C:\InputData.xlsx"

sconnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DBPath & ";HDR=Yes';"

'Open the Connection to data source

Conn.Open sconnect

'Create SQL Command String

sSQLSting = "SELECT * From [Sheet1$]" ' Your SQL Statement (Table Name= Sheet Name=[Sheet1$])

mrs.Open sSQLSting, Conn

'=>Load the Data into an array

'ReturnArray = mrs.GetRows

''OR''

'=>Paste the data into a sheet

Sheet2.Range("A2").CopyFromRecordset mrs

'Close Recordset

mrs.Close

'Close Connection

Conn.Close

End Sub


There are so many languages that can be connected to SQL server like .Net Framework, VBA, Python, PHP, JAVA etc.










  Devbrat Tripathi       16 Jun 2016       1       0     

Thanks Devbrat.

  Nishi Singh       16 Jun 2016       0       0     

Post Your Answers:

Please use the CODE HIGHLIGHT Button to format/highlight your codes if any