Public Function ConnectString() As String '链接数据库'返回数据库链接ConnectString = "provider =sqloledb;server=IP(写运行时数据库的存放地址);UID=sa;PWD=123456;database=charge"End FunctionPublic Function exectuteSQL(ByVal SQL As String, MsgString As String) As ADODB.RecordsetDim cnn As ADODB.Connection '定义链接Dim rst As ADODB.RecordsetDim sTokens() As String '定义字符串On Error GoTo executeSQL_error '异常处理sTokens = Split(SQL) '拆分字符串函数'用Split函数产生一个包含各个子串的数组Set cnn = New ADODB.Connection ' 创建连接cnn.Open ConnectString'判断字符串中是否含有指定内容If InStr("insert, delete,update", UCase$(sTokens(0))) Then '$表示返回的类型明确是字符型。cnn.Execute SQL '执行查询语句MsgString = sTokens(0) & "query successful" '返回查询信息ElseSet rst = New ADODB.Recordset '创建数据集对象rst.Open Trim$(SQL), cnn, adOpenKeyset, adLockBatchOptimistic '返回查询结果'adLockOptimistic 仅当调用update方法时,数据提供者锁定记录 adLockBatchOptimistic 用于批处理修改'这个是缺省值。 adOpenKeyset 反映由其它用户所做的对记录的改变或者删除动作,但并不反映由其它用户做作的添加新记录的动作。Set executeSQL = rst '返回记录集对象MsgString = "查询到" & rst.RecordCount & "记录"End IfexecuteSQL_exit:Set rst = Nothing '清空数据集对象Set cnn = Nothing '中断连接Exit FunctionexecuteSQL_error: '判断错误类型messtring = "查询错误:" & Err.DescriptionResume executeSQL_exit 'Resume 语句 在错误处理程序结束后,恢复原有的运行。End FunctionPublic Function Testtxt(txt As String) As BooleanIf Trim(txt) = "" ThenTesttxt = FalseElseTesttxt = TrueEnd IfEnd Function
'创建窗体
Private Sub Form_Load()Dim txtSQL As StringDim MsgText As StringDim mrc As ADODB.RecordsettxtSQL = "select * from 表 "Set mrc = ExecuteSQL(txtSQL, MsgText)mrc.MoveFirstCall viewDatamybookmark = mrc.Bookmarkmcclean = TrueIf mrc.EOF = False Thenmrc.MoveFirstCall viewDatamybookmark = mrc.BookmarkElseMsgBox "没有记录!", vbOKOnly + vbExclamation, "警告"Exit SubEnd IfEnd Sub