• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 244
  • Last Modified:

Excel Add-In Connecting to a SQL database


I want to connect to my online SQL database  in my Excel 2007 Add-In in VB.net.
I used the code below to connect via VBA.
How do I change it to connect in VB.net?
What Imports do I use
Sub A()
    Call oAppend(Now, "yet more", 45)
End Sub

Public Sub oAppend(ByVal oDate As Date, ByVal oText As String, ByVal oNumber As Single)
    Dim oSQL As String
    On Error GoTo EH
    Set con = New ADODB.Connection
    con.Open "Provider=SQLOLEDB;Data Source=196.220.411.247,1235;Network Library=DBMSSOCN;Initial Catalog=test;User ID=nmomashhjjk;Password=nnnmmm222;"

    Set cmd = New ADODB.Command

    'Check last ID ---------------------------
    Dim oSQL_lastID As String
    oSQL_lastID = "SELECT MAX(ID) FROM Table1"

    Dim Last_ID_Recordset As Variant
    Dim Last_ID_Integer As Integer
    Dim Next_ID_Recordset As Variant
    Dim Next_ID_Integer As Integer
    Dim intID As String
    With cmd
        .CommandText = oSQL_lastID
        .CommandType = adCmdText
        .ActiveConnection = con
        Last_ID_Recordset = .Execute() 'this line returns a recordset
        Last_ID_Integer = Last_ID_Recordset(0)
    End With

Open in new window

Murray Brown
Murray Brown
  • 2
1 Solution
See connection strings example.
Includes the libraries to import.

This example is helpful as well

Imports System.Data.SqlClient

connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"
        connection = New SqlConnection(connetionString)

Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
thanks very much
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now