Anexar um "front end" do Access contendo consultas, formulários e relatórios a tabelas de dados armazenadas em um banco de dados do SQL Server é muito simples. Existe uma ferramenta chamada "Assistente de Migração do Microsoft SQL Server para Access" que transfere um banco de dados existente do Access para o SQL Server.
É muito questionável se você se beneficiaria disso. Um banco de dados "pequeno" com menos de 5 usuários simultâneos não deve ter nenhum problema de desempenho ou confiabilidade em execução em qualquer versão do Access para o Access 97. Eu pessoalmente tenho mais de 200 instalações de um sistema de acesso multiusuário muito complexo, muitas executando por mais de 10 anos, cada instalação com até 25 usuários simultâneos, sem problemas particulares.
Dito isto, o SQL Server é certamente mais seguro do que o mecanismo do Access, mas não deve ser necessário para o tipo de aplicativo pequeno que você descreve. O único problema real com a confiabilidade do Access é a confiabilidade da rede. Conexões ignoradas ou inconsistentes com a rede podem causar danos ao banco de dados, mas a corrupção quase sempre é facilmente corrigida apenas abrindo o arquivo de dados no Access e permitindo que ele se repare automaticamente.O desempenho do SQL Server definitivamente será melhor com centenas de usuários simultâneos, mas com 5 usuários a maioria das funções será tão rápida ou ainda mais rápida no Access. Lembre-se de que, embora cinco pessoas possam ter o aplicativo aberto, a única vez em que estão realmente transacionando com o banco de dados é quando carregam dados em um formulário ou relatório (executando uma consulta) ou salvam alterações nos dados. Observando seu trabalho, você quase certamente descobrirá que até mesmo ter duas ações simultâneas reais no banco de dados não é tão comum.
Quase todos os problemas de desempenho com aplicativos do Access se devem ao design incorreto, começando com as estruturas de dados e continuando com consulta incorreta e design de formulário, uso de macros (nunca) e / ou código VBA incorreto. A maioria dos novos usuários do Access não tem consciência da necessidade de dividir um aplicativo multiusuário em bancos de dados front end e back end separados. Este artigo explica por que isso é necessário e exatamente como fazê-lo. É bastante fácil - existe até um assistente no Access para ajudá-lo a fazer isso.
Se você não puder explicar ou resolver problemas de desempenho específicos com um aplicativo do Access, provavelmente deverá procurar ajuda aqui ou em outros fóruns descrevendo a natureza exata do problema. Há também alguns bons livros disponíveis - particularmente o Manual do Desenvolvedor do Access de Ken Getz, et. al. Embora bastante antiga (2002), esta é a "bíblia" para o design do Access e ainda 99% aplicável a versões mais recentes.
Boa sorte!