converting sql to vba invalid character error (1 Viewer)

MilaK · Dec 5, 2016

Hello,

Please suggest how to convert the following SQL to VBA. I get an invalid character error at "_". Thanks for your help.

Code:

Dim SQL As String

SQL = "INSERT INTO tbl_Variants ( sample_id, flag, gene, exon, cDNA, aa_change, AF, FR, RR, FA, RA, cosmic_id, transcript, conseq, zyg, amplicon, Chr, [position], dbSNP, 1000G_freq, espSixtyFiveHundred, ExAc )"
SQL = SQL & "SELECT [tbl_Variants_temp]![run_name] & "_" & [tbl_Variants_temp]![sample_name] AS sample_id, tbl_Variants_temp.flag, tbl_Variants_temp.gene, tbl_Variants_temp.exon, tbl_Variants_temp.cDNA, tbl_Variants_temp.aa_change, tbl_Variants_temp.AF, tbl_Variants_temp.FR, tbl_Variants_temp.RR, tbl_Variants_temp.FA, tbl_Variants_temp.RA, tbl_Variants_temp.cosmic_id, tbl_Variants_temp.transcript, tbl_Variants_temp.conseq, tbl_Variants_temp.zyg, tbl_Variants_temp.amplicon, tbl_Variants_temp.Chr, tbl_Variants_temp.position, tbl_Variants_temp.dbSNP, tbl_Variants_temp.[1000G_freq], tbl_Variants_temp.espSixtyFiveHundred, tbl_Variants_temp.ExAc"
SQL = SQL & "FROM tbl_Variants_temp"
SQL = SQL & "WHERE [tbl_Variants_temp]![sample_name]) Not Like " * HD753 * ""

pbaldy · Dec 5, 2016

You need to use single quotes inside the string (around the underscore for example). This is a common method used to debug SQL in VBA and should help:

http://www.baldyweb.com/ImmediateWindow.htm

pbaldy · Dec 5, 2016

I should add you're going to have a problem with spaces between lines, which should jump out when you use the above technique.

MilaK · Dec 5, 2016

Now I get a type mismatch error at

Code:

SQL & "WHERE [tbl_Variants_temp]![sample_name]) Not Like " * str_HD * ""

Please advise...

Code:

Sub excsql()

Dim SQL As String
Dim str_HD As String
str_HD = "HD753"

SQL = "INSERT INTO tbl_Variants ( sample_id, flag, gene, exon, cDNA, aa_change, AF, FR, RR, FA, RA, cosmic_id, transcript, conseq, zyg, amplicon, Chr, [position], dbSNP, 1000G_freq, espSixtyFiveHundred, ExAc )"
SQL = SQL & " SELECT [tbl_Variants_temp]![run_name] & '_' & [tbl_Variants_temp]![sample_name] AS sample_id, tbl_Variants_temp.flag, tbl_Variants_temp.gene, tbl_Variants_temp.exon, tbl_Variants_temp.cDNA, tbl_Variants_temp.aa_change, tbl_Variants_temp.AF, tbl_Variants_temp.FR, tbl_Variants_temp.RR, tbl_Variants_temp.FA, tbl_Variants_temp.RA, tbl_Variants_temp.cosmic_id, tbl_Variants_temp.transcript, tbl_Variants_temp.conseq, tbl_Variants_temp.zyg, tbl_Variants_temp.amplicon, tbl_Variants_temp.Chr, tbl_Variants_temp.position, tbl_Variants_temp.dbSNP, tbl_Variants_temp.[1000G_freq], tbl_Variants_temp.espSixtyFiveHundred, tbl_Variants_temp.ExAc"
SQL = SQL & " FROM tbl_Variants_temp "
[B]SQL = SQL & "WHERE [tbl_Variants_temp]![sample_name]) Not Like " * str_HD * ""[/B]

Debug.Print SQL
End Sub

Thanks

pbaldy · Dec 5, 2016

Similar issue. Try

SQL = SQL & "WHERE [tbl_Variants_temp]![sample_name]) Not Like '*" & str_HD & "*'"

MilaK · Dec 5, 2016

Thank you, it worked!

One more follow up question if you don't mind?

What is better

Code:

db.Execute strSQL

or Set rs = db.OpenRecordset(SQL, dbOpenSnapshot)
to execute a query statement?

I'm writing a function that will execute one of the queries specified in the select case statement. I'm using Access 2016.

Code:

Private Function update_records()

Dim SQL As String
Dim str_HD As String
Dim db As Database

str_HD = "HD753"

Set dbs = CurrentDb

On Error GoTo Proc_Err

Select Case queryname
Case Is = "append_variants"

SQL = "INSERT INTO tbl_Variants ( sample_id, flag, gene, exon, cDNA, aa_change, AF, FR, RR, FA, RA, cosmic_id, transcript, conseq, zyg, amplicon, Chr, [position], dbSNP, 1000G_freq, espSixtyFiveHundred, ExAc )"
SQL = SQL & " SELECT [tbl_Variants_temp]![run_name] & '_' & [tbl_Variants_temp]![sample_name] AS sample_id, tbl_Variants_temp.flag, tbl_Variants_temp.gene, tbl_Variants_temp.exon, tbl_Variants_temp.cDNA, tbl_Variants_temp.aa_change, tbl_Variants_temp.AF, tbl_Variants_temp.FR, tbl_Variants_temp.RR, tbl_Variants_temp.FA, tbl_Variants_temp.RA, tbl_Variants_temp.cosmic_id, tbl_Variants_temp.transcript, tbl_Variants_temp.conseq, tbl_Variants_temp.zyg, tbl_Variants_temp.amplicon, tbl_Variants_temp.Chr, tbl_Variants_temp.position, tbl_Variants_temp.dbSNP, tbl_Variants_temp.[1000G_freq], tbl_Variants_temp.espSixtyFiveHundred, tbl_Variants_temp.ExAc"
SQL = SQL & " FROM tbl_Variants_temp "
SQL = SQL & "WHERE [tbl_Variants_temp]![sample_name]) Not Like '*" & str_HD & "*'"
Debug.Print SQL
db.Execute strSQL

Case Is = "append_qc_variants"

SQL = "INSERT INTO tbl_Variants_QC ( sample_id, flag, gene, exon, cDNA, aa_change, AF, FR, RR, FA, RA, chr )"
SQL = SQL & " SELECT [tbl_Variants_temp]![run_name] & '_' & [tbl_Variants_temp]![sample_name] AS sample_id, tbl_Variants_temp.flag, tbl_Variants_temp.gene, tbl_Variants_temp.exon, tbl_Variants_temp.cDNA, tbl_Variants_temp.aa_change, tbl_Variants_temp.AF, tbl_Variants_temp.FR, tbl_Variants_temp.RR, tbl_Variants_temp.FA, tbl_Variants_temp.RA, tbl_Variants_temp.chr"
SQL = SQL & " FROM tbl_Variants_temp"
SQL = SQL & " WHERE [tbl_Variants_temp]![sample_name]) Like '*" & str_HD & "*'"
Debug.Print SQL
db.Execute strSQL

Case Else

End Select

Proc_Exit:
  
  Set db = Nothing
  Exit Function

Proc_Err:
  
  MsgBox "Error updating: " & Err.Description
  Resume Proc_Exit
End Function

pbaldy · Dec 5, 2016

I would use the Execute method to run an action query. I use recordsets on select queries, where I need to do something with the results that are returned. Not sure if you're aware that what you have will fail silently, which sometimes you want. To have it throw an error when there is one:

db.Execute strSQL, dbFailOnError

MilaK · Dec 5, 2016

thank you!!!!

pbaldy · Dec 5, 2016

Happy to help!

converting sql to vba invalid character error (1 Viewer)

MilaK

Registered User.

pbaldy

Wino Moderator

pbaldy

Wino Moderator

MilaK

Registered User.

pbaldy

Wino Moderator

MilaK

Registered User.

pbaldy

Wino Moderator

MilaK

Registered User.

pbaldy

Wino Moderator

Users who are viewing this thread