mlcktmguy
asked on
Query Access With SQL Server
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:
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?
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
Else
selectString = selectString & " And [BRT] = " & passedBRT
End If
'
' 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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Jim, the tables are linked using the linked table manager vs a connection string built in the VB code.