Skipping code after moving to sql server

[Geirr]

Hi all.

I've got into a problem I just can't figure out how to fix..
I know it sounds stupid, but here is the case:
I have just created a copy of my database backend to Sql Server Express. all tables and fields are created 'by the book' - i think.. and the front end linked to the sql database. So far - so good..

But then, I see my front end start to 'behave' stranges. And with some test, I've found that severeal codes - typical an If Then Else are skipped. Same for a Select Case - I changed on of the If Then.. to Select Case, and the same part of code where skipped.

This happens in a standard procedures, and I've found this several other places, but not found a 'common issue' which create this.
The first case mentoined above, where found in a std- procedure, and the skipped If Then../Case Select where at second level of nesting.

The frontend where working perfectly ok when using it on std. access backend, but this is not the case now. Even tested with a 2012 and 2016 version of SQL Express. Most of the code works fine with with sql as backend, but some of codes in forms procedures are now skipped...

Btw, I am using Access 2013.
Please excuse my lousy english writing...

Best regards,
Geirr, Norway.

 

[The_Doc_Man]

The usual method for verifying this "skipping" that you report is to debug the code in Single Step, and to examine (via hovering the cursor over a variable or via Debug.Print in the Immediate window) every variable involved in each decision.

Tedious? Yes. Will you need to do it often? Not if you are lucky.

Put a breakpoint at the top of the code and maybe a few breakpoints elsewhere in the code sequence. Then trigger whatever event contains this code. Then use the F8 key to single step. Remember you are stopping BEFORE you execute the line on which the highlight is visible. Examine the relevant variables. Take the code one line at a time and decide what you THINK it should do next. Then see whether it in fact DOES that.

There is no substitute for this kind of testing. It is good that once you find this kind of problem you don't have to deal with it again (you hope).

There is another way to do this but is clunkier and harder to set up. You can open a log file and write one line to the file just before any branch points in your code so that you can see what values are being used to control the branch decisions.

Between manual single stepping and creation of a log file, you can find the truth. Since you are using a non-Access BE database, there IS a chance that you are running into a timing problem where something isn't quite right instantly. So either the manual stepping tests or the logging tests will let you decide if things are as they should be just before your test and branch/select.

 

[JHB]

Could you show the procedure/Sub where code stop working, (mark the code line where the strange behave starts)?

 

[Geirr]

Hi guys.

Thansk for your replies!

When I start checking with the usage of breakepoints, I found that some of the String variables, where added several spaces/blanks. So, using the Trim(var) fixed this problem.

Seondly, when testing - addeing a record to sql backend table using my frontend app, I see that the values are filled up with additional spaces/blanks. E.g. a text field speicifed to max 5 character, and added the data "To" - I see that spaces/blanks ar added, so the field data is in fact "To " now.
Checking this again the 'identical' access backend data, this does not occur. The same added data shows the real data is "To".

So, what now - do I realy have to add Trim to all read/write code to my datafile in 'sql mode'? If so, I will do it (it will be a huge job - it's a big program...), but maybe I have done something that tell the sql server to add blanks automatically? Any suggestion on this?

Best regards,
Geirr Eggen

 

[Geirr]

Hi again.

I understand this forum are not ment for Sql, but I will just inform you, that the probles is beacause I have selected nchar instead of nvarchar in table spec. in sql expr. server.
Thanks to google, I've found that nvar are fixed length, and therefore the space/blanks added.

So I thinkt we can conclude that the initial problem are solved, and the reason why this is happening is found.

Best Regards,
Geirr Eggen.

 

[Lightwave]

Yep did the very same when I first moved to SQL server - always use nvarchar not nchar.

Took me quite a few hours to figure that one out.