November 27, 2006 at 4:02 pm
I am re-posting this because I really couldn't make heads or butts out of the answers it received the first time. I really need help with this, but I need it in major idiot-speak. If anyone could help...I would worship you from afar!
Original Message:
I recently moved my databases to SQL 2000 from Access 2000. Users are still using Access as their front-end. I am finding that the ODBC connection does not "stay" when another user accesses the database. I keep having to use the Linked Table Manager to reconnect. What can I do to keep everyone's link connected?
-Sharon
|
|
November 28, 2006 at 4:22 am
Hi Sharon,
Could you please provide me with more information, such as how are you connecting, i.e. are you using file DSN's or system DSN's for your ODBC connections, and the type of user that is being used for the ODBC connection, i.e. windows authentication or SQL Server authentication.
Hopefully I can then help. I currently use Access to connect to SQL Server in a multi-user environment with no major issues.
Mark
November 28, 2006 at 5:25 am
Sharon have you thought about converting the database to an Access Project (adp). It uses SQL Server as the back end and Access as the front. Your tables and queries are stored on the SQL Server and the forms and modules are stored in Access. I use this in a multiuser environment and have had no problems. You would have to either put the Access portion on a shared drive or put a copy of the front end on each user's computer. But if you do that then every update you make you will have to copy onto each computer. Access has a wizard to convert from an Access mdf to an Access adf. There are some good books on this. One is by Rick Dobson titled SQL Server with Access 2000. I believe it's been updated for later versions.
November 28, 2006 at 9:24 am
We use File DSNs set up to use a "production" account and password (SQL Server Authentication). So everyone is using the same account and password. We found that there was still some instability in the connections if multiple users were trying to use the same database, so we did put copies of the Access database onto each persons "C" drive. We also installed a batch file (with icon on the desktop) to "refresh" the database from the network drive - so when we make updates, we just email the users to refresh their database. It works pretty well (as long as you move all complex queries to the SQL server; otherwise it can lock up the database...)
Haven't played much with the ADPs; we were converting to web-based interface, so the older Access one just needed to work until we finished the new interface; the Access program is mostly used now for reporting, and for list maintenance.
Steph Brown
December 4, 2006 at 10:54 am
HA! I figured it out! Thank you for all the wonderful words of wisdom! The answer was really dumb...as all IT answers usually are. The name of the odb connector on the desktop has to be exactly the same on the server and all other desktops using the same database.
See..it was dumb.
-Sharon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply