Dim conn As New ADODB.Connection
Dim ds As New ADODB.Recordset
Set conn = CreateObject("Adodb.Connection")
Set ds = CreateObject("Adodb.Recordset")
conn.Open "Provider=SQLOLEDB.1;Password=;Persist Security Info=True;User ID=sa;Initial Catalog=shajiaoc;Data Source=LYB"
ds.Open "select getdate()", conn, 1, 1
If Not ds.EOF Then
Debug.Print ds.Fields(0).Value
End If
Public adoCN As New ADODB.Connection '定义数据库的连接存放数据和代码
Public SqlCommand As New ADODB.Command '定义 SQL 命令
Dim adoDateTime As New ADODB.Recordset '获取 NT-SERVER 时间
'***********************************************************************
'* 功能:与 SQL SERVER 数据库建立连接并取出服务器时间
'***********************************************************************
Public Function OpenConnection() As String '打开数据库
On Error GoTo SQLConErr
With adoCN
.CursorLocation = adUseClient
.Provider = "sqloledb"
.Properties("Data Source").Value = cNtServerName
.Properties("Initial Catalog").Value = cDatabaseName
.Properties("User ID") = cSQLUserName
.Properties("Password") = cSQLPassword
.Properties("prompt") = adPromptNever
.ConnectionTimeout = 15 ‘可以改这个时间
.Open
If .State = adStateOpen Then
adoDateTime.Open "select getdate()", adoCN, adOpenStatic, adLockOptimistic
cServerDate = Format(adoDateTime(0), "yyyy-mm-dd")
cServertime = Mid(adoDateTime(0), 10)
Else
MsgBox "数据库连接失败,请找系统管理员进行检查 !", 16, cProgramName
End
End If
End With
SqlCommand.ActiveConnection = adoCN
SqlCommand.CommandType = adCmdText
Exit Function
SQLConErr:
Select Case Err.Number
Case -2147467259
MsgBox "找不到指定的SQL Server服务器或者数据库不存在,请重新设置!", vbExclamation
F_SetSystem.Show 1
Case -2147217843
MsgBox "指定的SQL Server数据库用户不存在或口令错误,请重新设置!", vbExclamation
F_SetSystem.Show 1
Case Else
MsgBox "数据环境连接失败,请找系统管理员进行检查 !", 16, cProgramName
End Select
OpenConnection
End Function
Public Function FgModGetServerTime(CSconn As ADODB.Connection) As String
Dim rstTemp As New ADODB.Recordset
Dim StrSql As String
Dim strTime As String
StrSql = "SELECT GETDATE()"
If CSconn.State = 0 Then
CSconn.Open '打开连接
End If
Set rstTemp = CSconn.Execute(StrSql)
strTime = Trim(rstTemp.Fields(0)) '取得日期时间
Set rstTemp = Nothing
Dim strHour As String
Dim strMinute As String
Dim strSecond As String
If CInt(strHour) >= 0 And CInt(strHour) <= 9 Then
strHour = "0" & CInt(strHour) '如果是以一位数表示小于10的小时数,就将其补为两位的
End If
If CInt(strMinute) >= 0 And CInt(strMinute) <= 9 Then
strMinute = "0" & CInt(strMinute) '如果是以一位数表示小于10的分钟数,就将其补为两位的
End If
If CInt(strSecond) >= 0 And CInt(strSecond) <= 9 Then
strSecond = "0" & CInt(strSecond) '如果是以一位数表示小于10的秒数,就将其补为两位的
End If
FgModGetServerTime = strHour & ":" & strMinute & ":" & strSecond '以(小时:分钟:秒 00:00:00)的形式返回
End Function
'******************************************************
'函 数 名:FgModGetServerDate
'函数功 能:取得服务器日期
'参 数1:连接数据库的连接对象
'参 数2:中间间隔的符号如"/","-"等
'使用限制:'本函数只适合1930到2029年的情形
'函数返回值:返回一个只带服务器日期的字符串,格式为(年/月/日 0000/00/00中间的分隔符以参数2为准)
'******************************************************
Public Function FgModGetServerDate(CSconn As ADODB.Connection, strSperator As String) As String
Dim rstTemp As New ADODB.Recordset
Dim StrSql As String
Dim strTime As String
StrSql = "SELECT GETDATE()"
If CSconn.State = 0 Then
CSconn.Open '打开连接
End If
Set rstTemp = CSconn.Execute(StrSql)
strTime = Trim(rstTemp.Fields(0)) '取得日期时间
Set rstTemp = Nothing
Dim strDay As String
Dim strMonth As String
Dim strYear As String
If CInt(strYear) > 30 And Len(Trim(strYear)) < 4 Then '是以两位数表示的19XX年
strYear = "19" & CInt(strYear)
ElseIf CInt(strYear) >= 0 And CInt(strYear) <= 9 And Len(Trim(strYear)) < 2 Then '是以一位数表示的200X年
strYear = "200" & CInt(strYear)
ElseIf CInt(strYear) >= 10 And CInt(strYear) <= 29 And Len(Trim(strYear)) < 4 Then '是以两位数表示的20XX年
strYear = "20" & CInt(strYear)
End If
If CInt(strMonth) >= 1 And CInt(strMonth) <= 9 Then
strMonth = "0" & CInt(strMonth) '如果是以一位数表示小于10的月数,就将其补为两位的
End If
If CInt(strDay) >= 1 And CInt(strDay) <= 9 Then
strDay = "0" & CInt(strDay) '如果是以一位数表示小于10的天数,就将其补为两位的
End If