[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

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

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
1 Solution

From: http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForSQLServer

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

oConn.Open "Provider=sqloledb;" & _
           "Network Library=DBMSSOCN;" & _
           "Data Source=xxx.xxx.xxx.xxx,1433;" & _
           "Initial Catalog=myDatabaseName;" & _
           "User ID=myUsername;" & _
- "Network Library=DBMSSOCN" tells OLE DB to use TCP/IP rather than
   Named Pipes (Q238949)
- xxx.xxx.xxx.xxx 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

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

GopsolutionAuthor Commented:
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.


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.

Recommend No Refund unless Gopsolution post his solution.

PAQed with no points refunded (of 500)

Community Support Moderator

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

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