I'm working in Access 2003 using a DSN file to link to SQL Server 2005. I've been updating tables in SQL Server, so I then have to delete the linked table from the Access .mdb file and then re-link it so that it sees the changes that I've made (otherwise, the Access file won't see new columns that I've added).
Anyways, I go through this process and things work fine, but when I open the Access .mdb, it pops up a login screen and I have to type in a password before the file opens. And if I use the old .mdb file (pre table re-linking), it just opens up without asking for the password. This is frustrating because my end-users aren't used to having to enter a password every time they open up the database. So...
1) Why does this occur?
2) How can I get around this--make the .mdb file open up WITHOUT it asking for a login after re-linking tables
3) Am I going about this the right way? If edits are made to a table in SQL Server, do you have to delete the linked table in Access and then re-import/link the table to see the updates? This kind of sucks, because in Access, you can only delete one table at a time and in this database there are many many tables.
4) Is there a way to update the links of ALL the linked tables in the .mdb at once--I already tried the Linked Table Manager, but for some reason, NONE of my database logins would work (even sa). So, I end up deleting the tables using VB code, and then I re-import them by going to File>Link Existing Data. But then, that damn login pops up every time...arrrgh!