1

I'm trying to upload a value directly from VBA to SQL via ADO - I collect values into a Dim srtSql As String and then insert them into the SQL table. The issue relates to a "Currency" value with a comma delimiter. I have a comma decimal separator and the value of my variable looks like (94,5), but a dot (94.5) is needed. The code below doesn't solves the problem.

With Application.
    .DecimalSeparator = "."
    .UseSystemSeparators = False
End With

While searching for a solution I've got a code below from the case How to ensure dot as decimal separator when exporting excel to csv? to check the separator.

Dim myShell, regDecSep    
Set myShell = CreateObject("WScript.Shell")
regDecSep = myShell.RegRead("HKCU\Control Panel\International\sDecimal")

Debug.Print "System decimal separator: " & regDecSep

I'm not familiar with Registry. My question - is it any way to set a system decimal separator via RegWrite or any other method? P.S. I would like to avoid a vba Replace() solution.

Mik
  • 2,099
  • 1
  • 5
  • 19
  • Your code has a trailing dot. That won't work. Did you run the full code from the answer linked? – Gustav Jul 21 '22 at 09:10
  • The link points to a question that exports data into CSV, but you are talking about SQL. Do you export the data into CSV and read that file from your database or do you write the data directly from VBA? – FunThomas Jul 21 '22 at 09:16
  • @FunThomas directly from vba to SQL – Mik Jul 21 '22 at 09:18
  • @Gustav thank you for your comment, What do you mean by a " trailing dot"? – Mik Jul 21 '22 at 09:19
  • @FunThomas I collect values into a `Dim srtSql As String` and then insert them via ADO into the SQL table. I have a "Currency" value with a comma delimiter. – Mik Jul 21 '22 at 09:27
  • I know you already accepted the answer and it is working for you. However, I encourage everybody to learn and use ADODB.Parameters. It's more code, but you get much more robust solutions because you don't need to take care about number or date formats. – FunThomas Jul 21 '22 at 12:03
  • @FunThomas thanks a lot for the idea. I'd like to learn more about. Can you share with a practice of using parameters or some samples? – Mik Jul 21 '22 at 12:19

1 Answers1

1

Your code is about Excel, not SQL. So, convert decimal values to string expressions for these having a dot as the decimal separator. Str will do that:

strSql = "Insert Into Table1 (SomeTextField, Amount, AnotherTextField) " & _
    "Values ('" & Text1 & "', Str(" & YourAmount & "), '" Text2 & "')"

Also, see my function CSql for concatenating SQL expressions.

Gustav
  • 53,498
  • 7
  • 29
  • 55
  • 1
    Thank you so much !!! For the answer and for the function. It's so helpfull. I already copied it into MyLib ). Million Thanks! – Mik Jul 21 '22 at 11:40