DB_Connect

VBA-Excel function to connect to SQL Database server, in addition to DB_Disconnect to disconnect from database.
I can not believe I did not add this one here before.
Returns 1 if connection to db was successful, 0 if not.
This is a simpler version of the big guy DB_Connect_AndOr_Fix that will be added too soon.
Edit 2020-03-26: Looks like drivers are not installed by default on all machines, If you are seeing error about Provider not installed, try any of the commented ConnString lines instead.
In all cases, installing the driver would fix it, get SQL Native Client driver from https://www.microsoft.com/en-us/download/details.aspx?id=50402


Public

Tested

My Own Work
Public Function DB_Connect()
' Returns 1 for successful connection, or 0 for not successful
' Variables needed to be set
' Public oConn as object
' Add it to declaration area in module mMain
' DB-Server = "db.server.com"
' DB-Name = "DatabaseName"
' DB-User = "dbadmin"
' DB-Pwd = "pwdofdbuser"
DBServer = SettingRead("DB-Server") '"db.server.com"
DBName = SettingRead("DB-Name") '"DatabaseName"
DBUser = SettingRead("DB-User") '"dbadmin"
DBPassword = SettingRead("DB-Pwd") ' "pwdofdbuser"
DB_Connect = 1
Err.Clear
On Error Resume Next
Set mMain.oConn = CreateObject("ADODB.Connection")
' ConnString = "PROVIDER={SQL Server} ;SERVER=" & DBServer & ";DATABASE=" & DBName & ";USER ID=" & DBUser & ";PASSWORD=" & DBPassword & ";"
' ConnString = "PROVIDER=SQLOLEDB ;SERVER=" & DBServer & ";DATABASE=" & DBName & ";USER ID=" & DBUser & ";PASSWORD=" & DBPassword & ";"
ConnString = "PROVIDER=SQLNCLI11 ;SERVER=" & DBServer & ";DATABASE=" & DBName & ";USER ID=" & DBUser & ";PASSWORD=" & DBPassword & ";"
mMain.oConn.Open ConnString
If Err.Number <> 0 Then DB_Connect = 0
On Error GoTo 0
End Function

Function DB_Disconnect()
' If SettingRead("00_Var7") = 0 Then SettingSave "00_Var5", ""
On Error Resume Next
mMain.oConn.Close
On Error GoTo 0
Set mMain.oConn = Nothing
'Set mMain.oRS = Nothing
End Function

None

Views 289 Downloads 67

VBA-Excel Database
ANmarAmdeen
719
Attachments
Revisions

v4.0