Convert SQL Server procedure to Access VBA

[maxlang]

Office 2003/Win7
I would like to convert some SQL Server procedures to Access VBA.

The procedures are used to calculate chess player ratings.
Basically player's ratings are updated monthly based on results against
rated opponents based on the work of stats Prof. Mark Glickman.
I have done some of the conversion and it runs OK on test tables and queries but am stuck on the first UPDATE procedure.

These procedures convert fine:
TRUNCATE TABLE #tmpRatedResults

DECLARE @GameMonth int
SELECT @GameMonth = 1
DECLARE @CValue decimal (12,2)
SELECT @CValue = 63.2

WHILE @GameMonth <=60 BEGIN
INSERT #tmpRatings (RatingMonthID, PID, Rating, RD, MonthEnding)
SELECT @GameMonth as RatingMonthID, PID, Rating, RD, MonthEnding
FROM #tmpGlickoRatings

TRUNCATE TABLE #tmpCurrentActive
INSERT #tmpCurrentActive (PID)
SELECT PID
FROM training_union
WHERE MonthID = @GameMonth
GROUP BY PID

However when I get to the next procedure I am unsure as to how to code this:

UPDATE CA
SET CA.RD = CASE
WHEN SQRT(GR.RD*GR.RD + @CValue*(@GameMonth - MonthEnding)) <350 THEN SQRT(GR.RD*GR.RD + @CValue*(@GameMonth - MonthEnding)) ELSE 350 END,
CA.r = GR.Rating
FROM #tmpCurrentActive CA
JOIN #tmpGlickoRatings GR ON CA.PID = GR.PID

Any suggestions or advice much appreciated!

 

[xenou]

Possibly using IIF here (?):

SET CA.RD = 
IIF(
    SQRT(GR.RD*GR.RD + @CValue*(@GameMonth - MonthEnding)) <350, 
    SQRT(GR.RD*GR.RD + @CValue*(@GameMonth - MonthEnding)), 
    350
    ),
CA.r = GR.Rating

IIF is a VBA Function.
Actually, I'm not sure that Access has a SQRT function - I hope it does (not on Windows at the moment).

Post back if any more problems - replies from other members welcome as I'm out for the day ...

ξ

P.S.
1. e4

 

[maxlang]

Thanks for prompt reply xenou!
I will check out your suggestion after a bit of sleep as it's gone 1:00am where I am, yes up late wracking my last couple of neurons on this code.
PS: 1. e4 c5

 

[maxlang]

Hi xenou, others,
I have checked and yes Access does not have the SQRT function that SQL Server uses instead Access uses the exponentiation "^" so have used ^0.5 instead of SQRT in code.

So far I tried:

strSql = "UPDATE CA "
strSql = strSql & " SET CA.RD = "
strSql = strSql & " IIF("
strSql = strSql & "(GR.RD*GR.RD + CValue*(GameMonth - MonthEnding))^0.5 < 350,"
strSql = strSql & "(GR.RD*GR.RD + CValue*(GameMonth - MonthEnding))^0.5,350) "
strSql = strSql & " , CA.r = GR.Rating "
strSql = strSql & " FROM tmpCurrentActive CA "
strSql = strSql & " JOIN tmpGlickoRatings GR ON CA.PID = GR.PID;"

DBEngine(0)(0).Execute strSql

However this throws the following:

Run-time error '3075'
Syntax error (missing operator) in query expression 'GR.Rating FROM tmpCurrentActive CA JOIN tmpGlickoRatings GR ON CA.PID = GR.PID'.

I cannot see what the missing operator could be.
Any clues?
Thank you for your generous assistance to date.

 

[xenou]

Dunno.
Try INNER JOIN instead of JOIN ...

(?)
That might be needed in Access

 

[xenou]

Another shot (that is, if the above doesn't work) is to try old ansi 89 syntax:

FROM FROM tmpCurrentActive, tmpGlickoRatings
WHERE tmpCurrentActive.ID = tmpGlickoRatings.PID;"

Sometimes Access likes that better (especially, in UPDATE queries).

Another try is to put lots of parenthesis in your query expression. Access just isn't as robust as SQL Server when it comes to parsing query expressions - not a deal-breaker, but something you get used to when you work in Access a lot.

 

[maxlang]

Hi xenou,
Thanks for your assistance!
I tried the suggestions of using INNER JOIN, ansi 89 format and
use of liberal parentheses but no improvement. Searched other
web sites and similar problems seem to pop up repeatedly with
Access.
I tested an elementary UPDATE and it runs OK but as soon as
an update using joins is tried the code falls over.
I have another box with Linux and MySQL installed so
maybe the message is to forget trying to use MS Access for
work like this.

 

[james_lankford]

strSql = ""
strSql = strSql & "UPDATE tmpCurrentActive CA "
strSql = strSql & " INNER JOIN "
strSql = strSql & " tmpGlickoRatings GR ON CA.PID = GR.PID "
strSql = strSql & " SET "
strSql = strSql & "     CA.RD = "
strSql = strSql & "           IIF("
strSql = strSql & "                (GR.RD*GR.RD + CValue*(GameMonth - MonthEnding))^0.5 < 350,"
strSql = strSql & "                (GR.RD*GR.RD + CValue*(GameMonth - MonthEnding))^0.5, 
strSql = strSql & "                350
strSql = strSql & "              ) "
strSql = strSql & "     , "
strSql = strSql & "     CA.r = GR.Rating "

 

[xenou]

^^ hopefully will help. I believe Access should work here - hate to see it failing as I've had great success with Access for many projects. Usually a "missing query expression" error is really just as simple as something like a missing parenthesis). That said, MySQL isn't a bad choice of course. Possibly providing some sample data we could test the query on this end.

 

[maxlang]

Thanks James!
Case of cyber beer on its way!
Your code was v. helpful and got my project moving again. This procedure is now running smoothly and I have moved forward in converting the rest of the SQL Server code to Access vba.
Your help and also that of xenou is greatly appreciated.

As I mentioned in my original post I am attempting to convert some Access SQL Server procedures to Access VBA. I am using some small tables of chess player performances just to get the method up and running before tackling a large db.

Another procedure has me at standstill namely the use of a sub query in the Server procedure:

UPDATE CA
SET CA.d2 = SubQ.d2
FROM (SELECT PID, 1.00/(SUM(q*q) as d2 FROM #tmpCR3 GROUP BY PID) SubQ
JOIN #tmpCurrentActive CA ON CA.PID = SubQ.PID

I have a workaround in creating a temp table in place of the sub query and then running an Update on this then deleting the temp table but I would prefer to use the sub query method directly.

If I type the above query into SQL View (omitting the #'s, ending with I get error warnings about a missing operator which mystifies me.
Any further assistance with this would be appreciated.