how do i remotely connect to a sql server database sitting at a particular ip address. in Visual Basic

Posted on 2005-04-28
Last Modified: 2013-11-25
how do i remotely connect to a sql server database sitting at a particular ip address. in Visual Basic
i tried the connection string the code is as follows ...this works for another server at a different IP but is not working for the IP i want
how ever i cant connect to the IP using Terminal Services Client and use the sql server  thru that...please send me a solution  asap
Private Sub Command1_Click()
Dim user As String                       ' User Name
Dim Pass As String                       ' Password
Dim srvName As String                    ' Server Name
Dim dbName As String
user = "sa"
passw = ""
srvName = ""
dbName = "catalystdb"
Set cn = New Connection
cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=" & user & ";Password=" & Pass & ";Initial Catalog=" & dbName & ";Data Source=" & srvName & ""
MsgBox "opened"
End Sub
Question by:Gopsolution
    LVL 29

    Expert Comment



    To connect to SQL Server running on a remote computer (via an IP address)

    oConn.Open "Provider=sqloledb;" & _
               "Network Library=DBMSSOCN;" & _
               "Data,1433;" & _
               "Initial Catalog=myDatabaseName;" & _
               "User ID=myUsername;" & _
    - "Network Library=DBMSSOCN" tells OLE DB to use TCP/IP rather than
       Named Pipes (Q238949)
    - is an IP address
    - 1433 is the default port number for SQL Server.  Q269882 and Q287932
    - You can also add "Encrypt=yes" for encryption

    In addition:

    Dim oConn As ADODB.Connection
    Set oConn = New ADODB.Connection

    LVL 9

    Expert Comment

    Make sure you have the latest MDAC installed

    and make sure that any firewall in front of the server will let trafic through on port 1433

    Good luck


    Author Comment

    dhey thanx a lot for your solutions i got it figured by my own n e ways thanx again i have a new question though
    i'm generating a chart in excel using visual basic the code does not give any error but the chart does not come up :(!!!!!111
    The code is as follows
    Dim XLApp As Excel.Application
      Dim XLwb As Excel.Workbook
      Dim XLws As Excel.Worksheet
      Dim XLChart As Excel.Chart
      Set XLApp = New Excel.Application
      'XLApp.DisplayAlerts = False
      Set XLwb = XLApp.Workbooks.Add("c:\x.xls")
      Set XLws = XLwb.Worksheets(1)
      Set XLChart = XLws.ChartObjects.Add(0, 0, 1312, 236).Chart
      XLChart.ChartType = xlLine
      XLChart.SetSourceData XLws.Range("X3:X11")
      XLChart.HasTitle = True
      XLChart.ChartTitle.Caption = "Chart Test"
    'XLwb.Close True, "c:\test.xls"
      XLApp.Visible = True
    LVL 29

    Expert Comment


    I am glad you were able to solve it.  Please post it so you can get a refund.  You new question should have its own post.  That way it will appear at the top of the list and all experts would get a chance to see it.  If you want me or p_sie to take a look it, you can post a link in this thread, and I am sure we would be happy to help if we can.

    Since this is an Excel question, I would also suggest you post it in the Excel TA.

    LVL 29

    Expert Comment

    Recommend No Refund unless Gopsolution post his solution.

    LVL 1

    Accepted Solution

    PAQed with no points refunded (of 500)

    Community Support Moderator

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
    Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now