RE: Xoá các table theo điều kiện trong Access
dannynguyen1980 > 10-11-11, 11:57 AM
Bác cho hỏi luôn tôi có viết đoạn code sau để xóa bảng với điều khi bảng tồn tại trên database SQL Server 2000 [nối với Access 2003], khi gọi hàm DeleteTable không báo lỗi gì nhưng bảng cũng không xóa luôn. Code như sau:
Option Compare Database
Option Explicit
Function DeleteTable[ByVal TableName As String] As Boolean
'References: Microsoft Access 11.0 Object Library, Microsoft DAO 3.6 Object Library
'Set references by Clicking Tools and Then References in the Code View window
' Deletes a Table with a given TableName that exists in a database
' Accepts
' TableName: Name of table
' Returns True on success, false otherwise
'USAGE: DeleteTable "TABLENAME"
On Error GoTo errhandler
Dim Db As ADODB.Connection
Dim strSql As String
Set Db = CurrentProject.Connection
'Create the Delte SQL Code from our Three Strings
strSql = "DROP TABLE " & TableName
If ifTableExists[TableName] = True Then
'Print the SQL so we can paste into the query builder if there are errors
Debug.Print strSql
'delete table if found
Db.Execute strSql
End If
'If no errors return true
DeleteTable = True
ExitHere:
Set Db = Nothing
'Notify the user the process is complete.
MsgBox "Delete Table Completed"
Exit Function
errhandler:
'There is an error return false
DeleteTable = False
With Err
MsgBox "Error " & .Number & vbCrLf & .Description, _
vbOKOnly Or vbCritical, "DeleteTable"
End With
Resume ExitHere
End Function
Function ifTableExists[TableName As String] As Boolean
Dim rs As Recordset, Db As ADODB.Connection
' DAO Vars
'References: Microsoft Access 11.0 Object Library, Microsoft DAO 3.6 Object Library
'Set references by Clicking Tools and Then References in the Code View window
'Checks if Table exists.
'USAGE: ifTableExists "TABLENAME"
On Error GoTo NoTable 'If there is no table capture the error.
Set Db = CurrentProject.Connection
'If Table is there open it
Set rs = Db.OpenRecordset["Select * from " & TableName & ";"]
ifTableExists = True
rs.Close
Db.Close
Exit Function
NoTable:
'If table is not there close out and set function to false
Set rs = Nothing
Db.Close
Set Db = Nothing
ifTableExists = False
Exit Function
End Function
Mong bác chỉ giúp.
Trân trọng cảm ơn!