你可以使用VB的ADO对对象来搞定。 你首先要在VB菜单中: “工程”-->“引用”-->“Microsoft AxtiveX Data Objects 2.X Library” 注:2.X为版本号,如果你机子上有高版本的就用高版本的,如:2.5或2.6的示例: dim cn as new adodb.Connection dim rs as new adodb.recordset dim sqlstr as string cn.open "Provider=SQLOLEDB;Driver={SQL Server};Server=服务器名或IP地址;Uid=用户名" & _ ";Pwd=密码;Database=数据库名" rs.cursorlocation=aduseclient rs.open "select * from XXX",cn,3,3 rs.save "d:\mydata\data.rst",adPersistXML'保存 rs.close rs.open "d:\mydata\data.rst",cn,AdOpenDynamic,AdLockBatchOptimistic,adCmdFile'打开 msgbox "共有:" & rs.recordcount & "条记录!!!" rs.close set rs=nothing cn.close set cn=nothing
ADODC控件使用不太灵活,最好还是用DAO!
象这样:工程--->引用--->Microsoft ActiveX Data Object 2.x(版本号) Dim CN As New ADODB.Connection '定义数据库的连接 Dim Rs As New ADODB.Recordset CN.ConnectionString = "Provider=sqloledb;Data Source=pmserver;Initial Catalog=northwind;User Id=sa;Password=sa;" CN.Open Rs.CursorLocation = adUseClient Rs.Open "select * from employees", CN, adOpenDynamic, adLockOptimistic Set DataGrid1.DataSource = Rs
Attribute VB_Name = "SqlConnModule" Option Explicit Public SqlConn As New ADODB.Connection'功能简介:建立SQL数据库公共链接 '参数一:服务器名或IP '参数二:数据库名称 '参数三:登陆用户名称 '参数四:登陆用户密码 '参数五:延时 '返回值:"ok"--成功, 否则返回错误信息 Function PulbicSQLConn(StrServerName As String, StrDataName As String, StrUserName As String, StrPassword As String, IntOutTime As Integer) As String On Error GoTo SQLConnErr Dim StrSql As String StrSql = "Provider=sqloledb;Data Source=" & StrServerName & ",1433;Initial Catalog=" & StrDataName & ";User ID=" & StrUserName & ";Password=" & StrPassword & ";" If SqlConn.State <> 0 Then SqlConn.Close SqlConn.ConnectionTimeout = IntOutTime SqlConn.Open StrSql PulbicSQLConn = "ok" Exit Function SQLConnErr: PulbicSQLConn = Err.Description If SqlConn.State <> 0 Then SqlConn.Close: Set SqlConn = Nothing End Function'功能简介:关闭SOL数据库公共链接 Function PublicMdbClose() If SqlConn.State <> 0 Then SqlConn.Close: Set SqlConn = Nothing End Function
dim con as new adodb.connectioncon.open"provider=sqloledb.1;user id=sa;password=**;initial catalog=pubs;data source=***('为服务器名)"
然后可以进行操作了.
你首先要在VB菜单中:
“工程”-->“引用”-->“Microsoft AxtiveX Data Objects 2.X Library”
注:2.X为版本号,如果你机子上有高版本的就用高版本的,如:2.5或2.6的示例:
dim cn as new adodb.Connection
dim rs as new adodb.recordset
dim sqlstr as string
cn.open "Provider=SQLOLEDB;Driver={SQL Server};Server=服务器名或IP地址;Uid=用户名" & _
";Pwd=密码;Database=数据库名"
rs.cursorlocation=aduseclient
rs.open "select * from XXX",cn,3,3
rs.save "d:\mydata\data.rst",adPersistXML'保存
rs.close
rs.open "d:\mydata\data.rst",cn,AdOpenDynamic,AdLockBatchOptimistic,adCmdFile'打开
msgbox "共有:" & rs.recordcount & "条记录!!!"
rs.close
set rs=nothing
cn.close
set cn=nothing
Dim Rs As New ADODB.Recordset
CN.ConnectionString = "Provider=sqloledb;Data Source=pmserver;Initial Catalog=northwind;User Id=sa;Password=sa;"
CN.Open
Rs.CursorLocation = adUseClient
Rs.Open "select * from employees", CN, adOpenDynamic, adLockOptimistic
Set DataGrid1.DataSource = Rs
Option Explicit
Public SqlConn As New ADODB.Connection'功能简介:建立SQL数据库公共链接
'参数一:服务器名或IP
'参数二:数据库名称
'参数三:登陆用户名称
'参数四:登陆用户密码
'参数五:延时
'返回值:"ok"--成功, 否则返回错误信息
Function PulbicSQLConn(StrServerName As String, StrDataName As String, StrUserName As String, StrPassword As String, IntOutTime As Integer) As String
On Error GoTo SQLConnErr
Dim StrSql As String
StrSql = "Provider=sqloledb;Data Source=" & StrServerName & ",1433;Initial Catalog=" & StrDataName & ";User ID=" & StrUserName & ";Password=" & StrPassword & ";"
If SqlConn.State <> 0 Then SqlConn.Close
SqlConn.ConnectionTimeout = IntOutTime
SqlConn.Open StrSql
PulbicSQLConn = "ok"
Exit Function
SQLConnErr:
PulbicSQLConn = Err.Description
If SqlConn.State <> 0 Then SqlConn.Close: Set SqlConn = Nothing
End Function'功能简介:关闭SOL数据库公共链接
Function PublicMdbClose()
If SqlConn.State <> 0 Then SqlConn.Close: Set SqlConn = Nothing
End Function
数据库以SQL Server 7为例,如果你使用DAO对象,那首先你必须加载DAO对象。然后:
Set ws=CreateWorkspace
Set db=ws.OpenDatabase("", dbDriverNoPrompt, False, _
"ODBC;Driver=SQL Server;UID=" & sUID & _
";PWD=" & sPWD & ";SERVER=" & sServer & _
";DATABASE=" & sDatabaseName )
这样,应用程序就以sUID身份密码为sPWD与指定的服务器sServer上的数据库sDatabaseName建立了连接。
如果你使用ADO对象,则可以按如下方法:
Dim cn As ADODB.Connection
cn.ConnectionString="DRIVER=SQL Server" & _
";SERVER=" & sServer & ";UID=" & _
sUID & ";PWD=" & sPWD & ";DATABASE=" & _
sDatabaseName
cn.Open
...
cn.Close
当然,前提是你已经引用了ADO对象(ADODB)。
怎么样,是不是省去了手工创建DSN的烦恼,用户可以轻松许多,你的程序也光彩不少吧。