If mCnn.State <> adStateOpen Then GoTo ErrH mDBName = vDBName mClientUserCode = vClientUserCode SetWorkDB = True Exit Function
ErrH: SetWorkDB = False Set mCnn = Nothing End Function '写入表记录SprintF是我自己写的一个函数,格式化字符串 sql = "insert into " & mMstTable & " (id,name,caption,objectname,notes) values (%d,'%s','%s','%s','%s')" sql = SprintF(sql, mID, mName, mCaption, mObjectName, mNotes) Call mCnn.Execute(sql)
'引用Microsoft Activex Data Object2.x Library Dim cn As New ADODB.Connection Dim rs as New Adodb.recordset'连接 cn.Open "Provider=SQLOLEDB;Data Source=服务器名;User Id=sa;PassWord=****;Initial Catalog=master" 'Microsoft OLE DB Provider for SQL Server '建立数据库 cn.Execute "create database 数据库名" cn.Execute "use 数据库名" '建立新表 cn.Execute "create table 表名(列1 int, 列2 varchar(10))" '往表中添加纪录 cn.Execute "insert into 表名(列1,列2) select 1111,'abcdeflkj'" cn.Execute "insert into 表名(列1,列2) select 22,'add'" '更新纪录 cn.Execute "update 表名 set 列2='xxxx' where 列1=22" '删除纪录 cn.Execute "delete 表名 where 列2='abcdeflkj'"'查询一个表 rs.open "select * from 表名",cn do while not rs.eof debug.print rs.fields(0) rs.movenext loop cn.Close Set cn = Nothing
Public Function SetWorkDB(ByVal vDBName As String, ByVal vClientUserCode As String) As Boolean
On Error GoTo ErrH
mDBServerName = GetDBServerName
mCOMServerName = GetCOMServerName
Call GetDBUserInfo(mDBUserID, mDBUserPWD)
'连接数据库
mCnn.CommandTimeout = 60 * 30
mCnn.Provider = "MSDataShape"
mCnn.Open "Data Provider=SQLOLEDB.1;" & _
"Persist Security Info=False;" & _
"User ID=" + mDBUserID + _
";Password=" + mDBUserPWD + _
";Initial Catalog=" + vDBName + ";" & _
"Data Source=" & mDBServerName
If mCnn.State <> adStateOpen Then GoTo ErrH
mDBName = vDBName
mClientUserCode = vClientUserCode
SetWorkDB = True
Exit Function
ErrH:
SetWorkDB = False
Set mCnn = Nothing
End Function
'写入表记录SprintF是我自己写的一个函数,格式化字符串 sql = "insert into " & mMstTable & " (id,name,caption,objectname,notes) values (%d,'%s','%s','%s','%s')"
sql = SprintF(sql, mID, mName, mCaption, mObjectName, mNotes)
Call mCnn.Execute(sql)
Dim cn As New ADODB.Connection
Dim rs as New Adodb.recordset'连接
cn.Open "Provider=SQLOLEDB;Data Source=服务器名;User Id=sa;PassWord=****;Initial Catalog=master" 'Microsoft OLE DB Provider for SQL Server
'建立数据库
cn.Execute "create database 数据库名"
cn.Execute "use 数据库名"
'建立新表
cn.Execute "create table 表名(列1 int, 列2 varchar(10))"
'往表中添加纪录
cn.Execute "insert into 表名(列1,列2) select 1111,'abcdeflkj'"
cn.Execute "insert into 表名(列1,列2) select 22,'add'"
'更新纪录
cn.Execute "update 表名 set 列2='xxxx' where 列1=22"
'删除纪录
cn.Execute "delete 表名 where 列2='abcdeflkj'"'查询一个表
rs.open "select * from 表名",cn
do while not rs.eof
debug.print rs.fields(0)
rs.movenext
loop
cn.Close
Set cn = Nothing