VBScript how to set connection string
I'm not sure if this has been discussed before or not, but I have the following code (I changed from the example for an Access database.)
I don't know what to put inside "Provider" and "Data Source" . I am using MS SQL Server 2005 and how can I find this information on my machine? Please, even I am trying to follow from here http://www.connectionstrings.com/ but I still don't know how to do it.
Dim conn, sql
sql = "SELECT * FROM tblOutbox"
Set conn = CreateObject("ADODB.Connection")
With conn
.Provider = "myProvider"
.Mode = adModeReadWrite
.ConnectionString = "Data Source=mysource;" & _
"database=myDbase.mdf; "
.Open
End With
If conn.State = adStateOpen Then
WScript.Echo "Connection was established."
End If
conn.Close
Set conn = Nothing
the access database is used here:
Dim conn
Dim sql
sql = "SELECT * FROM tblOutbox"
Set conn = CreateObject("ADODB.Connection")
With conn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Mode = adModeReadWrite
.ConnectionString = "Data Source= f:/Status.mdb"
.Open
End With
WScript.Echo "Connection was opened."
conn.Close
Set conn = Nothing
WScript.Echo "Connection was closed."
source to share
I finally found a solution thanks to your help. Under Administration Tools, Data Source (ODBS), Driver tab, I am using provider: SQLNCLI, my server: data source, and I add the Recordset rs to the code. It seems that without this the connection is not established. I don't know what happened.
Dim conn , rs, sql, ConnString
sql = "SELECT * FROM tblOutbox"
Set rs = CreateObject("ADODB.Recordset")
Set conn = CreateObject("ADODB.Connection")
With conn
.Provider = "SQLNCLI"
.Mode = adModeReadWrite
.ConnectionString = "SERVER=.\SQLExpress;AttachDbFilename=F:\Test2.mdf;Database=Test2.mdf; Trusted_Connection=Yes;"
.Open
WScript.Echo "Connection was established."
End With
rs.Open sql,conn
If conn.State = adStateOpen Then
WScript.Echo "Connection was established."
Else
WScript.Echo "No Connection ."
End If
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
source to share
With SQL Server, you don't connect directly to the file. Access, which is an RDBMS file system, is slightly different.
Following your example, it will look something like this:
Dim conn, sql
sql = "SELECT * FROM tblOutbox"
Set conn = CreateObject("ADODB.Connection")
With conn
.Mode = adModeReadWrite
.ConnectionString = "Provider=SQLOLEDB;server=[servername];database=[databasename]uid=[insertuser];pwd=[insertpassword];"
.Open
End With
If conn.State = adStateOpen Then
WScript.Echo "Connection was established."
End If
conn.Close
Set conn = Nothing
This is generally a more compact way of doing it, but without context it is difficult to give a better example.
source to share