添加到@HansUp的出色答案中，您可以隐藏
IDENTITY列，同时使用SQL公开格式化的列
VIEW：然后可以撤消对表的特权，以便用户使用
VIEW和不“查看”表，例如：demo：
复制+粘贴到任何VBA模块中，无需引用也不需要Access UI /对象模型，可以在temp文件夹中创建一个新的mdb，例如，使用Excel：
Sub YourView2() On Error Resume Next Kill Environ$("temp") & "DropMe.mdb" On Error GoTo 0 Dim cat Set cat = CreateObject("ADOX.Catalog") With cat .Create _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & _ Environ$("temp") & "DropMe.mdb" With .ActiveConnection Dim Sql As String Sql = _ "CREATE TABLE YourTable ( " & _ "ID INTEGER IDENTITY(1, 1) NOT NULL UNIQUE, " & _ "data_col VARCHAr(20) NOT NULL);" .Execute Sql Sql = _ "CREATE VIEW YourView AS " & _ "SELECT FORMAT$(ID, '0000') & 'A' AS formatted_ID, " & vbCr & _ " data_col " & vbCr & _ " FROM YourTable;" .Execute Sql Sql = _ "INSERT INTO YourView (data_col) VALUES ('one');" .Execute Sql Sql = _ "INSERT INTO YourView (data_col) VALUES ('day');" .Execute Sql Sql = _ "INSERT INTO YourView (data_col) VALUES ('when');" .Execute Sql Sql = "SELECt * FROM YourView;" Dim rs Set rs = .Execute(Sql) MsgBox rs.GetString End With Set .ActiveConnection = Nothing End WithEnd Sub我认为，如果您包含DDL GRANT / REVOKE示例来管理特权，那么它会更好。
这是完成此操作的更新代码：
Sub YourView2() On Error Resume Next Kill Environ$("temp") & "DropMe.mdb" Kill Environ$("temp") & "DropMeToo.mdw" On Error GoTo 0 ' Create workgroup and db Dim cat As ADOX.Catalog Set cat = CreateObject("ADOX.Catalog") With cat .Create _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Jet OLEDB:Engine Type=4;" & _ "Data Source=" & _ Environ$("temp") & "DropMeToo.mdw;" & _ "Jet OLEDB:Create System Database=-1" .Create _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Jet OLEDB:Engine Type=4;" & _ "Data Source=" & _ Environ$("temp") & "DropMe.mdb;" & _ "Jet OLEDB:System Database=" & _ Environ$("temp") & "DropMeToo.mdw;" ' Add table with data and user with privileges With .ActiveConnection Dim Sql As String Sql = _ "CREATE TABLE YourTable ( " & _ "ID INTEGER IDENTITY(1, 1) NOT NULL UNIQUE, " & _ "data_col VARCHAr(20) NOT NULL);" .Execute Sql Sql = _ "CREATE VIEW YourView AS " & _ "SELECT FORMAT$(ID, '0000') & 'A' AS formatted_ID, " & vbCr & _ " data_col " & vbCr & _ " FROM YourTable WITH OWNERACCESS OPTION;" .Execute Sql .Execute "CREATE USER onedaywhen pwd Chri5tma5;" .Execute "GRANT ALL PRIVILEGES ON YourView TO onedaywhen;" .Execute "REVOKE ALL PRIVILEGES ON YourTable FROM onedaywhen;" End With End With ' Test user can connect Dim con As ADODB.Connection Set con = New ADODB.Connection With con .ConnectionString = _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Jet OLEDB:Engine Type=4;" & _ "Data Source=" & _ Environ$("temp") & "DropMe.mdb;" & _ "Jet OLEDB:System Database=" & _ Environ$("temp") & "DropMeToo.mdw;" & _ "User ID=onedaywhen;Password=pwd;" .Open On Error Resume Next ' Attempt to insert to table (no privileges) Sql = _ "INSERT INTO YourTable (data_col) VALUES ('one');" .Execute Sql If Err.Number <> 0 Then MsgBox _ Err.Number & ": " & _ Err.Description & _ " (" & Err.Source & ")" End If On Error GoTo 0 Dim rs On Error Resume Next ' Attempt to read table (no privileges) Sql = _ "SELECT * FROM YourTable;" Set rs = .Execute(Sql) If Err.Number <> 0 Then MsgBox _ Err.Number & ": " & _ Err.Description & _ " (" & Err.Source & ")" End If On Error GoTo 0 ' From here, work only with VIEW Sql = _ "INSERT INTO YourView (data_col) VALUES ('one');" .Execute Sql Sql = _ "INSERT INTO YourView (data_col) VALUES ('day');" .Execute Sql Sql = _ "INSERT INTO YourView (data_col) VALUES ('when');" .Execute Sql Sql = "SELECt * FROM YourView;" Set rs = .Execute(Sql) MsgBox rs.GetString Set con = Nothing End WithEnd Sub
