I am not sure if this is the right forum to post this question in but here it goes. I want to switch to using Access as a front end and MS SQLServer as a backend (not MySQL because of the amazing gobs of referential integrity in my current Access db). The upsizing works almost flawlessly(just a few foreign key errors) and it works well on my workstation xp test box. Right now we have a replicated Access db on a server and the users, up to the mid 30s in numbers, all connect and run the same replica via terminal server off the server. Unlike a previous thread, I only have problems with db corruption seldomly, when the internet connection between the two offices of my company in Kansas and California goes down unexpectedly. But, I sure would like to cut terminal server, that big security hole and the drain on server resources, out of the picture and have people connect to the front end, with just linked tables at the moment and not pass-through queries, through a network share that will, at the very least, make the workstations carry the memory load. But people trying to connect to the front end through the share get a 'ODBC CONNECTION FAILED...' error when they try to open a form or report. Do I have to code the ODBC connection in on database open or something? I have even made a login in SQLServer and given that login datareader and datawriter permissions in the backend and even went to the backend database in enterprise manager and granted rights to select, update, delete all tables in the backend database, and I can't manage to change things. Any ideas? Should I post this on the SQLServer forum? Thank in advance for your time and responses.
FYI Using RI links needn't preclude the use of MySQL - it can handle foreign keys, you just have to use the right table type (INNODB if my memory serves me right) compared to the default table type MyISAM