Function GetTables(cnn As ADODB.Connection) As Boolean 'Purpose : Get table names from the Connection given 'Input : cnn, ther ADODB.Connection 'Output : TRUE/FALSE ' On Error GoTo GetTables_ErrorHandler Dim rstSchema As ADODB.Recordset cboTablesName.Clear
Set rstSchema = cnn.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "Table"))
Do Until rstSchema.EOF If StrComp(Left(rstSchema!TABLE_NAME, 4), "MSys", vbTextCompare) <> 0 Then cboTablesName.AddItem rstSchema!TABLE_NAME End If rstSchema.MoveNext Loop
rstSchema.Close Set rstSchema = Nothing
Screen.MousePointer = 0ErrorHandler: Exit Function GetTables_ErrorHandler: Screen.MousePointer = 0 msgbox err.description Resume ErrorHandler End Function
'↓ DAO方法获取数据库非系统表名 ↓ Public Function GetDBTable() 'Function GetDBTable(ByVal index As Integer) As String '*** 获得数据库中的表名称 On Error Resume Next ' Debug.Print strDBFile Dim rsTem As Database Set rsTem = OpenDatabase(strDBFile) FrmMain.cmbTable.Clear Dim i% For i = 0 To rsTem.TableDefs.Count - 1 If CheckTable(rsTem.TableDefs(i).Name) Then FrmMain.cmbTable.AddItem rsTem.TableDefs(i).Name End If Next Set rsTem = Nothing End Function Public Function CheckTable(strTemp As String) As Boolean '*** 检查数据表是否为系统数据表 Select Case strTemp Case "MSysAccessObjects", "MSysAccessXML", "MSysACEs", "MSysObjects", "MSysQueries", "MSysRelationships" CheckTable = False Case Else CheckTable = True End Select End Function '**↑ DAO方法获取数据库非系统表名 ↑ *********
Private Sub Form_Load() dim i% Me.cmbSource.clear For i = 0 To UBound(arrTabName) If arrTabName(i) <> "" Then Me.cmbSource.AddItem arrTabName(i) Next End Sub'读取表名(用户表) '输入:[dbPath]数据库路径,[arrTabName]字符串数组(用于存放表名) '输出:arrTabName(表名) Public Sub subGetTabName(ByVal dbPath As String, ByRef arrTabName() As String) Dim db As DAO.Database Dim tblObj As TableDef Set db = OpenDatabase(dbPath) ReDim arrTabName(1)
For Each tblObj In db.TableDefs If (tblObj.Attributes And dbSystemObject) = 0 Then arrTabName(UBound(arrTabName) - 1) = tblObj.Name ReDim Preserve arrTabName(UBound(arrTabName) + 1) End If Next ReDim Preserve arrTabName(UBound(arrTabName) - 2) Set db = Nothing End Sub
用ADO取代RDO (ADO = DAO + RDO)。Public Sub GetTablesName() Dim cnn1 As ADODB.Connection Dim rstSchema As ADODB.Recordset Dim strCnn As String
Set cnn1 = New ADODB.Connection strCnn = "driver={SQL Server};server=srv;" & _ "uid=sa;pwd=;database=pubs" cnn1.Open strCnn
Set rstSchema = cnn1.OpenSchema(adSchemaTables)
Do Until rstSchema.EOF combo1.additem rstSchema!TABLE_NAME rstSchema.MoveNext Loop rstSchema.Close
cnn1.Close
End SubPublic Sub GetFieldsName() Dim rst As ADODB.Recordset Dim fld As ADODB.Field
Set rst = CurrentProject.Connection. _ OpenSchema(adSchemaTables) For Each fld In rst.Fields combo2.additem fld.Name Next fld rst.Close Set rst = Nothing Set fld = Nothing End Sub
'Purpose : Get table names from the Connection given
'Input : cnn, ther ADODB.Connection
'Output : TRUE/FALSE
'
On Error GoTo GetTables_ErrorHandler
Dim rstSchema As ADODB.Recordset
cboTablesName.Clear
Set rstSchema = cnn.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "Table"))
Do Until rstSchema.EOF
If StrComp(Left(rstSchema!TABLE_NAME, 4), "MSys", vbTextCompare) <> 0 Then
cboTablesName.AddItem rstSchema!TABLE_NAME
End If
rstSchema.MoveNext
Loop
rstSchema.Close
Set rstSchema = Nothing
Screen.MousePointer = 0ErrorHandler:
Exit Function
GetTables_ErrorHandler:
Screen.MousePointer = 0
msgbox err.description
Resume ErrorHandler
End Function
Public Function GetDBTable()
'Function GetDBTable(ByVal index As Integer) As String
'*** 获得数据库中的表名称
On Error Resume Next
' Debug.Print strDBFile
Dim rsTem As Database
Set rsTem = OpenDatabase(strDBFile)
FrmMain.cmbTable.Clear
Dim i%
For i = 0 To rsTem.TableDefs.Count - 1
If CheckTable(rsTem.TableDefs(i).Name) Then
FrmMain.cmbTable.AddItem rsTem.TableDefs(i).Name
End If
Next
Set rsTem = Nothing
End Function
Public Function CheckTable(strTemp As String) As Boolean
'*** 检查数据表是否为系统数据表
Select Case strTemp Case "MSysAccessObjects", "MSysAccessXML", "MSysACEs", "MSysObjects", "MSysQueries", "MSysRelationships"
CheckTable = False
Case Else
CheckTable = True
End Select
End Function
'**↑ DAO方法获取数据库非系统表名 ↑ *********
dim i%
Me.cmbSource.clear
For i = 0 To UBound(arrTabName)
If arrTabName(i) <> "" Then Me.cmbSource.AddItem arrTabName(i)
Next
End Sub'读取表名(用户表)
'输入:[dbPath]数据库路径,[arrTabName]字符串数组(用于存放表名)
'输出:arrTabName(表名)
Public Sub subGetTabName(ByVal dbPath As String, ByRef arrTabName() As String)
Dim db As DAO.Database
Dim tblObj As TableDef
Set db = OpenDatabase(dbPath)
ReDim arrTabName(1)
For Each tblObj In db.TableDefs
If (tblObj.Attributes And dbSystemObject) = 0 Then
arrTabName(UBound(arrTabName) - 1) = tblObj.Name
ReDim Preserve arrTabName(UBound(arrTabName) + 1)
End If
Next
ReDim Preserve arrTabName(UBound(arrTabName) - 2)
Set db = Nothing
End Sub
Dim rstSchema As ADODB.Recordset
Dim strCnn As String
Set cnn1 = New ADODB.Connection
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
cnn1.Open strCnn
Set rstSchema = cnn1.OpenSchema(adSchemaTables)
Do Until rstSchema.EOF
combo1.additem rstSchema!TABLE_NAME
rstSchema.MoveNext
Loop
rstSchema.Close
cnn1.Close
End SubPublic Sub GetFieldsName()
Dim rst As ADODB.Recordset
Dim fld As ADODB.Field
Set rst = CurrentProject.Connection. _
OpenSchema(adSchemaTables)
For Each fld In rst.Fields
combo2.additem fld.Name
Next fld
rst.Close
Set rst = Nothing
Set fld = Nothing
End Sub