field variables in sql string (2 Viewers)

[John Sh]

In the SQL string below, the "Main_Family" field needs to be controlled by the variable "dFamily".
I need to run the same basic SQL with the "Family" data going into different fields..

This occurs n the "INSERT INTO" and "WHERE" clauses.
I have tried fields(dFamily) to no avail AI has been no help.

Public Sub inToDisc(dFamily As String, sFamily As String, nAccno As Double, sStr As String)
    oDB.Execute "INSERT INTO Discrepancies( Accession, Main_Family, Genus, Infra, Raw_Box, Collection) " & _
                "SELECT B.Accession, B.Family, B.Genus, B.Infra, B.BoxNo, B.Collection " & _
                "FROM Boxes2 as B LEFT JOIN Discrepancies as A ON A.Accession = B.Accession " & _
                "WHERE A.Main_Family ='" & sFamily & "'" & _
                "AND B.Accession = " & nAccno & _
                "AND A.Raw_Box = B.boxno ;", dbFailOnError
End Sub

I would also like to insert sStr into a "Notes" field
These are the relevant fields of the two tables.

 

[cheekybuddha]

"WHERE A.Main_Family ='" & sFamily & "'" & _

What is your question?

Do you have an error?

It looks like you need an extra space at the end of the above:

"WHERE A.Main_Family ='" & sFamily & "' " & _
                                       ^
                                       |
                                  space here

Also here:

"AND B.Accession = " & nAccno & " " & _
                              ^^^^^
                              |||||

 

[arnelgp]

you can also Add records using Recordsets:

Public Sub inToDisc(dFamily As String, sFamily As String, nAccno As Double, sStr As String)
    Dim rstMain As DAO.Recordset
    Dim rstDisc As DAO.Recordset
    
    Set rstMain = oDB.OpenRecordset("Select * From Discrepancies Where (1=0);")
    
    Set rstDisc = oDB.OpenRecordset( _
                "SELECT B.Accession, B.Family, B.Genus, B.Infra, B.BoxNo, B.Collection" & _
                "FROM Boxes2 as B LEFT JOIN Discrepancies as A ON A.Accession = B.Accession " & _
                "WHERE A.Main_Family ='" & sFamily & "'" & _
                "AND B.Accession = " & nAccno & _
                "AND A.Raw_Box = " & B.boxno & ";")
                
    With rstDisc
        If Not (.BOF And .EOF) Then
            .MoveFirst
        End If
        Do Until .EOF
            rstMain.AddNew
            rstMain!Accession = !Acccession
            rstMain!Main_Family = !Family
            rstMain!Genus = !Genus
            rstMain!Infra = !Infra
            rstMain!Raw_Box = !boxno
            rstMain!Collection = !Collection
            rstMain!Notes = sStr
            rstMain.Update
            
            .MoveNext
        Loop
        .Close
    End With
    rstMain.Close
    Set rstMain = Nothing: Set rstDisc = Nothing
End Sub

 

[John Sh]

It looks like you need an extra space at the end of the above:

"WHERE A.Main_Family ='" & sFamily & "' " & _
                                       ^
                                       |
                                  space here

Also here:

"AND B.Accession = " & nAccno & " " & _
                              ^^^^^
                              |||||

Thank you. I should have realised those spaces were missing but the harder you look, the easier it is to not see.
The second part of my query was how to include the "Notes" field using the sStr variable.
E.g.

Public Sub inToDisc(dFamily As String, sFamily As String, nAccno As Double, sStr As String)
    oDB.Execute "INSERT INTO Discrepancies( Accession, Main_Family, Genus, Infra, Raw_Box, Collection, Notes) " & _
                "SELECT B.Accession, B.Family, B.Genus, B.Infra, B.BoxNo, B.Collection, sStr " & _

This code gives error 3061: too few parameters. Expected 1. Which I assume is referring to the placement of sStr

 

[John Sh]

you can also Add records using Recordsets:

To achieve the results I need, I have to do a series of findfirst operations and it simply takes too long.
Using SQL is many times faster.

 

[cheekybuddha]

If notes will be the same for every possible inserted record then you can try:

    oDB.Execute "INSERT INTO Discrepancies( Accession, Main_Family, Genus, Infra, Raw_Box, Collection, Notes) " & _
                "SELECT B.Accession, B.Family, B.Genus, B.Infra, B.BoxNo, B.Collection, '" & Replace(sStr, "'", "''") & "' " & _

 

[Josef P.]

A few tips for troubleshooting/prevention:

* Use an extra variable for the concatenated SQL text, then you can output the content more easily.

Public Sub inToDisc(dFamily As String, sFamily As String, nAccno As Double, sStr As String)
 
   dim InsertSql as String

   InsertSql = "INSERT INTO Discrepancies( Accession, Main_Family, Genus, Infra, Raw_Box, Collection, Notes) " & _
                "SELECT B.Accession, B.Family, B.Genus, B.Infra, B.BoxNo, B.Collection, sStr " & _

 
   debug.Print InsertSql
   stop ' check string in immediate window
 
   oDB.Execute InsertSql, dbfailonerror

* Avoid SQL injection: strings must remain strings.
... "WHERE A.Main_Family ='" & sFamily & "'" & ...

=> try with sFamily = "abc' or 1=1 or 'a' = '"
=> "WHERE A.Main_Family = " & SqlText("abc' or 1=1 or 'a' = '")

public function SqlText(byval Text2Convert as Variant) as string
     if isnull(Text2Convert) then
         SqlText = "Null"
     else
        SqlText = "'" & replace(Text2Convert, "'", "''") & "'"
     end if
end function

(Note: simple function .. only show the concept)

 

[arnelgp]

To achieve the results I need, I have to do a series of findfirst operations and it simply takes too long.
Using SQL is many times faster.

i don;'t have Findfirst on my code. It just add a "filtered" records.
anyway, your choice.

 

[RonPaii]

Using Parameters eliminates the need to cleanup the text and the possibility of injection.

Public Sub inToDisc(dFamily As String, sFamily As String, nAccno As Double, sStr As String)
    ' I don't know where oDB is, so I used CurrentDb
    ' See the new ,Nones in insert and [sStr] as NewNote
    With CurrentDb.CreateQueryDef(vbNullString, _
                    "PARAMETERS " & _
                    "dFamily Text (255), " & _
                    "sFamily Text (255), " & _
                    "nAccno IEEEDouble, " & _
                    "sStr Text (255); " & _
                "INSERT INTO Discrepancies( Accession, Main_Family, Genus, Infra, Raw_Box, Collection, Notes) " & _
                "SELECT B.Accession, B.Family, B.Genus, B.Infra, B.BoxNo, B.Collection, [sStr] as NewNote " & _
                "FROM Boxes2 as B LEFT JOIN Discrepancies as A ON A.Accession = B.Accession " & _
                "WHERE A.Main_Family =[sFamily] " & _
                "AND B.Accession = [nAccno] " & _
                "AND A.Raw_Box = B.boxno " & _
                "AND B.Main_Family = [dFamily];")
        ' I Add "AND B.Main_Family = [dFamily]" for
        ' "Main_Family" field needs to be controlled by the variable "dFamily"
        .Parameters("dFamily") = dFamily
        .Parameters("sFamily") = sFamily
        .Parameters("nAccno") = nAccno
        .Parameters("sStr") = sStr
        .Execute dbFailOnError
    End With
End Sub