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:

'
' 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

Open in new window


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?
LVL 1
mlcktmguyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
instead of -1 use true or 1
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>The field '[Active_YN]' is type Yes/No in the original Access backend MDB but is type 'bit' in the SQL table
Correct.  Also, SQL Server bit column values  are 1 (true) and 0 (false), compared to Access -1 and 0.

>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.
Will need to verify that your VBA is correctly connecting to the SQL table, so copy-paste it into this question.

For other Access SQL to SQL Server questions check out my article Migrating your Access Queries to SQL Server Transact-SQL.
0
mlcktmguyAuthor Commented:
True worked but lots of great information in Jim Horn's link

Jim, the tables are linked using the linked table manager vs a connection string built in the VB code.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.