Public adoCN As New ADODB.Connection '定义数据库的连接存放数据和代码 Public adoCNAccess As New ADODB.Connection '定义数据库的连接存放数据和代码Public SqlCommand As New ADODB.Command '定义 SQL 命令Dim adoDateTime As New ADODB.Recordset '获取 NT-SERVER 时间 '*********************************************************************** '* 功能:与 SQL SERVER 数据库建立连接并取出服务器时间 '*********************************************************************** Public Function OpenConnection1() As String '打开数据库 End FunctionPublic 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
其实连远程数据库与本地的sql server是一样的~
Public Function exesql(ByVal sql As String) As ADODB.Recordset Dim conn As New ADODB.Connection sql = Trim$(sql) Set conn = New ADODB.Connection Set rst = New ADODB.Recordset conn.ConnectionString = "Driver={SQL Server};Server=61.172.xx.xx;Address=61.172.xx.xx,1433;Network =dbmssocn;Database=xxxxxxxxx;Uid=xxxxxxxx;Pwd=xxxxxxx;" conn.ConnectionTimeout = 30 conn.Open Set rst.ActiveConnection = conn rst.LockType = adLockOptimistic rst.CursorType = adOpenKeyset rst.Open sql Set exesql = rst Set rst = Nothing Set conn = Nothing End Function 请问我的这个代码有什么问题啊!? 多谢LBwu(大鸟) chenyu5188(来自东方的狼)! 分不够再开贴转分!!!
Public Function exesql(ByVal sql As String) As ADODB.Recordset Dim conn As New ADODB.Connection dim rst as adodb.recordset sql = Trim$(sql) Set conn = New ADODB.Connection Set rst = New ADODB.Recordset conn.ConnectionString = "Driver={SQL Server};Server=61.172.xx.xx;Address=61.172.xx.xx,1433;Network =dbmssocn;Database=xxxxxxxxx;Uid=xxxxxxxx;Pwd=xxxxxxx;" conn.ConnectionTimeout = 30 conn.Open Set rst.ActiveConnection = conn rst.LockType = adLockOptimistic rst.CursorType = adOpenKeyset rst.Open sql Set exesql = rst Set rst = Nothing Set conn = Nothing End Function
这是我自己一直用的,挺好使的 Public Enum e_DatabaseTypes e_DatabaseTypes_Undefined = 0 e_databaseTypes_OracleMSDA = 1 e_databaseTypes_OracleODBC = 2 e_databaseTypes_SQLserver = 3 e_databaseTypes_MicrosoftJet = 4 e_databaseTypes_MicrosoftAccess97File = 5 e_databaseTypes_MicrosoftAccess2KFile = 6 e_databaseTypes_DSNFile = 7 e_databaseTypes_AccessFile = 99 End Enum Public Function BuildConnectString(ByVal databaseType As e_DatabaseTypes, _ ByVal serverOrFilename As String, Optional ByVal databaseName As String, _ Optional ByVal UserName As String, Optional ByVal Password As String) As String '此函数生成ADODATA 控件的connectstring 属性
Select Case databaseType Case e_databaseTypes_OracleMSDA
End FunctionPublic Function DetermineDirectory(inputString As String) As String '从包含文件路径的字符串中提出文件所在的目录路径 Dim Pos As Integer Pos = InStrRev(inputString, "\", , vbTextCompare) DetermineDirectory = Mid(inputString, 1, Pos)
End Function Public Function DetermineFilename(inputString As String) As String '从包含文件路径的字符串中提出文件名 Dim Pos As Integer If InStr(1, inputString, "\") = 0 Then DetermineFilename = inputString Else Pos = InStrRev(inputString, "\", , vbTextCompare) DetermineFilename = Mid(inputString, Pos + 1, Len(inputString) - Pos) End If End Function
Public adoCNAccess As New ADODB.Connection '定义数据库的连接存放数据和代码Public SqlCommand As New ADODB.Command '定义 SQL 命令Dim adoDateTime As New ADODB.Recordset '获取 NT-SERVER 时间
'***********************************************************************
'* 功能:与 SQL SERVER 数据库建立连接并取出服务器时间
'***********************************************************************
Public Function OpenConnection1() As String '打开数据库
End FunctionPublic 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
Dim conn As New ADODB.Connection
sql = Trim$(sql)
Set conn = New ADODB.Connection
Set rst = New ADODB.Recordset
conn.ConnectionString = "Driver={SQL Server};Server=61.172.xx.xx;Address=61.172.xx.xx,1433;Network =dbmssocn;Database=xxxxxxxxx;Uid=xxxxxxxx;Pwd=xxxxxxx;"
conn.ConnectionTimeout = 30
conn.Open
Set rst.ActiveConnection = conn
rst.LockType = adLockOptimistic
rst.CursorType = adOpenKeyset
rst.Open sql
Set exesql = rst
Set rst = Nothing
Set conn = Nothing
End Function
请问我的这个代码有什么问题啊!? 多谢LBwu(大鸟) chenyu5188(来自东方的狼)! 分不够再开贴转分!!!
Dim conn As New ADODB.Connection
dim rst as adodb.recordset
sql = Trim$(sql)
Set conn = New ADODB.Connection
Set rst = New ADODB.Recordset
conn.ConnectionString = "Driver={SQL Server};Server=61.172.xx.xx;Address=61.172.xx.xx,1433;Network =dbmssocn;Database=xxxxxxxxx;Uid=xxxxxxxx;Pwd=xxxxxxx;"
conn.ConnectionTimeout = 30
conn.Open
Set rst.ActiveConnection = conn
rst.LockType = adLockOptimistic
rst.CursorType = adOpenKeyset
rst.Open sql
Set exesql = rst
Set rst = Nothing
Set conn = Nothing
End Function
Public Enum e_DatabaseTypes
e_DatabaseTypes_Undefined = 0
e_databaseTypes_OracleMSDA = 1
e_databaseTypes_OracleODBC = 2
e_databaseTypes_SQLserver = 3
e_databaseTypes_MicrosoftJet = 4
e_databaseTypes_MicrosoftAccess97File = 5
e_databaseTypes_MicrosoftAccess2KFile = 6
e_databaseTypes_DSNFile = 7
e_databaseTypes_AccessFile = 99
End Enum
Public Function BuildConnectString(ByVal databaseType As e_DatabaseTypes, _
ByVal serverOrFilename As String, Optional ByVal databaseName As String, _
Optional ByVal UserName As String, Optional ByVal Password As String) As String
'此函数生成ADODATA 控件的connectstring 属性
Select Case databaseType
Case e_databaseTypes_OracleMSDA
BuildConnectString = "Provider=MSDAORA;Data Source=" & serverOrFilename & ";User ID=" & _
IIf(UserName <> "", UserName, "") & ";Password=" & IIf(Password <> "", Password, "") & _
";" & IIf(databaseName <> "", "Initial Catalog=" & databaseName & ";", "")
Case e_databaseTypes_OracleODBC
BuildConnectString = "DRIVER={Microsoft ODBC for Oracle};SERVER=" & serverOrFilename _
& ";UID=" & UserName & ";PWD=" & Password & ";" & _
IIf(databaseName <> "", "Initial Catalog=" & databaseName & ";", "")
Case e_databaseTypes_SQLserver
BuildConnectString = "Provider=SQLOLEDB.1;Persist Security Info=False;Data Source=" _
& serverOrFilename & ";User ID=" & IIf(UserName <> "", UserName, "") & _
";Password=" & IIf(Password <> "", Password, "") & ";" & _
IIf(databaseName <> "", "Initial Catalog=" & databaseName & ";", "")
Case e_databaseTypes_DSNFile
BuildConnectString = "Provider=MSDASQL;DSN=" & serverOrFilename & _
";UID=" & IIf(UserName <> "", UserName, "") & ";PWD=" & _
IIf(Password <> "", Password & ";", "") & ";" & _
IIf(databaseName <> "", "Initial Catalog=" & databaseName & ";", "")
Case e_databaseTypes_MicrosoftAccess2KFile, e_databaseTypes_MicrosoftAccess97File
BuildConnectString = "Driver={Microsoft Access Driver (*.mdb)};Dbq=" & _
serverOrFilename & ";DefaultDir=" & DetermineDirectory(serverOrFilename) & ";"
End Select
End FunctionPublic Function DetermineDirectory(inputString As String) As String '从包含文件路径的字符串中提出文件所在的目录路径
Dim Pos As Integer
Pos = InStrRev(inputString, "\", , vbTextCompare)
DetermineDirectory = Mid(inputString, 1, Pos)
End Function
Public Function DetermineFilename(inputString As String) As String '从包含文件路径的字符串中提出文件名
Dim Pos As Integer
If InStr(1, inputString, "\") = 0 Then
DetermineFilename = inputString
Else
Pos = InStrRev(inputString, "\", , vbTextCompare)
DetermineFilename = Mid(inputString, Pos + 1, Len(inputString) - Pos)
End If
End Function
ConnectString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;pwd=xxx;Data Source=IP"
DBtest.ConnectionString = ConnectString
DBtest.CursorLocation = adUseClient
DBtest.ConnectionTimeout = 5
DBtest.Open连接SQL数据库中某个数据库
CSWithDB = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;PWD=XXX;Initial Catalog=" & DataBaseName & ";Data Source=IP"
CNCsDB.ConnectionString = CSWithDB
CNCsDB.CursorLocation = adUseClient
CNCsDB.Open
我经常用的 连接字符串
gdbCnnStr = "Provider=sqloledb;Data Source=IP;Initial Catalog=mydb;User Id=Myusername;Password=MyPassword"另外远程连接不成功的原因是 ODBC 没有配置成使用TCP/IP 连接 ,因为默认是 命名管道 进行连接,这样远程连接就建立不起来。
我为什么在SQlsever客户端通过外网的ip不能够访问到服务器上面?时还需要其他的配置吗?
我用的是winxp和SQLsever个人版。
谢谢了!请指教!
还要保证ping 服务器ip的1433端口能通过能通过
vpn是硬件
代理 服务器我不会请看这里
http://community.csdn.net/Expert/topic/3698/3698555.xml?temp=.6193201