I strongly agree with NoelD and Kieth Henry - Access Data Projects (.adp) is the way to go for a number of reasons.
One of them is that each linked table uses a connection to SQL Server - with an .adp, you're generally using only two or so connections regardless of how many tables are in the database.
I refer to Access Database Projects as "Access on Steroids." As a longtime Access developer (I started in early 1993 with Access 1.0) and a big proponent of Access, I am convinced that using linked tables and DAO with SQL Server is a bad idea.
Stay with DAO for applications that will never migrate from JET - but for SQL Server front ends - go with ADPs and OLE DB.
Best regards,
SteveR