后学目前遇到以下一个问题,请各位朋友帮助?
1、在程序运行过程中在一个数据库里新创建1个表?
2、如何在程序运行中检查数据库中以某名字命名的数据表是否存在?
由于时间关系暂时不能去书店,所以在这里请教有经验的高手,环境是VB+SQL7.0,以上2个问题如何编写代码?请赐教。谢谢!
1、在程序运行过程中在一个数据库里新创建1个表?
2、如何在程序运行中检查数据库中以某名字命名的数据表是否存在?
由于时间关系暂时不能去书店,所以在这里请教有经验的高手,环境是VB+SQL7.0,以上2个问题如何编写代码?请赐教。谢谢!
2.
function existTab(TabName as string) as boolean
dim TR as new recordset
dim Adoconn as new adodb.connection
adoconn.open connstr
existtab=true
on error goto Err1
tr.open "select from testtable"
tr.close
adoconn.close
exit function
Err1:
adoconn.close
existtab=false
end function
2.
Function IsExist(tb As String) As Boolean
Dim adoRst As New ADODB.Recordset
Dim adoConn As New ADODB.connection
Dim strConn As String
Dim sql As String
strConn = "driver={SQL server};server=lyb;uid=sa;pwd=;database=jppoem"
adoConn.Open strConn
sql = "select * from sysobjects where id=object_id('" + tb + "')"
adoRst.Open sql, adoConn, 3, 3
If adoRst.EOF Then
IsExist = False
Else
IsExist = True
End If
End Function
Sub CreateTable(tb As String)
Dim adoRst As New ADODB.Recordset
Dim adoConn As New ADODB.connection
Dim strConn As String
Dim sql As String
strConn = "driver={SQL server};server=lyb;uid=sa;pwd=;database=jppoem"
adoConn.Open strConn
sql = "create table " & tb & "("
sql = sql & vbCrLf & "tb_index integer NOT NULL IDENTITY (1, 1),"
sql = sql & vbCrLf & " field1 varchar(10) Not Null, "
sql = sql & vbCrLf & " field2 VarChar(100)"
sql = sql & ")"
adoRst.Open sql, adoConn, 3, 3
End SubFunction IsExist(tb As String) As Boolean
Dim adoRst As New ADODB.Recordset
Dim adoConn As New ADODB.connection
Dim strConn As String
Dim sql As String
strConn = "driver={SQL server};server=lyb;uid=sa;pwd=;database=jppoem"
adoConn.Open strConn
sql = "select * from sysobjects where id=object_id('" + tb + "')"
adoRst.Open sql, adoConn, 3, 3
If adoRst.EOF Then
IsExist = False
Else
IsExist = True
End If
End Function
Private Sub Command1_Click()
Dim tb As String
tb = "tb_name"
If Not IsExist(tb) Then
CreateTable (tb)
MsgBox "创建表成功!"
Else
MsgBox "该表已经存在!"
End If
End Sub
1.从SYSOBJECTS系统表中查看表是否存在