Linked Tables with standard security

[SemperFiDownUnda]

I've recently upsized a Access XP database to SQL Server. Because of the network we have to use standard security. When I upsized the database I used an standard account with appropriate access to the database. The problem is the account info is not being stored with the linked tables meaning it tries to connect via intergrated security then fails and throws up a login box.

I've tried using this code provided by MS to stop the login box from popping up.

   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:\Work\SnrMedSt2\SnrMedSt.mdb")
   strConnect = UCase(db1.TableDefs("tblCampus").Connect) & _
                ";Username=SnrMedStUser;PWD=5nrM3d5t"
   Set db2 = OpenDatabase("", False, False, strConnect)
   db2.Close
   Set db2 = Nothing
   Set rs = db1.OpenRecordset("tblCampus")
   Debug.Print rs(0)
   MsgBox "Recordset Opened Successfully"
   rs.Close
   db1.Close
   Set rs = Nothing
   Set db1 = Nothing

[code]
[/color]

but this doesn't work as the linked tables are causing the error to get thown BEFORE an code is actually run.

How can I get this working

 

[SemperFiDownUnda]

Ooops sorry wrong forum. Reposting here thread181-741000