Sim, você pode definir relações de chave estrangeira no Access. Contanto que você não esteja fazendo nada particularmente incomum, os relacionamentos do SQL Server normalmente se adaptam muito bem aos relacionamentos do MS Access.
Aqui estão algumas exceções deste link:
Microsoft: Como definir relacionamentos entre tabelas em um banco de dados do Access
A one-to-many relationship is the most common kind of relationship. In this kind of relationship, a row in table A can have many matching rows in table B. But a row in table B can have only one matching row in table A. ... In the relationship window in Access, the primary key side of a one-to-many relationship is denoted by a number 1. The foreign key side of a relationship is denoted by an infinity symbol.
In a many-to-many relationship, a row in table A can have many matching rows in table B, and vice versa. You create such a relationship by defining a third table that is called a junction table. The primary key of the junction table consists of the foreign keys from both table A and table B. For example, the "Authors" table and the "Titles" table have a many-to-many relationship that is defined by a one-to-many relationship from each of these tables to the "TitleAuthors" table. The primary key of the "TitleAuthors" table is the combination of the au_ID column (the "Authors" table’s primary key) and the title_ID column (the "Titles" table’s primary key).