Jet/Ace and float numbers

[JANR]

This is not a problem but an observation, sometimes Access throws you an unexpected curveball. This time the problem circles around Jet/Ace and float numbers.

Dim s As String
Dim x as Double
x = 125.25
s = " Insert into MyTable (MyNumber) Values ( " & x & ")"
Currentdb.Execute s, dbFailOnError

This should be strait forward for most, BUT here is the kicker. If your Regionalsettings uses comma and not period to seperate the integerpart and decimalpart of the number an error occures.
To test that it is not related to VBA I tested it in a simple append query by just putting 125,25 in the fieldrow and run the query and it works, but when you switch to SQL-view you get

INSERT INTO MyTable ([MyNumber])
SELECT 125.25 As Expr1;

So Jet/Ace do a conversion just like it does with dates. When you want to use VBA to work with Jet/Ace you have to do this conversion yourself, there are more ways to do this.

s = "Insert Into MyTable (MyNumber) Values ( " & Format(x, "0\.") & Right(x, 2) & ")"
or
s = "Insert Into MyTable (MyNumber) Values ( " & Replace(x, ",", ".") & ")"

Replace is best since it does not care how many decimals are precent.
It could be interesting to know how Jet/Ace do it's conversion, anybody knows?

Hope this can help someone else.

JR