Beware. If your legal department knows anything at all, they will tell you not to do this. Every Access license is single-user, single machine. Running the executable part from a server violates the license agreement if you bought it off-the-shelf at your favorite computer software store. And unless one has come out recently, there IS no server-shared, multi-user version of Access for this purpose. It is still one-to-one:seat-to-license. I would defer to Pat Harman's expertise on this one - or perhaps G Hudson or some of the other frequent posters who do Access work as their primary assignment. I kind of backed into Access at my site.
Now the next part... I have to disagree with TREASON's response. I'd be very surprised if you can get it work right when run remotely from a server because it still depends on YOUR registry settings. It will still look for .DLL files on YOUR workstation unless it was set up to uniformly look for all files using the "server\\path\file" notation for EVERYTHING. And I don't know that you can do that for every file it wants. I would see lots of pitfalls and doggone near insurmountable obstacles. It would take a phenomenally good session with RegEdit or RegEdit32 to reset everything you need - if you can even FIND everything you need. Which I doubt. (No offense intended... I don't know that I could do it either.)
I can understand that your IT folks don't like Access ... but the question is whether they dislike it for the wrong reasons. Do they have a performance gripe, a security gripe, a cost-per-seat gripe, or something else? Performance, security, and cost are the three most common objections. Let me take a shot at each.
If they worry about the network load, having an Access FE and an SQL Server BE is the correct way to allay their performance fears. Having an application developed in Access doesn't mean you can't up-scale the BE to SQL server. In fact, it is one of the recommended up-scaling pathways from MS. Access FE and BE together can, indeed, be a nightmare of performance, particularly for poorly designed queries. Making the BE into SQL Server speeds up query performance significantly. The bigger the database and the more selective a query can get, the better this performance differential pays off. If a typical query would return no more than 10% of a table, switching to an SQL BE makes your app 10 times faster over the network. It's that simple most of the time.
If they worry about security, you can set up workgroup security for the Access part and use SQL server's internal security for another layer of protection. That usually is enough to take care of internal security. Search this forum for any article that has "Workgroup" in its title. I would guess there are hundreds of threads available on this one topic. More info than you ever wanted to know, I'm sure.
As to the other part, as long as your site has a good firewall that blocks external traffic to/from that server, you are OK. You can block all traffic to/from the server with regard to the outside world. Then your only security issue becomes insiders, which is why you set up the workgroup anyway.
Now, if your IT folks have allowed a server to exist that is inside the firewall but talks to things outside the firewall without making it a separate DMZ, they have no room to gripe at YOU for security issues. Having such a thing violates every rule of setting up a secure office perimeter. (You may show them this comment if you wish. IT folks, if you doubt that I know whereof I speak, you should know that I teach Windows Security for Systems Administrators for the U.S. Dept. of the Navy.)
If their gripe is setting up the internal security, here is what they should do...
Set up a relatively high-level folder. Like drive-letter:\ACProj\ - or at worst, second-level, like drive-letter:\CompanyShared\ACProj\.
Set up a group with all of your users. Give those users "MODIFY" access THROUGH THEIR GROUP MEMBERSHIP to the ACProj folder and its contents, propagated to all existing files, and with that Discretionary Access Control Entry set to "Inheritable." If they give you a second-level folder, your group needs Advanced setting "TRAVERSE" on any higher level folders.
They can also remove EVERYONE group from the DACL if they like. (If they didn't know to do this and they are griping about security, shame on them...). Either just remove it flat-out, or replace it with group "Authenticated Users" and give them READ access. If they prefer to work at the Advanced permissions level, Access users need to have everything except "Change Permissions" and "Take Ownership". If they get really nit-picky, you can do without propagating "Delete Subfolders and Files" to a child of your application folder, but you need to be able to create and delete files in the folder where Access lives. (On the other hand, if this expands to SQL server, that uses a different method of security. You need an SQL Server expert to advise you on that one...)
If their gripe is cost-per-seat, they may have you... except you can ask: How much will it cost to now go back to retrofit a user-friendly interface using something OTHER than Access as the front end? You have already worked on the app interface. Are they going to force you to spend more engineering dollars to re-invent a perfectly round wheel? Not only that... do they already have an SQL Server administrator lined up? Someone TRAINED for such a task? How much will THAT cost?
The IT folks need to be visited by the accounting folks for a little "Come-to-Jesus" meeting in that case. Or did they think that SQL server's interactive SQL interface was so very nice that anyone could use it without training and without making a costly mistake? Let's see now... "Was the keyword I wanted... DELETE or DROP?"
Access is DESIGNED for rapid application development, but it has hooks to extend its service life beyond the up-scale to SQL Server as a BE. "Extending service life" means "spending less money" - which should perk up your finance people's ears. It is DESIGNED to hide details from the casual users of the application. So that they DON'T have to remember whether the keyword was DROP, DELETE, or ERASE. It was DESIGNED to minimize cost of use in terms of training and of protecting against accidental data loss - by not showing folks some of the ways it is possible to so badly SCREW UP your DB. Between reducing (but NOT eliminating) training needs and reducing (but NOT eliminating) expensive maintenance, an Access front end can actually save you money vs. re-inventing a complex VB wheel. Saving money is NOT always something an IT guy can ignore. I've never yet seen a set of IT folks that could compleletly win an argument with the bean-counters who cut their paychecks.
If the IT problem is something else, I need to know what it is before I can address it.
