SQL with variables

[kevin.dorrian]

Hi Everyone,

Hours of frustration have led me to this point...

I have various functions in VB which will parse strings into start middle and ends dependent on the string format. So 3 functions will return a_, b and _c if fed a_b_c. There is more than one string format so there are 3 more functions for 0b0 etc.
These functions can obviously be grouped depending on the string format so I keep each function name and its parameters as a string in a field in a table. A user can select a record from a form and each of the 3 functions will be displayed through 3 fields in this form.

I want to loop through a list of strings and perform an INSERT INTO a sql table using variables. The variables are the fields in the form and therefore names of functions and their parameters.

I can perform the SQL INSERT easily enough but I can figure out how to replace the variable and then resolve the function before executing the SQL statement.

Here is the code, this will INSERT ok but it will insert the string from the table instead of resolving the function first.

    Dim strTemp As String
    Dim colFolders As New Collection
    Dim vFolderName As Variant
    Dim strSQL As String
    Dim strEqu1 As String
    Dim strEqu2 As String
    Dim strEqu3 As String
    
    'Add the files to the folder.
    'Strips extension
    strFolder = TrailingSlash(strFolder)
    strTemp = Dir(strFolder & strFileSpec)

    
    Do While strTemp <> vbNullString
    
    strEqu1 = [Forms]![frmCode]![FileName]
    strEqu2 = [Forms]![frmCode]![BeforeFilename]
    strEqu3 = [Forms]![frmCode]![AfterFilename]

        gCount = gCount + 1
        SysCmd acSysCmdSetStatus, gCount
        
    strSQL = "INSERT INTO tblexample " _
    & "(fileName, filePath, fileExt, beforeFileName, afterFileName, dateImported) " _
    & "SELECT """ & strEqu1 & """ " _
    & ", """ & strFolder & """ " _
    & ", """ & FileExtFromFile(strTemp) & """ " _
    & ", """ & strEqu2 & """ " _
    & ", """ & strEqu3 & """ " _
    & ", """ & Date & """"
    

MsgBox strSQL, vbOKOnly, "debug"
          
         CurrentDb.Execute strSQL
        colDirList.Add strFolder & strTemp
        strTemp = Dir
    Loop

Please can someone help. Im sorry if I havnt included enough info/included too much, im new to bug reporting.

Thanks,

Kev

 

[OlcayM]

Hi,

I might understood your explanation wron but, seems you are willing to go on each record on a form and apply your sql... If this is right then
- take the
"strEqu1 = [Forms]![frmCode]![FileName]
strEqu2 = [Forms]![frmCode]![
strEqu3 = [Forms]![frmCode]![AfterFilename]"
lines, out of your (Do While strTemp <> vbNullString) loop.
- Add another loop over your loop, which visits each record on your form

HTH
OcayM

 

[kevin.dorrian]

Hi,

I might understood your explanation wron but, seems you are willing to go on each record on a form and apply your sql... If this is right then
- take the
"strEqu1 = [Forms]![frmCode]![FileName]
strEqu2 = [Forms]![frmCode]![
strEqu3 = [Forms]![frmCode]![AfterFilename]"
lines, out of your (Do While strTemp <> vbNullString) loop.
- Add another loop over your loop, which visits each record on your form

HTH
OcayM

Thanks for your suggestion OlcayM,

I don't think I explained the problem properly. What I am looking for is the correct syntax for the string SQL that allows the variable(which is a function name & arguments string) to be resolved before the SQL is executed. Or any other suggestions for alternate ways to carry out the same idea.

Thanks,

Kevin

 

[Rabbie]

It would be very helpful if you could post both the strSQL that is displayed in the msgbox and the value for it that you think should be there.

 

[Dennisk]

I don't think you can do this in sql. in VBA there is an EVAL() function. Test it in sql.

 

[kevin.dorrian]

It would be very helpful if you could post both the strSQL that is displayed in the msgbox and the value for it that you think should be there.

The Debug message box shows:

INSERT INTO tblexample (fileName, filePath, fileExt, beforeFileName, afterFileName, dateImported) SELECT "FileName(strTemp)" , "\\shared\examplefolder\" , ".pdf" , "FileFirstUnderScore(strTemp)" , "FileSecondUnderScore(strTemp)" , "10/11/2007"

where FileName(strTemp) etc are function names,

and I really want it to show this:

INSERT INTO tblexample (fileName, filePath, fileExt, beforeFileName, afterFileName, dateImported) SELECT "actualFileName" , "\\shared\examplefolder\" , ".pdf" , "actualFileNameBeforeUnderscore" , "actualFileNameAfterUnderscore" , "10/11/2007"

where actualFileName is the string that has been parsed by the function.

Also I don't think Eval() works as I pass an argument to my function and Eval() only allows you to pass an empty argument to the function.

Thanks again

Kev

 

[OlcayM]

Kevin,

Again I might misunderstood due to my poor English.

try this

strSQL = "INSERT INTO tblexample " _
& "(fileName, filePath, fileExt, beforeFileName, afterFileName, dateImported) " _
& "SELECT '" & strEqu1 & "'" _
& ", '" & strFolder & "'" _
& ", '" & FileExtFromFile(strTemp) & "'" _
& ", '" & strEqu2 & "'" _
& ", '" & strEqu3 & "'" _
& ", '" & Date & "'"

 

[kevin.dorrian]

Thanks for all your help guys, I managed to solve the problem by doing the following:

Application.Run(function)

This forces the resolution of the function which you can then plug in later on in the code.

Seems like a fairly obvious thing to do to be honest, can't see why i didn't spot it before.

Thanks again,

Kev