How To Bypass Login Prompt When Opening Linked Table

MORE INFORMATION

1. In Access, create a new database, db1.mdb, and a linked table, dbo_authors, from SQL Server Pubs database.
2. In Visual Basic, start a new project and choose "Standard EXE." Form1 is created by default.
3. In Access, create a new database and create a new form (Form1).
4. In Visual Basic 4.0 and later, add a Reference to:

      Microsoft Data Access Object 2.x   (VB4 16-bit)
      Microsoft Data Access Object 3.x   (VB4 32-bit; VB5)
   					

5. Paste the following code in the General Declarations section of Form1:
   
   Note You must change Username=<username> and PWD=<strong password> to the correct values before you run this code. Make sure that Username has the appropriate permissions to perform this operation on the database.

         Sub Command1_Click()
            Dim db1 As Database
            Dim db2 As Database
            Dim rs As Recordset
            Dim strConnect As String
   
            '*** You have to modify the path to where db1.mdb is located
            Set db1 = OpenDatabase("C:\MyTest\db1.mdb")
            strConnect = UCase(db1.TableDefs("dbo_authors").Connect) & _
                         ";Username=<username>;PWD=<strong password>"
            Set db2 = OpenDatabase("", False, False, strConnect)
            db2.Close
            Set db2 = Nothing
            Set rs = db1.OpenRecordset("dbo_authors")
            Debug.Print rs(0)
            Debug.Print "Recordset Opened Successfully"
            rs.Close
            db1.Close
            Set rs = Nothing
            Set db1 = Nothing         
         End Sub
   					

1. You must provide correct login information, User ID and Password, in strConnect to establish the connection.
2. If you know which DSN the table is linked to, you can hard-code the value of strConnect.
3. The Microsoft Jet database engine will first try to log you in with the same user ID and password that you log into the Jet database with (default is Admin/no password). If you make the local login match the server login, you will not get any login prompts.
4. Microsoft SQL Server can integrate its security mechanism with Microsoft NT domain accounts. If the user has a valid account in the domain, you will not get any login prompts.