mdlcreatdata.bas

1:首先要导入hotel数据

Attribute VB_Name = "mdlCreatData"
Public CreateDataCon As New ADODB.Connection
Public CreateDataRs As New ADODB.Recordset

Public strCon As String

Public Function ConnectData() As Boolean
    On Error GoTo conerr
    CreateDataCon.ConnectionString = strCon
    CreateDataCon.ConnectionTimeout = 15              '连接时间15
    CreateDataCon.Open
    ConnectData = True
    Exit Function
conerr:
    Conshow = False
End Function

Private Sub Main()
  '读取配置文件
    frmLogo.Show
    Dim ReadText As String
    Open App.Path & "\config.ini" For Input As #1
    While Not EOF(1)
      Line Input #1, ReadText
      strCon = strCon + ReadText
    Wend
    Close #1
    
    If ConnectData = True Then
        CreateDataCon.Close
        frmLogo.Show
    Else
        frmLogo.Show
        CreateDataCon.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=msdb"
        CreateDataCon.Execute ("CREATE DATABASE Hotel")
        CreateDataCon.Close
        
        CreateDataCon.Open strCon
'创建用户表
        CreateDataCon.Execute ("CREATE TABLE administrator ( [admin] [varchar] (20) NOT NULL ,[ad_type] [varchar] (20) NOT NULL ,[ad_pwd] [varchar] (20) NOT NULL ) ON [PRIMARY]")

'创建顾客表
        CreateDataCon.Execute ("CREATE TABLE consumer([con_id] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ," & _
                    "[con_name] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ," & _
                    "[con_sex] [varchar] (5) COLLATE Chinese_PRC_CI_AS NOT NULL ," & _
                    "[con_cardtype] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ," & _
                    "[con_cardid] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ," & _
                    "[con_tel] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ," & _
                    "[con_roomid] [int] NULL ," & _
                    "[con_address] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ," & _
                    "[con_people] [int] NULL ," & _
                    "[con_indate] [datetime] NOT NULL ," & _
                    "[con_intime] [datetime] NOT NULL ," & _
                    "[con_state] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ," & _
                    "[con_remark] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ," & _
                    "[con_operator] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL)")
'创建员工表
       CreateDataCon.Execute ("CREATE TABLE employee([emp_id] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ," & _
                    "[emp_workdate] [datetime] NOT NULL ," & _
                    "[emp_dept] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ," & _
                    "[emp_name] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ," & _
                    "[emp_sex] [varchar] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ," & _
                    "[emp_age] [int] NOT NULL ," & _
                    "[emp_nation] [varchar] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ," & _
                    "[emp_cardId] [varchar] (18) COLLATE Chinese_PRC_CI_AS NOT NULL ," & _
                    "[emp_address] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ," & _
                    "[emp_Graduate] [datetime] NULL ," & _
                    "[emp_school] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ," & _
                    "[emp_specialty] [varchar] (15) COLLATE Chinese_PRC_CI_AS NULL ," & _
                    "[emp_work1] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ," & _
                    "[emp_work2] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ," & _
                    "[emp_remark] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL)")
'创建顾客预订表
        CreateDataCon.Execute ("CREATE TABLE preconcert([pre_id] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ," & _
                    "[pre_name] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ," & _
                    "[pre_sex] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ," & _
                    "[pre_prdate] [datetime] NOT NULL ," & _
                    "[pre_prtime] [datetime] NOT NULL ," & _
                    "[pre_tel][varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ," & _
                    "[pre_comedate] [datetime] NULL ," & _
                    "[pre_cometime] [datetime] NULL ," & _
                    "[pre_deposit] [money] NULL ," & _
                    "[pre_roomid] [int] NOT NULL ," & _
                    "[pre_roomtype] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ," & _
                    "[pre_consumer] [int] NULL ," & _
                    "[pre_unitprice] [money] NOT NULL ," & _
                    "[pre_remark] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ," & _
                    "[pre_state] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ," & _
                    "[pre_operator] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL)")
'创建房间表
        CreateDataCon.Execute ("CREATE TABLE rooms([room_floor] [varchar] (6) COLLATE Chinese_PRC_CI_AS NOT NULL ," & _
                    "[room_id] [varchar] (8) COLLATE Chinese_PRC_CI_AS NOT NULL ," & _
                    "[room_type] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ," & _
                    "[room_price] [money] NOT NULL ," & _
                    "[room_bed] [Int] NOT NULL ," & _
                    "[room_consumer] [int] NOT NULL ," & _
                    "[room_state] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ," & _
                    "[room_tel] [Int])")

'创建房间使用表
        CreateDataCon.Execute ("CREATE TABLE roomuse([use_id] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ," & _
                    "[use_name] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ," & _
                    "[use_indate] [datetime] NOT NULL ," & _
                    "[use_intime] [datetime] NOT NULL ," & _
                    "[use_roomid] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ," & _
                    "[use_consumer] [int] NOT NULL ," & _
                    "[use_unitprice] [money] NOT NULL ," & _
                    "[use_rebate] [int] NOT NULL ," & _
                    "[use_deposit] [money] NOT NULL ," & _
                    "[use_paymenttype] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ," & _
                    "[use_outdate] [datetime] NULL ," & _
                    "[use_inday] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ," & _
                    "[use_roommoney] [money] NULL ," & _
                    "[use_mealprice] [money] NULL ," & _
                    "[use_telprice] [money] NULL ," & _
                    "[use_allprice] [money] NULL ," & _
                    "[use_remark] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ," & _
                    "[use_inoperator] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ," & _
                    "[use_operator] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ," & _
                    "[use_state] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL)")
        
        CreateDataCon.Execute ("ALTER TABLE administrator ADD CONSTRAINT [PK_admin] PRIMARY KEY CLUSTERED ([admin])  ON [PRIMARY]")
        CreateDataCon.Close
        End If
End Sub