I'm taking over a project with a VB middle and an Access Backend. The client wants the DB upsized to SQL Server but wants the VB middle to be able to work against use either the existing Access backend or the new SQL Server backend. I admit I know little about this and it is past time I learn.

Is this feasible? I don't see the need of maintaining the backward compatibility with Access but would like to know your opinion.

What would you recommend to the client?

There is lots of code like this:

strSQL = "SELECT EMPLOYEE.*, FACILITY.*, COSTCENT.*, DIVISION.*, EMAIL.*, SupervisorLink.SupervisedCostCenterID, SupervisorLink.SupervisedCostCenterDescription, WEBSITEINFO.* "
strSQL = strSQL & " FROM DIVISION, Facility, CostCent, Employee, Email, WEBSITEINFO, SupervisorLink "
strSQL = strSQL & " WHERE Division.DIV_PK = Facility.FAC_FacilityDIVFK "
strSQL = strSQL & " AND FACILITY.FAC_PK = Costcent.COS_FacilityFACFK"
strSQL = strSQL & " AND COSTCENT.COS_PK = Employee.EMP_CostCenterCOSFK"
strSQL = strSQL & " AND Email.EMA_PK = Facility.FAC_EmailEMAFK"
strSQL = strSQL & " AND Facility.FAC_WebsiteInfoWEBFK = WebSiteInfo.WEB_PK"
strSQL = strSQL & " AND SUPERVISORLINK.SupervisorID = EMPLOYEE.EMP_PK"
strSQL = strSQL & " AND EMPLOYEE.EMP_LoginID = '" & strLoginID & "' "
strSQL = strSQL & " AND EMPLOYEE.EMP_Active"
strSQL = strSQL & " AND FACILITY.FAC_Active"
RS.Open strSQL, DataConnectionString(), adOpenStatic

EMPLOYEE.EMP_Active and FACILITY.FAC_Active are logical fields in access and this query fails against SQL server.

How would you deal with this and other problems?