寻找表中自动编号字段名函数

Henry D. Sy

1. '---------------------------------------------------------------------------------------
   
2. ' Module    : IsAutoNum
   
3. ' DateTime  : 2011-12-07 22:00
   
4. ' Author    : Henry D. Sy
   
5. ' Purpose   : AutoNumIs("tbl1")
   
6. '---------------------------------------------------------------------------------------
   
7. Public Function AutoNumIs(ByVal tblName As String) As String
   
8.     Dim rs As New ADODB.Recordset
   
9.     Dim cnn As New ADODB.Connection
   
10.     Dim intCount As Integer
    
11.     Dim strName As String
    
12.     On Error GoTo AutoNumIs_Error
    
13. 
    
14.     Set cnn = CurrentProject.Connection
    
15.     rs.Open tblName, cnn, adOpenKeyset, adLockReadOnly
    
16.     For intCount = 0 To rs.Fields.Count - 1
    
17.         If rs.Fields(intCount).Properties("isautoincrement") Then
    
18.             strName = rs.Fields(intCount).Name
    
19.             Exit For
    
20.         End If
    
21.     Next
    
22.     If Len(strName) <> 0 Then
    
23.         AutoNumIs = strName
    
24.     Else
    
25.         AutoNumIs = "Not Find"
    
26.     End If
    
27.     rs.Close
    
28.     Set rs = Nothing
    
29. 
    
30.     On Error GoTo 0
    
31.     Exit Function
    
32. 
    
33. AutoNumIs_Error:
    
34. 
    
35.     MsgBox "Error " & Err.Number & " (" & Err.Description & ")"
    
36. End Function
    

复制代码

Henry D. Sy

鱼儿游游 发表于 2011-12-7 22:35
斑竹，我试了，发现有以下问题，能不能再改进一下。
要是当前ACCESS数据库，可以取回自动编号的字段。
要 ...

1. Public Function AutoNumIs(ByVal tblName As String) As String
   
2.     Dim rs As New ADODB.Recordset
   
3.     Dim cnn As New ADODB.Connection
   
4.     Dim intCount As Integer
   
5.     Dim strName As String
   
6.     On Error GoTo AutoNumIs_Error
   
7. 
   
8.     'Set cnn = CurrentProject.Connection
   
9.     cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
   
10.         CurrentProject.Path & "/ABC.mdb;Jet OLEDB:database password="
    
11.     cnn.Open
    
12.     rs.Open tblName, cnn, adOpenKeyset, adLockReadOnly
    
13.     For intCount = 0 To rs.Fields.Count - 1
    
14.         If rs.Fields(intCount).Properties("isautoincrement") Then
    
15.             strName = rs.Fields(intCount).Name
    
16.             Exit For
    
17.         End If
    
18.     Next
    
19.     If Len(strName) <> 0 Then
    
20.         AutoNumIs = strName
    
21.     Else
    
22.         AutoNumIs = "Not Find"
    
23.     End If
    
24.     rs.Close
    
25.     Set rs = Nothing
    
26. 
    
27.     On Error GoTo 0
    
28.     Exit Function
    
29. 
    
30. AutoNumIs_Error:
    
31. 
    
32.     MsgBox "Error " & Err.Number & " (" & Err.Description & ")"
    
33. End Function
    

复制代码

abc是你的库名，保存在一样的目录里

Henry D. Sy

视乎漏掉最终的赋值
GetIdentityField=strFieldName

Henry D. Sy

GetIdentityField=strFieldName
rst.Close
cn.Close