Do Until rstSchema.EOF out = out & "Table name: " & _ rstSchema!TABLE_NAME & vbCr & _ "Table type: " & rstSchema!TABLE_TYPE & vbCr rstSchema.MoveNext Loop rstSchema.Close
adoCN.Close Debug.Print out End Sub
Public adoCN As New ADODB.Connection '定义数据库的连接存放数据和代码 Public adoCNAccess As New ADODB.Connection '定义数据库的连接存放数据和代码 Public adoCNAccess1 As New ADODB.Connection '定义数据库的连接存放数据和代码 Public adoCNtemp As New ADODB.Connection '临时数据库 Public SqlCommand As New ADODB.Command '定义 SQL 命令 Public RsUsers As New ADODB.Recordset Public RsDept As New ADODB.Recordset Public Rs_Dm_Level As New ADODB.Recordset 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'*********************************************************************** '* 功能:连接数据报表环境 '* '*********************************************************************** Public Sub OpenDEConnection() '连接数据环境 On Error GoTo DEConErr With DE_Report.Con_report If .State = adStateOpen Then .Close End If .CursorLocation = adUseClient .ConnectionTimeout = 15 .ConnectionString = "Provider=SQLOLEDB.1;Password=" & cSQLPassword & ";Persist Security Info=True;User ID=" & cSQLUserName & ";Initial Catalog=" & cDatabaseName & ";Data Source=" & cNtServerName .Open End With Exit Sub DEConErr: Select Case Err.Number Case Else MsgBox "数据环境连接失败,请找系统管理员进行检查 !", 16, cProgramName End End Select End SubPublic Function OpenAccess() As String With adoCNAccess If .State <> adStateOpen Then .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & cProgramPath & "Trade.mdb" ';password=allway" .ConnectionTimeout = 5 .Open If .State = adStateOpen Then OpenAccess = "数据库连接成功" Else OpenAccess = "数据库连接失败,请按帮助进行检查 !" MsgBox "数据库连接失败,请找系统管理员进行检查 !", 16, cProgramName End End If End If End With End Function
'最好是代码里写连接SQL数据库'要便于调试的话,在主程序一进来的时候,连接SQL SERVER,以后来开记录集的时候都用这个连接字符串,这样便于程序维护.'也可以用INI文件控制连接数据库设置, Data Source Initial Catalog User ID Password 等几个连接参数
Dim adoCN As New ADODB.Connection '定义数据库的连接存放数据和代码Dim rstSchema
Dim strCnn As New ADODB.Recordset
str1 = "Provider=SQLOLEDB.1;Password=sa;Persist Security Info=True;User ID=sa;Initial Catalog=wktrade;Data Source=net5"
adoCN.Open str1
Set rstSchema = adoCN.OpenSchema(adSchemaTables)
Do Until rstSchema.EOF
out = out & "Table name: " & _
rstSchema!TABLE_NAME & vbCr & _
"Table type: " & rstSchema!TABLE_TYPE & vbCr
rstSchema.MoveNext
Loop
rstSchema.Close
adoCN.Close
Debug.Print out
End Sub
Public adoCNAccess As New ADODB.Connection '定义数据库的连接存放数据和代码
Public adoCNAccess1 As New ADODB.Connection '定义数据库的连接存放数据和代码
Public adoCNtemp As New ADODB.Connection '临时数据库
Public SqlCommand As New ADODB.Command '定义 SQL 命令
Public RsUsers As New ADODB.Recordset
Public RsDept As New ADODB.Recordset
Public Rs_Dm_Level As New ADODB.Recordset
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'***********************************************************************
'* 功能:连接数据报表环境
'*
'***********************************************************************
Public Sub OpenDEConnection() '连接数据环境
On Error GoTo DEConErr
With DE_Report.Con_report
If .State = adStateOpen Then
.Close
End If
.CursorLocation = adUseClient
.ConnectionTimeout = 15
.ConnectionString = "Provider=SQLOLEDB.1;Password=" & cSQLPassword & ";Persist Security Info=True;User ID=" & cSQLUserName & ";Initial Catalog=" & cDatabaseName & ";Data Source=" & cNtServerName
.Open
End With
Exit Sub
DEConErr:
Select Case Err.Number
Case Else
MsgBox "数据环境连接失败,请找系统管理员进行检查 !", 16, cProgramName
End
End Select
End SubPublic Function OpenAccess() As String
With adoCNAccess
If .State <> adStateOpen Then
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & cProgramPath & "Trade.mdb" ';password=allway"
.ConnectionTimeout = 5
.Open
If .State = adStateOpen Then
OpenAccess = "数据库连接成功"
Else
OpenAccess = "数据库连接失败,请按帮助进行检查 !"
MsgBox "数据库连接失败,请找系统管理员进行检查 !", 16, cProgramName
End
End If
End If
End With
End Function
但我并不知道能否实现
不过还是觉的你会的很多从你的回答也学到很多啊借别人的帖子THANK YOU啊成光
信他的没问题的
Dim strCnn As New ADODB.Recordset
str1 = "Provider=SQLOLEDB.1;Password=sa;Persist Security Info=True;User ID=sa;Initial Catalog=wktrade;Data Source=net5"
adoCN.Open str1
'最好是代码里写连接SQL数据库'要便于调试的话,在主程序一进来的时候,连接SQL SERVER,以后来开记录集的时候都用这个连接字符串,这样便于程序维护.'也可以用INI文件控制连接数据库设置,
Data Source
Initial Catalog
User ID
Password
等几个连接参数
data.databasename=""
data.connect="ODBC;DRIVER={SQL Server};Server=SQL服务器名;uid=用户名;pwd=密码;database=数据库名"
data.refresh用ADO 控件打开SQL数据库与ACCESS相近,只是选择数据源提供者为SqlOledb而已
dim cn as new adodb.connectioncn.open "driver={sql server};srever=servername;username=sa;pwd=;database=databasename"
Dim conn As Object
Private Sub Form_Load()
Set conn = CreateObject("ADODB.Connection")
conn.Open ("driver={SQL Server};server=(local);uid=sa;database=Master;pwd=密码")
End Sub
'___________________________________转载
袖珍数据库连接方式查询手册 下面的资料是平时使用到的一些数据库连接方式,可能不太全,但是可以用来当做资料查询,希望能够帮上大家的忙。
下面将简单介绍一下几种ADO连接方式:ODBC DSN,ODBC DSN-Less, OLE DB Provider,和"MS Remote" Provider. I.DSN
oConn.Open "DSN=AdvWorks; UID=Admin; PWD=;"
注意:从MDAC2.1开始就不能够在使用这样的方式了,就是只把DSN文件名放在ConnectString中。你必须同时使用DSN,UID,PWD标志。例如下面的方式在MDAC 2.1中将会出错:oConn.Open "AdvWorks" II.File DSN
oConn.Open "FILEDSN=\somepath\mydb.dsn; UID=Admin; PWD=;" III.ODBC DSN-Less Connections
a)ODBC Text Driver
oConn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)}; Dbq=\somepath\;" & _
"Extensions=asc,csv,tab,txt; Persist Security Info=False"
注意:需要在SQL语句中指定使用到的文件名。例如:
oRs.Open "Select * From customer.csv", oConn, adOpenStatic, adLockReadOnly, adCmdText b)ODBC Driver for Access
i)普通安全模式:
oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=\somepath\mydb.mdb; Uid=Admin; Pwd=;" ii)如果使用了System database:
oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=\somepath\mydb.mdb; SystemDB=\somepath\mydb.mdw;", "admin", "" c)ODBC Driver for SQL Server
i)普通安全模式
oConn.Open "Driver={SQL Server}; Server=carl2;" & _
"Database=pubs; Uid=sa; Pwd=;" ii)使用信任安全模式:
oConn.Open "Driver={SQL Server}; Server=carl2;" & _
"Database=pubs; Uid=; Pwd=;" 注意:要使用空白的Uid和Pwd d)ODBC Driver for Oracle
i)使用现有的Oracle ODBC Driver from Microsoft:
oConn.Open "Driver={Microsoft ODBC for Oracle};" & _
"Server=OracleServer.world; Uid=demo; Pwd=demo;" ii)使用老版本的Oracle ODBC Driver from Microsoft:
oConn.Open "Driver={Microsoft ODBC Driver for Oracle};" & _
"ConnectString=OracleServer.world; Uid=demo; Pwd=demo;"
IV)使用微软的OLE DB Data Link Connections方式Data Link File (UDL)
a)使用绝对路径
oConn.Open "File Name=\somepath\pubs.udl;" b)使用相对路径
oConn.Open "File Name=pubs.udl;"
Dim conn As Object
Private Sub Form_Load()
Set conn = CreateObject("ADODB.Connection")
conn.Open ("driver={SQL Server};server=(local);uid=sa;database=Master;pwd=密码")
End Sub
转载袖珍数据库连接方式查询手册 下面的资料是平时使用到的一些数据库连接方式,可能不太全,但是可以用来当做资料查询,希望能够帮上大家的忙。
下面将简单介绍一下几种ADO连接方式:ODBC DSN,ODBC DSN-Less, OLE DB Provider,和"MS Remote" Provider. I.DSN
oConn.Open "DSN=AdvWorks; UID=Admin; PWD=;"
注意:从MDAC2.1开始就不能够在使用这样的方式了,就是只把DSN文件名放在ConnectString中。你必须同时使用DSN,UID,PWD标志。例如下面的方式在MDAC 2.1中将会出错:oConn.Open "AdvWorks" II.File DSN
oConn.Open "FILEDSN=\somepath\mydb.dsn; UID=Admin; PWD=;" III.ODBC DSN-Less Connections
a)ODBC Text Driver
oConn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)}; Dbq=\somepath\;" & _
"Extensions=asc,csv,tab,txt; Persist Security Info=False"
注意:需要在SQL语句中指定使用到的文件名。例如:
oRs.Open "Select * From customer.csv", oConn, adOpenStatic, adLockReadOnly, adCmdText b)ODBC Driver for Access
i)普通安全模式:
oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=\somepath\mydb.mdb; Uid=Admin; Pwd=;" ii)如果使用了System database:
oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=\somepath\mydb.mdb; SystemDB=\somepath\mydb.mdw;", "admin", "" c)ODBC Driver for SQL Server
i)普通安全模式
oConn.Open "Driver={SQL Server}; Server=carl2;" & _
"Database=pubs; Uid=sa; Pwd=;" ii)使用信任安全模式:
oConn.Open "Driver={SQL Server}; Server=carl2;" & _
"Database=pubs; Uid=; Pwd=;" 注意:要使用空白的Uid和Pwd d)ODBC Driver for Oracle
i)使用现有的Oracle ODBC Driver from Microsoft:
oConn.Open "Driver={Microsoft ODBC for Oracle};" & _
"Server=OracleServer.world; Uid=demo; Pwd=demo;" ii)使用老版本的Oracle ODBC Driver from Microsoft:
oConn.Open "Driver={Microsoft ODBC Driver for Oracle};" & _
"ConnectString=OracleServer.world; Uid=demo; Pwd=demo;"
IV)使用微软的OLE DB Data Link Connections方式Data Link File (UDL)
a)使用绝对路径
oConn.Open "File Name=\somepath\pubs.udl;" b)使用相对路径
oConn.Open "File Name=pubs.udl;"
,我送你一个自己编的类模块,总共不超过7行代码,以后每次设置好联接方式后,你就可以重复调用,非常方便。而且,代码也没那么冗于了。把邮箱告诉我,等到货后,在结贴呀!不然我可没的赚了! _==/ i i \==_
/XX/ |\___/| \XX\
/XXXX\ 蝙 |XXXXX| 蝠 /XXXX\
|XXXXXX\_ _XXXXXXX_ _/XXXXXX|
XXXXXXXXXXXxxxxxxxXXXXXXXXXXXxxxxxxxXXXXXXXXXXX
|XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX|
XXXXXXXXXXXXXXXXXXXXXX 侠 XXXXXXXXXXXXXXXXXXXXXXX
|XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX|
XXXXXX/^^^^"\XXXXXXXXXXXXXXXXXXXXX/^^^^^\XXXXXX
|XXX| \XXX/^^\XXXXX/^^\XXX/ |XXX|
\XX\ \X/ \XXX/ \X/ /XX/
adoCN.Open str1大家都知道一句话就可以实现,但作为一个程序员,应该尽量为用户着想,把程序写活,当环境改变时,设置一下就可以用了。象用友,金碟,南北软件都做到了这点三年前的时候,我写程序就写得很死,我们市场部的人拿去当用户环境一变,程序就不能用,后来一直用通用程序的作法,把程序写活,也便于程序维护所以说,我们平时可以错鉴别人软件中好的经验,为我所用.