'创建表
Public Function CreateERPDBObject() As Boolean
On Error GoTo ON_ERROR
Dim strCtab As String
Screen.MousePointer = vbHourglass '鼠标变成沙漏
con.ConnectionString = "provider = sqloledb; data source = .; initial catalog =YHERP;uid=sa;pwd="
con.ConnectionTimeout = 15
con.Open
strCtab = "USE YHERP" & vbCrLf & _
"CREATE TABLE Eemp" & vbCrLf & _
"( Ename CHAR(32),Ecode CHAR(16) PRIMARY KEY,EAddress CHAR(128),ETel CHAR(32),ESal INT,EPos CHAR(32))" & vbCrLf & _
"CREATE TABLE Econtract" & vbCrLf & _
"( Cname CHAR(128),Ccode CHAR(32) PRIMARY KEY,Eccode CHAR(16),Ctel CHAR(32),Cmon INT,Caddress CHAR(128)," & vbCrLf & _
" CDate DATETIME)" & vbCrLf & _
"ALTER TABLE Econtract" & vbCrLf & _
" ADD CONSTRAINT CHK1 CHECK (Ccode%10000000>=1 and Ccode%10000000<=9)" & vbCrLf & vbCrLf & _
"ALTER TABLE Econtract" & vbCrLf & vbCrLf & _
" ADD CONSTRAINT CHK2 CHECK (Cmon > 1000)" & vbCrLf & _
" ALTER TABLE Econtract" & vbCrLf & _
" ADD CONSTRAINT CHK3" & vbCrLf & _
" FOREIGN KEY (Eccode) REFERENCES Eemp(Ecode)" & vbCrLf & _
"GO" & vbCrLf & _
"CREATE PROC SearchInfo" & vbCrLf & _
"AS" & vbCrLf & _
" SELECT Eemp_link.ESal,Econtract_link.Cmon FROM " & _
"Eemp AS Eemp_link INNER JOIN Econtract AS Econtract_link" & vbCrLf & _
" ON Eemp_link.Ecode = Econtract_link.Eccode" & vbCrLf & _
"GO" con.Execute (strCtab)
CreateERPDBObject = True
Screen.MousePointer = vbDefault '鼠标变成正常状态
Call Me.CreateObjectFinish
Exit Function
ON_ERROR:
Screen.MousePointer = vbDefault '鼠标变成正常状态
Frm_Main.Label1.Caption = "ERP数据库恢复系统"
MsgBox "数据库对象创建错误!" & vbCrLf & "错误编号:" & Err.Number, vbCritical + vbOKOnly, "错误"
CreateERPDBObject = False
End Function
Public Function CreateERPDBObject() As Boolean
On Error GoTo ON_ERROR
Dim strCtab As String
Screen.MousePointer = vbHourglass '鼠标变成沙漏
con.ConnectionString = "provider = sqloledb; data source = .; initial catalog =YHERP;uid=sa;pwd="
con.ConnectionTimeout = 15
con.Open
strCtab = "USE YHERP" & vbCrLf & _
"CREATE TABLE Eemp" & vbCrLf & _
"( Ename CHAR(32),Ecode CHAR(16) PRIMARY KEY,EAddress CHAR(128),ETel CHAR(32),ESal INT,EPos CHAR(32))" & vbCrLf & _
"CREATE TABLE Econtract" & vbCrLf & _
"( Cname CHAR(128),Ccode CHAR(32) PRIMARY KEY,Eccode CHAR(16),Ctel CHAR(32),Cmon INT,Caddress CHAR(128)," & vbCrLf & _
" CDate DATETIME)" & vbCrLf & _
"ALTER TABLE Econtract" & vbCrLf & _
" ADD CONSTRAINT CHK1 CHECK (Ccode%10000000>=1 and Ccode%10000000<=9)" & vbCrLf & vbCrLf & _
"ALTER TABLE Econtract" & vbCrLf & vbCrLf & _
" ADD CONSTRAINT CHK2 CHECK (Cmon > 1000)" & vbCrLf & _
" ALTER TABLE Econtract" & vbCrLf & _
" ADD CONSTRAINT CHK3" & vbCrLf & _
" FOREIGN KEY (Eccode) REFERENCES Eemp(Ecode)" & vbCrLf & _
"GO" & vbCrLf & _
"CREATE PROC SearchInfo" & vbCrLf & _
"AS" & vbCrLf & _
" SELECT Eemp_link.ESal,Econtract_link.Cmon FROM " & _
"Eemp AS Eemp_link INNER JOIN Econtract AS Econtract_link" & vbCrLf & _
" ON Eemp_link.Ecode = Econtract_link.Eccode" & vbCrLf & _
"GO" con.Execute (strCtab)
CreateERPDBObject = True
Screen.MousePointer = vbDefault '鼠标变成正常状态
Call Me.CreateObjectFinish
Exit Function
ON_ERROR:
Screen.MousePointer = vbDefault '鼠标变成正常状态
Frm_Main.Label1.Caption = "ERP数据库恢复系统"
MsgBox "数据库对象创建错误!" & vbCrLf & "错误编号:" & Err.Number, vbCritical + vbOKOnly, "错误"
CreateERPDBObject = False
End Function
"provider = sqloledb.1; data source = .; initial catalog =YHERP;uid=sa;pwd=" //正确strCtab = "USE YHERP" //错误
你前面已经打开了这个数据库,此处不要“USE YHERR”。
"if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Eemp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Eemp]" & vbCrLf & _
"CREATE TABLE Eemp" & vbCrLf & _
"( Ename CHAR(32),Ecode CHAR(16) PRIMARY KEY,EAddress CHAR(128),ETel CHAR(32),ESal INT,EPos CHAR(32))" & vbCrLf & _
"if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Econtract]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Econtract]" & vbCrLf & _
"CREATE TABLE Econtract" & vbCrLf & _
"( Cname CHAR(128),Ccode CHAR(32) PRIMARY KEY,Eccode CHAR(16),Ctel CHAR(32),Cmon INT,Caddress CHAR(128)," & vbCrLf & _
" CDate DATETIME)" & vbCrLf & _
"ALTER TABLE Econtract" & vbCrLf & _
" ADD CONSTRAINT CHK1 CHECK (Ccode%10000000>=1 and Ccode%10000000<=9)" & vbCrLf & vbCrLf & _
"ALTER TABLE Econtract" & vbCrLf & vbCrLf & _
" ADD CONSTRAINT CHK2 CHECK (Cmon > 1000)" & vbCrLf & _
" ALTER TABLE Econtract" & vbCrLf & _
" ADD CONSTRAINT CHK3" & vbCrLf & _
" FOREIGN KEY (Eccode) REFERENCES Eemp(Ecode)" & vbCrLf & _
"GO" & vbCrLf & _
"CREATE PROC SearchInfo" & vbCrLf & _
"AS" & vbCrLf & _
" SELECT Eemp_link.ESal,Econtract_link.Cmon FROM " & _
"Eemp AS Eemp_link INNER JOIN Econtract AS Econtract_link" & vbCrLf & _
" ON Eemp_link.Ecode = Econtract_link.Eccode" & vbCrLf & _
"GO"
不要有GO,好象con.Execute函数自动执行GO操作像这样:
strtab="create table......"
con.Execute(strtab)
strtab="create proc ......"
con.Execute(strtab)
" if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Eemp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Eemp];" & _
" CREATE TABLE Eemp (Ename CHAR(32),Ecode CHAR(16) PRIMARY KEY,EAddress CHAR(128),ETel CHAR(32),ESal INT,EPos CHAR(32));" & _
" if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Econtract]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Econtract];" & _
" CREATE TABLE Econtract (Cname CHAR(128),Ccode CHAR(32) PRIMARY KEY,Eccode CHAR(16),Ctel CHAR(32),Cmon INT,Caddress CHAR(128), CDate DATETIME);" & _
" ALTER TABLE Econtract ADD CONSTRAINT CHK1 CHECK (Ccode%10000000>=1 and Ccode%10000000<=9);" & _
" ALTER TABLE Econtract ADD CONSTRAINT CHK2 CHECK (Cmon > 1000);" & _
" ALTER TABLE Econtract ADD CONSTRAINT CHK3 FOREIGN KEY (Eccode) REFERENCES Eemp(Ecode);" & _
" CREATE PROC SearchInfo AS SELECT Eemp_link.ESal,Econtract_link.Cmon FROM Eemp AS Eemp_link INNER JOIN Econtract AS Econtract_link ON Eemp_link.Ecode = Econtract_link.Eccode"