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 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
' 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 1,540
Downloads 404
CodeID
DB ID