Due to Access size constraints we are in the process of replacing out current Access backend data MDB with SQL Server.
Doing some preliminary testing I created a SQL database from the existing Access MDB using SQL Server Migration Asssistant for Access. I linked the SQL tables to the Access front MDB. Most of the functionality is intact but I am having issues on certain VBA select queries.
Here is an example:
' Only pull active tax records
selectString = " Select [ID], [BRT] , [PrincipalAmt], [PenaltyAmt], [InterestAmt], [LienAmt], [AttyFeesAmt], [EligExpAmt], [DateOfNumbers], [PayoffAmount], [DatePayoffCalculated], [PayStausID], [UserRevised], [DateRevised] " & _
" from tblTaxHeader Where [Active_YN] = " & cYesNum
' Is this for a specific BRT
If passedBRT = 0 Then
selectString = selectString & " And [BRT] = " & passedBRT
' Now set the order
selectString = selectString & " Order By [BRT] "
Dim rsTax As ADODB.Recordset
Set rsTax = New ADODB.Recordset
rsTax.Open selectString, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If rsTax.EOF Then
The definition of CYesNum is 'Public Const cYesNum As Long = -1'
The field '[Active_YN]' is type Yes/No in the original Access backend MDB but is type 'bit' in the SQL table
Executing that code with the AccessMDB as the backend selects the over 48,000 active records in the table ' tblTaxHeader'. The statement 'If rsTax.EOF Then' evaluates to false and processing continues.
Executing the code with the SQL Server backend linked to the MDB and the 'If rsTax.EOF Then' evalutes to True and no processing takes place.
I verified that the SQL table 'tblTaxHeader' is linked and contains the same amount of active records (over 48,00) that the Access MDB table contains.
I have completely seperate fornt end MDB's and backend MDB for running these test.
Any ideas what the issue is?