????? ??????? ????? ??????? ?? ?????? Access ?? SQL Server 2000 Desktop Edition

?????? ????????? ?????? ?????????
???? ???????: 308312 - ??? ???????? ???? ????? ????? ??? ???????.
??????: ????? ????? ?????? ??????? ??????? ???????? ? ?????? ?????? ??????????.

??? ????? ??? ?????? ??? ??? ????? Microsoft Access (.adp).

?????? ??? ????? Microsoft Access 2000 ?? ??? ???????? ???? 318816.
????? ???? | ?? ????

??????

???? ??? ?????? ??????? ?????? ?????? ???????? Microsoft SQL Server ??????? ??????? ?? ????? Microsoft Access (ADP).

??????? ????

?? SQL Server? ????? ????? ????? ????? ???????? ?? ??? ??????? ???? ?? ???????? ?? ????? ??????. ????? ?? ??? ???????? ??????? ??? ?????? ???? ?????, ????? ????? ?????????? ??? ?????????? ??? ??? ?????? ????? ????? ??? ????? ???????? ??????? ??? ?? ????? ???????? ??? ??? ????? ???????? ?????. ?? ?? ?????? ????? ???? ???? ?????? ???? ??? ??? ??? ?????????? ?????? ??? ???????? ???????? ??? ??? ????? ???????? ???.

????? ????? ?????? ??????? ??? ???? ?? ????? ?????????? ?????? ????????? ??????? ?????? ??? ?? ????????? ??? ????? ?????? ?? ????? ?????? ????? ????? ??? ???? ?????? ????????. ?? ??? ???????? ?? ???? ?????????? ??? ?????? ????? ??? ????????? ????? ???? ?? ???? ?? ???? ?????? ??? ??? ?? ????? ?????? ???? ???????.

????? ???? ?????? ???????? ??? ??? ???? ???????? ???????? ??? ???? ?????? ???? SQL ???? ???. ?? ???? ??? ?????????? ?????? ?????? ??????? ?????? ?????? ???? ?? ???? ?? ????? ??? ??????. ????? ??? ???? ?????? ??? ????? ???????? ???????? ???? ???????? ??????? ADP ?? ?? ??????? ??????? ???????? ?????? ?????? ???? ???????? ???? ??? ??????. ????? ???? ?? ???? ??? ??????, ?? ???? ?? ??????? ??? ?????????? ?? ????? ???????? ?? ?????? ?? ?????? ????? ????? ????.

?? ????? ????? ??????? ??????? ?????? ??? ??? ???????. ?? ??? ????? ??????? ???? ????? ????? ?????? ????? ????? ?????. ????? ?? ???? ?? ?????? ?????? ??? SQL Server ?????????? ?? ??????? ?????? ?????? ???????? ?????? ???????? ?????? ???. ??? ??? ?????? ???? ?????? ????? ??? ??????? ?? ???? ????? ??????? ??? ????? ????? ?????? ??????? ??????? sp_setapprole ?????. ?? SQL Server ??????? ?? ??? ????? ?? ?????????? ???????? ??? ??????? ???????? ???? ????? ???????? ???????? ??? ????? ????? ??? ?????? ??? ???????. ????? ????????? ??????? ?????? ? ?? ??? ??? ??? ?? ????? ?? ?????.

????? ??? ????? ??

Microsoft Access ???????? ?? ???? ??? ????? ??????? ?????? ?????? ?????? ???? ?????? SQL ??? ????? ???????. ???? Microsoft ???????? ????? ?????? ???? ?? ??????? ?? ??????? ?????? ?? SQL Server ?? Microsoft Office XP ?????? ?????? ??? ??????? ???????? ????????. ??? ???? ????? ?? ???? ????? ??? ??????? ?? ???? ???????? ???????? ??????? ???????? Transact-SQL (T-SQL) ?? ADP. ??? ????? ?? ?? ?????? ???? ?? ???? ?????? SQL ???? ???? ??? ??????? ?????? ???? ?????? ??? ??????? ?? ??? SQL Server ??????? ????? ??????? ??????? ????? ????? ??? ????? ?? ??????? ?? ??? ????? ?????? ????? ???????? ??? ????:
  1. ??? ????? Access.
  2. ??? ????? Access ????? Northwind.
  3. ?? ?????? "????? ??????" ? ???? ??? ????? ????? ???? ?????? ?? ???? ??? ???? ???? ???? ????? ????? ?? ???? Visual Basic.

    ?????? ?? Access 2007? ???? ??? ???? ????? ?? ?????? ???? ??? ????? ??????? ?????.
  4. ???? ?? ???? ???????? ???????? ??????? ???? ?????? ??????? ????:
    Public Function AddNewAppRole(RoleName As String, PW As String) As Boolean
    On Error GoTo EH:
    If CurrentProject.IsConnected Then
    Dim sTSQL As String
        'Create the command
    sTSQL = "EXEC sp_addapprole '" & RoleName & "','" & PW & "'"
        'Send the command
    Application.CurrentProject.Connection.Execute sTSQL
    AddNewAppRole = True
    Else
    AddNewAppRole = False
    End If
    Exit Function
    EH:
    MsgBox Err.Number & ": " & Err.Description, vbCritical
    AddNewAppRole = False
    End Function
    					
  5. ??? ?????? ??????? ?? ?? ?????? ???? Visual Basic.
  6. ????? ???? ?? ???? ??????? ??? ?? ???? ?? tNewTable. ?????? ????? ???? ??????? ???????:
    1. ?? ?????? "????? ??????" ? ???? ??? ?????? ?????? ??? ???? ??????? ??? ?? ???? ??? ??? ???? ??????? ????????.

      ?????? ?? Access 2007? ???? ??? ???? ??????? ?? ??? ?????? ???? ??? ?? Microsoft Office ? ???? ??? ??? ???? ?? ?? ???? ??? ??? ???? ????.
    2. ?? ???? ?????? ??? ????? ???? tNewTable ?? ?????? ??? ???? '???????' ??? ??? ?? ???? ??? ?????.
  7. ?? ?????? "????? ??????" ? ???? ??? ????? ???? ?????? ???? ??? ???? ?? ?? ???? ??? ????? ???? ????? ???? ?? ????? ????? "?????".

    ?????? ?? Access 2007? ???? ??? ????? ????? ?? ?????? ????? ??? ????? ??????? ?????.
  8. ????? ?? ??? ??? ????? ????.
  9. ????? ??????? ???_????? ?? ??? ???? ??? ????? ????? ??????:
    On Error GoTo EH:
    'Code only works if ADP is connected.
    If CurrentProject.IsConnected Then
        Dim bNewAppRole As Boolean, strTSQL As String
        Dim strRoleName As String, strPW As String
        strRoleName = "AppRoleName"
        strPW = "Password"
        'Call function to create app role.
        bNewAppRole = AddNewAppRole(strRoleName, strPW)
        'Test to see if it failed.
        If bNewAppRole = False Then
            Exit Sub
        End If
        MsgBox "New Application role '" & strRoleName & "' created", vbInformation
        'Create command to grant permissions.
        strTSQL = "Grant Select on tNewTable to " & strRoleName
        'Send the command.
        Application.CurrentProject.Connection.Execute strTSQL
        MsgBox "Select permissions granted on tNewTable for " & strRoleName
    Else
    MsgBox "ADP must be connected to SQL Server"
    End If
    Exit Sub
    EH:
    MsgBox Err.Number & ": " & Err.Description, vbCritical
    					
  10. ????? ???? Visual Basic ?????? ??? ???????.
  11. ??? ??????? ??? ?? ??????? ??????? ??? ???????.
  12. ???? ??? ?? ????? ?????? ????????? ???????? ????????.

    ???? ???? ????? ????? ?????? ??????. ???? ??? ??? ????? ??? ??????? ???? ??? ??? ???? ?????? ??? ?????? ??? ???? tNewTable.

????? ??? ???????

complication ??????? ??? ??????? ????? ??????? ?? ?????? Access ?????? Access ????? ??????? ?? SQL Server ??????? ?????? ????????. ????? ?????? ?????? ??? ????? ?? ??? ??????? ?????? ???? ???? ????? sp_setapprole ?? ???? ???? ????????? ???????. ?????? ???????? ?? ??? ?? ????? ???????:

  1. ?????? ?????? ???? ???????? ?? ????? ?????? ?????? ??? ??? ????? ???????? ?????? ?????? ????????.

    ??????? ??? ??????? ? ??? ????? ?????????? ??????? ????? ? ????? ??????? ??????? ? ???????? ??????? (???? ??? ??????? ??????? ????).

    ?????? ?? ??? ?????? ??? ????? ??????? ?????? ??????? ?????? ?????? ??????? ?????????.
  2. ??????? ??? ??????? ? ??? ????? ?????????? ??????? ????? ? ????? ??????? ??????? ? ???????? ??????? (???? ??? ??????? ??????? ????).

    ?????? ?? ??? ?????? ??? ????? ??????? ?????? ??????? ?????? ?????? ??????? ?????????.
  3. ?????? ?? ??? ?????? ??? ????? ??????? ?????? ??????? ?????? ?????? ??????? ?????????.

??? ????? ?? ??? ???? ?????? ??? ????????? # 2 ? # 3 ?????? ?????? ? ???? ????? ?????? ?? ??? ????? ??????? ?????? ?? ???? ??????? ??? 1. ????? ????? ??? ??????? ??? ????? ????? ?? ??????? ?? ??? ????? ?????? ??? ?? ???? ????? ????? ???????? ?????? ?? (??? ???? ??????? ????? ???? ????? ???) ??????? ?????? ????? ???????? ????? ?? ???????? ??? ?????? "????? ??????" ???????.

?????? ??????? ??????? ????? Access ????? Northwind ??? ????? ????? ??? ????? ?? ????? ??????? # 2 ? # 3:

  1. ?? ?????? "????? ??????" ? ???? ??? ????? ???? ?????? ???? ??? ???? ?? ?? ???? ??? ????? ???? ????? ???? ?? ????? ????? "?????".

    ?????? ?? Access 2007? ???? ??? ????? ????? ?? ?????? ????? ??? ????? ??????? ?????.
  2. ????? ???? ????? ??? ????? ?? ?????? ?????? ?? ?? ?????? ??????? ??? ???? ??????? ??? lst_AppRole.
  3. ????? ?? ??? ??? ???????.
  4. ????? ??????? ???_????? ?? ??? ???? ??? ????? ????? ??????:
    On Error GoTo EH
        'This avoids a message that no records were returned.
    DoCmd.SetWarnings False
    Dim TSQL
    TSQL = "EXEC sp_setapprole 'AppRoleName', {Encrypt N 'Password'}, 'odbc'"
        'This sets the app role on Connection #2.
    Application.CurrentProject.Connection.Execute TSQL
        'This sets the app role on Connection #3.
    lst_approle.RowSource = TSQL
    lst_approle.Requery
    DoCmd.SetWarnings True
    MsgBox "The application Role is now in effect.", vbInformation
    Exit Sub
    EH:
    MsgBox Err.Number & ": " & Err.Description, vbCritical
    					
  5. ????? ???? Visual Basic ?????? ??? ???????.
  6. ??? ??????? ??? ?? ??????? ??????? ??? ???????.
  7. ???? ??? ?? ????? ?????? ????????? ???????? ????????.

    ???? ???? ???? ????? ???? ???? ??? ??????.
  8. ?? ?????? "????? ??????" ? ???? ??? ????? ???? ?????? ?? ?? ???? ?????? tNewTable.

    ?????? ?? Access 2007? ???? ????? ??????? ??? ?????? tNewTable ?? ??? ??????.
  9. ????? ??? ??????? ??? ?????????.
???? ??? ????? ?????? ????? ????????? ?????? ?? ???? ????? ??? ??? ??? ???? ?????? ?????. ???? ??? ???? ?????? ??? ??????? ???? ????? ??? ??? ???? tNewTable ???? ?? ????? ???????.

??? ??????? ???? Access ???????? ?? ???? ????? ???????? ???? ????? ??? ????? ????? ???????? ?? ????? ???????? ???. ?????? ?????? ????? # 1 ?????? ???????? ???? ??? ?????? ??????. ??? ????? ??????? ????? ??? ???? ??????? # 2 ?3 # ????? ???????? ??? ???? ??? ???????? ???? ?? ??? ? ??? ?? ?? ??? ?? ???? ???? ?????? ??? ???? ?????? ????????? ?? ?? ???? ???? ?????? ??? ???? ?? ???????? ???? ?? ????. ?? ????? ??? ?? ???? ??? ????? ??? ??????? ???? ????? ????????.

??? ???? ??????? ??? ??? ???? tNewTable "????" ??? ???????? ?????? ?????? ?? ????? ?????. ????? ??? ??? ????? ???? ?? ???? ?????? ?? ???? ???????? ?????? ??? ??? ??? ?? ??? ???????. ??? ???? ?? ???? ???? ???? ??????? ??? ?? ?????? ????? "?????" ?? "????? ??????? ?? ????? ???. ???? ?????? ???? ?????? ??? ?????? ?? ???.

???? ?????? ?????? ?? ????? ????? ????? ???????? ???? ?? ????? ??? ?????? "????? ??????". ???????? "????? ?????? ??? ???? ?????" ? ????? ?????? ?? ???????? ???? ???????? ?????? ???.

???? ? ???????? ?????? ??????

?? ???? ??????? ???????

???? ?? ?????? ????? ?????? Access ?? ????? ?????? ??? ????? ??????? ???? ?????? ??????? ??????. ???? Access ???? ????? (???? ??? ??????) ?????? ????? ???? ??? ???? SQL ??? ??????? ?????? ????? ??????? ?????? ?? ??????? ?????? ??? ???????? ???? ???? ??????? ?????? ?? ??????? ???????. ???? ??? ??? ??? ??????? ??????? ??? ??????? ????? ?? ??? ????? ??? ?????? ??? ?? ??? ???? ?????? ????? ???? ????? ????????. ???? ????? ??? ??? ???? ?? ?? ???? ??? ????? ???? ???????? ????? ??????? ??????? ??????? ???? ????? ??? ????? ????? ???????. ?? ???? ??? ?? ???? ???? ???? ???? ??? ?????? ??????? ?? ?????? ???????? ??????? ???????. ??? ?? ??????? ?????? ????? ??? ??????? ????? ??????? ?? Access.

?????? ?? ????

??? ???? ???? ??? ???? ?? ????? ??? ????? ??? ?? ???? ??? ????? ?? ????? ???? ?? ??????? Access ?????? ?? ??? ??? ??? ??? ?????? ?? ???? ????? ???????? ??? ??????? ??? ?????? ?? SQL Server. ??? ???? ????? ???????? ?????? ????? ????? ??? ??????? ????? ??? ????? ??? ??? ?? ??? ???? ?????? ????? ??? ???? ???????? ???????.

??????? ?????? ??? ??? ??????? ?? ????? ??????? ?????? SQL ???????? ????? ????? ??????? ?????????. ??? ???? ??????? ?????? "????? * ?? ViewName" ????? ?? ???? "ViewName" ?? "Exec StoredProcedureName" ????? ?? ???? "StoredProcedureName." ???? ???????? ???? ?????? ?????? SQL ???????? ?????? ??? SQL Server ??????? ???????? ???????? ???????? ??? ???????? ??? ???????.

??? "????? ???????? ????????"

??? ????? ?? ????? ?????? ??? ????? ???????? ????????. ???? ??????? ?? NorthwindCS ??? ????? ?? ?????? ????? ???? ????????. ????? ???? ??? ????? ?? ?????? ???? ???. ??? ????? ???? tNewTable ?? ??? "????? ??? ?????????" ?? ??? ??? ????? ??????? ????? ??? ?????? ?? ??? ??????? ??????? ???? ?????? ??? ??????? ?????? ?? ??? ??????. ??? ???? ????? ???? ?? ?? ???? ??? ????? ??? ??? ??????? ??? ??? ????? ?? ?????? ??? ??? ????????.

?????? VBA

???? ???? ???? ?????? ?????? ??? ????????? ?? ??????? ???? ???? ???? ?????? knowledgeable ?? ?????? ??? ????? ??? ??? ??????? ????? ?????? ?? ????????? ???????? ?????? ??? ?? ??????? ??? ????????? ??? ?????? ??? ?????? ?????? ??? ???? SQL ?? ????? ???. ????? ?? ???????? ??????? ??????? ?? ADP ??? ??? ADE ???? ????????? ???????? ?????? ??? ???? ?????. ???? ? ??? ???? ???? ??? ????? VBA.

?????

?????? ??? ??????? ?????? ??? ????? Microsoft Access 2000 ?? ??? ???????? ???? ??? ??? ??????? ????? ?????? ?? "????? ??????? ?? Microsoft:
318816ACC2000: ????? ??????? ????? ??????? ?? ??????? Access ? SQL Server 2000 ??? ?????? Engine (MSDE 2000)
????? ?? ????????? ??? GRANT ???? ??? ??? SQL Server ???????. ??? SQL Server ??????? ????? ?? ???? Microsoft ?????? ??? ?????:
http://www.microsoft.com/sql/techinfo/productdoc/2000/default.asp

???????

???? ???????: 308312 - ????? ??? ??????: 10/???? ?????/1428 - ??????: 6.2
????? ???
  • Microsoft Office Access 2007
  • Microsoft Office Access 2003
  • Microsoft Access 2002 Standard Edition
????? ??????: 
kbmt kbexpertiseinter kbinfo kbprogramming kbadp kbvba kbhowto KB308312 KbMtar
????? ????
???: ??? ????? ??? ?????? ???????? ?????? ????? ???? ????? ?????????? ????? ?? ????????? ?????? ????. ???? ???? ?????????? ???? ?? ???????? ???????? ?????? ????????? ????? ????????? ???????? ????? ???????? ?????? ?? ?????? ??? ?? ???????? ???????? ?? ????? ??????? ?????? ??? ??????? ?????? ??. ?????? ?? ???? ??? ??????? ???????? ????? ?? ???? ????? ?????? ??? ????? ??? ????? ??????? ?? ????? ?? ?????? ??? ??? ??????? ??????? ?? ????? ????? ????? ????? ?????. ?? ????? ???? ?????????? ??????? ??? ????? ?? ??????? ?? ????? ?????? ?? ??? ????? ?? ????? ??????? ?? ???????? ?? ??? ???????. ???? ???? ?????????? ???????? ??? ????? ?????? ??????? ??????
???? ??? ????? ??????? ?????? ??????????308312

????? ???????

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com