我想用SQL语句来实现SQL数据库的打包
具体是这样的:
我先把所有有用的的SQL SERVER的数据库写成SQL语句,然后放在VB的SUB MAIN中。用一个同目录下的文本文件来检查是否是第一次打开程序。如果是第一次,就用ADO连上SQL SERVER然后进行建库和建表。如果不是跳过。
可是他再一个XP的机子上可以新建可在另一台机子上不能新建啊!高手请看代码:
'这是查询函数
Public Function ExecuteSQL(ByVal SQL As String, MsgString As String) As ADODB.Recordset
'定义连接
Dim cnn As ADODB.Connection
'定义数据集
Dim rst As ADODB.Recordset
'定义字符串
Dim sTokens() As String
'异常处理
On Error GoTo ExecuteSQL_Error
'用Splite分割SQL中数据
sTokens = Split(SQL)
'创建连接
Set cnn = New ADODB.Connection
Dim ConnectString As String
ConnectString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=StudentInfoCtrl"
cnn.Open ConnectString
If InStr("INSERT,DELETE,UPDATE", UCase$(sTokens(0))) Then
cnn.Execute SQL
MsgString = sTokens(0) & "Query successful!"
Else
Set rst = New ADODB.Recordset
'返回查询结果
rst.Open Trim$(SQL), cnn, adOpenKeyset, adLockOptimistic
'返回记录集对象
Set ExecuteSQL = rst
MsgString = "查询到" & rst.RecordCount & "条记录"
End If
ExecuteSQL_Exit:
Set rst = Nothing
Set cnn = Nothing
Exit Function
ExecuteSQL_Error:
MsgString = "查询错误:" & Err.Description
Resume ExecuteSQL_Exit
End Function
'这是我的建库语句等:
Sub Main()
Dim datainput As String
Open App.Path & "\SETUP.INI" For Input As #1
Input #1, datainput
Close #1
If datainput = "1" Then
Dim mrc As ADODB.Recordset
Dim txtSQL As String
Dim msgText As String
txtSQL = "create database studentInfoCtrl"
Set mrc = ExecuteSQL(txtSQL, msgText)
txtSQL = "CREATE TABLE studentInfoCtrl.dbo.user_info(userID char(10) not null,userPWD char(16) not null,userDes char(10) not null)"
Set mrc = ExecuteSQL(txtSQL, msgText)
txtSQL = "create table studentInfoCtrl.dbo.student_info(studentID char(10) not null,studentName char(10) null,studentSex char(2) null,bornDate datetime null,classNO char(10) null,teleNumber char(14) null,ruDate datetime null,address varchar(50) null,comment varchar(200) null)"
Set mrc = ExecuteSQL(txtSQL, msgText)
txtSQL = "create table studentInfoCtrl.dbo.class_info(classNO char(10) not null,grade char(20) null,director char(10) null,classroomNO char(10) null)"
Set mrc = ExecuteSQL(txtSQL, msgText)
txtSQL = "create table studentInfoCtrl.dbo.course_info(courseNO char(10) not null,courseName char(10) null,courseType char(10) null,courseDes char(50) null)"
Set mrc = ExecuteSQL(txtSQL, msgText)
txtSQL = "create table studentInfoCtrl.dbo.gradecourse_info(grade char(10) null,courseName char(10) null)"
Set mrc = ExecuteSQL(txtSQL, msgText)
txtSQL = "create table studentInfoCtrl.dbo.result_info(examNO char(10) not null,studentID char(10) not null,studentName char(10) null,classNO char(10) null,courseName char(10) null,result float null)"
Set mrc = ExecuteSQL(txtSQL, msgText)
txtSQL = "insert into studentInfoCtrl.dbo.user_info values('sa','','')"
Set mrc = ExecuteSQL(txtSQL, msgText)
Open App.Path & "\SETUP.INI" For Output As #1
Write #1, "2"
Close #1
End If
Dim fLogin As New frmLogin
fLogin.Show vbModal
If Not fLogin.OK Then
End
End If
Unload fLogin
Set fMainForm = New frmMain
fMainForm.Show
End Sub