Access ??? ???? ?? ?? ???????? ?? ??? SQL ????? ?? ??? DSN ?? ??????? ????? ?? ??? ???? ????

???? ID: 892490 - ?? ???????? ?? ?????? ??? ?? ?? ???? ???? ???? ??.
???? ?????? ???????????? ?????? ???????? KB ?? ???????? ????? ?? ??? ???? ????? ????
?????: ?????? ???????? ??????, ?????????????? ?? multiuser ???? ???

?? ???? ?? Microsoft Access ??????? (.accdb ?? .MDB) ?? ??? ???? ???? ???? ???
??? ?? ??????? ???? | ??? ?? ??????? ????

?? ????? ??

?????

?? ???? ????? ???? ?? ?? ??? ???? ????? ??? (DSN) ?? ????? ???? ???? ?? ?? Microsoft Access ??? ???? ?? ?? ???????? ?? ??? Microsoft SQL Server ???? ?? ??? ??? ??????? ????? ?? ??? ???? ????? ?? ?? ???? ?? ??DSN ????????? ??? ?????? ?? ?? ?? ???? ??? Microsoft Office Access 2007 ???, Microsoft Office Access 2003 ?? ???, ?? Microsoft Access 2002 ???? ?? ??? ???? ???? ????
????? ?? ???? ???? | ??????????? ???

???? ???????

?? Microsoft Access ??? ?????? SQL Server ???????? ????? ?? ??? ??? DSN ?? ????? ?? ???? ??? ????? ?? ?? ???? ???? ???????? ?? ??? ??????? ?? ????, ?? ?? ???????? ?? DSN re-create ???? ????? ?? ????????? ?????? ???? ?? ???? ?? ?? ???? ??? ?? ?? ???? ???????? ?? ????? ???? ?? ???? ?? ?? ????????? ??? ?? ???? ???? ??, ?? ???? ?? ?? ???????? ???? ?? ???? ?? DSN ?????? ??? ?????? ?????, ???? ?? ?? ???????? ???? ???? ?? ?????? SQL ?? ?????? ???? ?? ??? ??????

When you want to create a link to a SQL Server table but do not want to hard-code a DSN in theData Sourcesdialog box, use one of the following methods to create a DSN-less connection to SQL Server.

Method 1: Use the CreateTableDef method

TheCreateTableDefmethod lets you create a linked table. To use this method, create a new module, and then add the followingAttachDSNLessTablefunction to the new module.
'//Name     :   AttachDSNLessTable
'//Purpose  :   Create a linked table to SQL Server without using a DSN
'//Parameters
'//     stLocalTableName: Name of the table that you are creating in the current database
'//     stRemoteTableName: Name of the table that you are linking to on the SQL Server database
'//     stServer: Name of the SQL Server that you are linking to
'//     stDatabase: Name of the SQL Server database that you are linking to
'//     stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
'//     stPassword: SQL Server user password
Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)
    On Error GoTo AttachDSNLessTable_Err
    Dim td As TableDef
    Dim stConnect As String
    
    For Each td In CurrentDb.TableDefs
        If td.Name = stLocalTableName Then
            CurrentDb.TableDefs.Delete stLocalTableName
        End If
    Next
      
    If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
    Else
        '//WARNING: This will save the username and the password with the linked table information.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
    End If
    Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
    CurrentDb.TableDefs.Append td
    AttachDSNLessTable = True
    Exit Function

AttachDSNLessTable_Err:
    
    AttachDSNLessTable = False
    MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description

End Function
??? ???? ?? ???AttachDSNLessTablefunction, add code that is similar to one of the following code examples in theAutoExecmacro or in the startup formForm_Open?????:
  • ?? ?? ????? ????AutoExecmacro, call theAttachDSNLessTablefunction, and then pass parameters that are similar to the following from the RunCode action.
        AttachDSNLessTable ("authors", "authors", "(local)", "pubs", "", "")
  • When you use the startup form, add code that is similar to the following to theForm_Open??????
    Private Sub Form_Open(Cancel As Integer)
    If AttachDSNLessTable("authors", "authors", "(local)", "pubs", "", "") Then
        '// All is okay.
    Else
        '// Not okay.
    End If
End Sub
    ???:You must adjust your programming logic when you add more than one linked table to the Access database.

Method 2: Use the DAO.RegisterDatabase method

TheDAO.RegisterDatabasemethod lets you create a DSN connection in theAutoExecmacro or in the startup form. ?? ???? DSN ??????? ?? ??? ???????? ?? ??????? ???? ??, ?????? ?? ??? ??? DSN ??????? ??? ?? ?? ?????? ?? ?? ???? ??? ???? ?? ???? ?? ?????, ?? ??? ?? ?? ??????? ?????, ?? ???? ??? ????? ??????CreateDSNConnection?? ??????? ???? ?? ??? ????? ?????
'//Name     :   CreateDSNConnection
'//Purpose  :   Create a DSN to link tables to SQL Server
'//Parameters
'//     stServer: Name of SQL Server that you are linking to
'//     stDatabase: Name of the SQL Server database that you are linking to
'//     stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
'//     stPassword: SQL Server user password
Function CreateDSNConnection(stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String) As Boolean
    On Error GoTo CreateDSNConnection_Err

    Dim stConnect As String
    
    If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "Description=myDSN" & vbCr & "SERVER=" & stServer & vbCr & "DATABASE=" & stDatabase & vbCr & "Trusted_Connection=Yes"
    Else
        stConnect = "Description=myDSN" & vbCr & "SERVER=" & stServer & vbCr & "DATABASE=" & stDatabase & vbCr 
    End If
    
    DBEngine.RegisterDatabase "myDSN", "SQL Server", True, stConnect
        
    '// Add error checking.
    CreateDSNConnection = True
    Exit Function
CreateDSNConnection_Err:
    
    CreateDSNConnection = False
    MsgBox "CreateDSNConnection encountered an unexpected error: " & Err.Description
    
End Function
???:???RegisterDatabase?????? ?? ??? ?? ??? ???? ??, DSN ?????? ???

??? ???? ?? ???CreateDSNConnection?????, ??? ????? ??? ???????? ?? ?? ?? ???? ???? ?? ?? ??? ??????AutoExec?????? ?? ????????? ?? ??? ???Form_Open?????:
  • ?? ?? ????? ????AutoExec??????, ???CreateDSNConnection??????, ?? ???? ??? ??? ???????? ?? ?? ????? ?? ???? RunCode ?????? ?? ??? ????
        CreateDSNConnection ("(local)", "pubs", "", "")
  • ?? ?? ????????? ?????? ?? ????? ????, ??? ?? ????? ?? ???? ???? ?? ?? ??????Form_Open??????
    Private Sub Form_Open(Cancel As Integer)
    If CreateDSNConnection("(local)", "pubs", "", "") Then
        '// All is okay.
    Else
        '// Not okay.
    End If
End Sub
???:?? ???? ????? ?? ???? ??? ???? ?? ?? ??? SQL ????? ?? ???? ?? ?? ???????? Access ??????? ??? DSN ?? ??? ??? "myDSN" ?? ????? ???? ????
????? ?? ???? ???? | ??????????? ???

???

???? ID: 892490 - ????? ???????: 05 ?????? 2010 - ??????: 2.0
???? ???? ???? ??:
  • Microsoft Office Access 2007
  • Microsoft Office Access 2003
  • Microsoft Access 2002 Standard Edition
??????: 
kbprogramming kbconfig kbdatabase kbhowto kbinfo kbmt KB892490 KbMthi
???? ?????? ????????
??????????: ?? ???? ?? ???? ??????? ?? ????? ?? Microsoft ????-?????? ?????????? ?????? ?????? ???? ??? ??. Microsoft ???? ??? ????-???????? ?? ????-???????? ????? ?????? ?? ???? ???????? ???? ?? ???? ????? ????? ??? ?? ??? ?????? ?? ???? ???? ???? ??? ????? ??. ???????, ????-???????? ???? ????? ???? ???? ???? ???. ?????, ????????, ?????-???? ?? ??????? ?? ???????? ?? ???? ???, ???? ?? ??? ?????? ???? ???? ??? ????? ??? ?? ???? ??. Microsoft ??????? ??? ???? ?? ?????? ?? ??????????, ????????? ?? ??? ?????? ?? ???? ????? ?? ???? ???????? ?? ??? ???? ????? ?? ??? ????????? ???? ??. Microsoft ????-?????? ?????????? ?? ????? ?????? ?? ?? ??? ??.
?????????? ?? ??????? ????????? ??????? ??:892490
(http://support.microsoft.com/kb/892490/en-us/ )
????? ?? ???? ???? | ??????????? ???

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

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