Yes and no.
first you both should be using the Database Diagrams to maintain the Schema
of the Database and do design on the Tables. You can break down the diagrams
to functions. that you would have views and stored procedures in.
yes if you are doing different objects on the SQL server(Tables, views,
Stored proceeedures). You are working on different VBA classes or modules.
yes if the ADP each developer is working on is not connected to the VSS, and
one developer is responsible to keep the ADP in the VSS updated with the
*.bas and *.cls files the other developer put on the VSS.
Then you put the Classes and Modules in the VSS for others to import into
their copy of the ADP.
no if you are working on the same VBA code module and Class or SQL server
objects.