Problems with linked tables
Just a couple of things you might want to look at:
1. When you linked the tables from SQL Server into the MSAccess application did you check the Save Password box?
2. Is the login you're using or does your loginID from LDAP show in the Security Manager in SQL Server and do you also have it in the database for Users with permissions to the Public role? (it usually defaults to this)
3. Sometimes I found that I had to go into the Roles (Public) and check (green check mark) all the user created tables (checking ALL columns in that row - including the RFI column.)
4. When you linked the SQL Server tables, were you prompted to identify a primary key in the linking table? (this goes back to hmscott's email)
5. Are all the DSN's the exact same name for anyone accessing the tables. If you have a different DSN Name for other users, you will have some problems.
6. How is your security set up in SQL Server (Mixed Authentication?)
7. You can also try creating a TimeStamp field on the tables but I doubt this would be an issue as it's usually a problem with having Memo (Text) fields in the table.
8. When you created the DSN for ODBC, did you create this as a System DSN so any user's who logs in to your machine would have permissions? Or are you using a User or File created DSN?
In regards to re-writing this as an ADP, I actually like the method of linking in the tables or writing the code with a "cnn" connection. Our forms are all unbound in the MSAccess application and utilize Write/Update/Delete functions. When a customer is retrieved from the search menu, the SQL Statement fires (which spikes the server a little), returns the values to the unbound form, and then closes the SQL Connection. If something is updated, a checkbox (called: DataUpdated on the form) is marked as true to indicate that the Update function needs to be called (or you could have a specific "Save" button on the form.) This method has worked great for us to make updates to the SQL Server tables without having to worry about someone having the MSAccess ADP or MDE file open and on a specific record (where they left it and went to lunch.) This makes it more difficult for SQL Server to update the new/updated field names in the table and sometimes ends up hanging up or takes an extremely long time and we have to find the user's in the database and have them close out so SQL Server can complete the update. Thus I've become a fan of writing unbound forms which has worked almost flawlessly (and very fast) for us even with external users connecting via Citrix or Remote Desktop Connection. (my thoughts on this is that it's all on how you write your code and also making sure your hardware is sufficient). Some people say MSAccess isn't an enterprise solution but if the code's written correctly, I believe you can make it act like an enterprise solution.
I'm attaching a program which automatically updates/creates DSN's for users on their machine from SQL Server (assuming that the user's login has permissions to do this.) You will need to delete the current dbo_XXXX linked tables in the attached Mdb and add in 1 linked table to the database you want a DSN created for. This makes it easy to create DSN's for other users, making sure the DSN name is the same as all you need to do is open the MDB and the DSN is created. It's not a fancy application but it does do the job.
I hope any of this helps.
Last edited by pkstormy; 11-09-05 at 12:02.
Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)