Connect DB with IP address-CodePudding

I'm trying to connect my DB through excel macro. the below VBA code I tried but I'm unable to connect to the DB.please assist me.

DB name: abcd
Username: praveen
password: XYZ
host:10.161.0.XX
port: 1519
service name:PRODUCTDB

--------VBA code--------------------------

Dim con As ADODB.Connection
Dim rec As ADODB.Recordset
Set con = New ADODB.Connection`enter code here`
Set rec = New ADODB.Recordset
rec.CursorLocation = adUseClient
con.Open ("Provider=SQLOLEDB;Data Source=10.161.0.00;port=1519;Service name=PRODUCTDB;Initial Catalog=abcd;user ID=praveen; password=XYZ;")

CodePudding user response：

use something like this to generate the connection string

Function ConnStr()
' For this to work we need a reference to
' Microsoft OLE DB Service Component 1.0 Type Library
  '--------------
  'Define variables and objects
  '--------------
  Dim DataLink As DataLinks
  '--------------
  'Create Objects
  '--------------
  Set DataLink = New DataLinks
'display the UDL dialog and put result in the cell
  ConnStr = DataLink.PromptNew()
End Function