Change the SQL database structure in VB

In the development of "Liangshan Prefecture Forestry Bureau" Natural Forest Resources protection Integrated Management system, the need for the program to establish a SQL Server database operating environment. In order to facilitate the user, the author developed the database Configuration tool. Complete the facility in the SQL Server database, set up a database, set up tables, assign permissions, and all database configuration operations in the system of "Liangshan Forestry Bureau" can be accomplished through this gadget. facilitates the creation of the SQL Server environment required for database applications without starting the SQL Enterprise Manager configuration database.

----This gadget developed by VB, access to the database using ADO, to implement changes in the database structure, other languages can also be used as a reference. Start VB6.0, create a new project, select "Microsoft ActiveX Data Objects 2.0 Library" in the menu-project-Reference, the code needs to have

dim conn As New ADODB.Connection　　
　　定义ADO数据库对象
conn.ConnectionString = "driver={SQL Server};" & _
"server=" & ServerName & ";uid=" & UserName & ";
pwd=" & Password & ";database=" & DatabaseName & ""
　　　　连接数据串
conn.open　　　　连接数据库----NOTE: servername is the server name, username is the user name, password is the user password, databasename the name of the database to log on, can be empty.

The----core code is as follows:

I. Establishment of a database

----principle: To create a database, first initialize a database device, and then establish a database on this device. All the device names are recorded in the system table "sysdevices" and all the database names are recorded in the system table "sysdatabases". Before you build, it's a good idea to query the two system tables to see if the name already exists. A physical name and an idle device identification number are required before the device is established.

初始化设备语法：
DISK INIT NAME="device_name",PHYNAME=
"physical_name",VDEVNO=device_number,
SIZE=numberofblock----Description: Here, name is the database device name (a valid identifier), Phyname (the physical name of the database device) is the original disk partition UNIX or peripheral (VMS) name, or the operating system file name. Vdevno The device identification number of the database, the legal value of 1-255,size is a block of 2KB, such as 1MB (1024KB) When the size value is 512.

----CREATE DATABASE syntax: Create DB database_name [on Database_device]

----Description: DB me is the name of the database to be built, Database_device is the device name

----to create a new database, you need the device name, database name, physical name, and device number. The specific steps are as follows:

----We assume that the user will build a new device dbname and build a database dbname on the device dbname.

----1) Get the device name. DBName is the user given the name of the device, first query the system table sysdevices to see whether the user given the device name dbname already exists, if this device name exists, you need to replace a device name, because the device name is unique.

sql = "select * from sysdevices
where name=" & dbName & ""
Set rs = conn.Execute(sql)
If Not rs.EOF Then
MsgBox "设备名"" & dbName & ""
已存在！", 16, "请重新输入名称"
Exit Sub
End If----2) to get the database name. DBName is the user given the database name, query system table sysdatabases, see the user given the database name dbname already exist, if this database exists, you need to replace a database name, like device name, database name is the only

sql = "select * from sysdatabases
where name=" & dbName & ""
Set rs = conn.Execute(sql) 下面代码略----3) Get phyname physical name. The physical location of the database file on the query server Serverpath, typically, we can query the location of master (this is the main library name of SQL Server) from the system table sysdevices, such as G:\MSSQL\DATA\MASTER. DAT, our database can be built in the "G:\MSSQL\DATA\" directory.

sql = "select name,phyname from sysdevices "　　
low/16777216为设备号
Set rs = conn.Execute(sql)
然后遍历记录对象rs，当name="master"时，取出phyname，
从而可以得到物理位置serverpath =G:\MSSQL\DATA\。----4) to get an idle device number VDEVNO. Device number legal value 1~255, traverse these numbers, find out unused idle device number, the following program to get the existing device number

sql = "select distinct low/16777216
from sysdevices order by low/16777216"
low/16777216为设备号----5) to establish a database. The required information is ready, you can set up a database (note: The following "" & Chr () & "" is a "" "double quotes, so that after processing to meet the requirements of the grammar; the database is 20M, dbsize=512*20)

sql = "DISK INIT NAME=" & Chr(34) & ""
& dbName & "" & Chr(34) & ",PHYSNAME="
& Chr(34) & "" & serverpath & "" & dbName
& ".dat" & Chr(34) & ",VDEVNO=" & vdevno
& ",SIZE=" & dbSize & ""
Set rs = conn.Execute(sql) 初始化设备
sql = "CREATE DATABASE " & dbName & "
on " & dbName & "=" & dbSize & ""
注：
第一个dbName是数据库名，
第二个dbName是设备名
Set rs = conn.Execute(sql)
在设备dbName上建立数据库dbName
MsgBox "数据库"" & dbName & ""建在服务器上
"" & serverpath & "" & dbName & ".dat"，
建立成功！", 64, "成功"