Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2008
    Posts
    2

    Unanswered: Modify 'IN' pathlink of a SQL query

    Hi,

    with VBA I would like to change the 'IN' function within a SQL query:

    SELECT Prm_Importation.Path, Prm_Importation.[Critere importation], Prm_Importation.[Table Destination], Prm_Importation.Confirmation
    FROM Prm_Importation IN 'G:\Documents\BRP - Professionnel\Consolidation_Master_2009.mdb';

    this represents the pathlink stated in the <source database> of the properties of my query.

    I built a form where the user is supposed to enter the path and the filename of the DB where this information is stated as the DB can be saved on different places on the server.

    then, I retrieved this information via this VBA code:

    Public strPathDB as string 'must this data be other than <string>?
    Public strFileDB as string
    Public strsSql as string

    Sub GeneralImportGlobal() 'Information provided by the user on form_FrmImportGlobal
    strPathDB = Rs![Path] 'Path of external DB
    strFileDB = Rs![File] 'Name of external DB
    strsSql = strPathDB & strFileDB 'string SQL to replace 'IN' in my SQL query

    End Sub

    Sub ModifysSql()
    Docmd. ????
    End Sub

    here I am blocked and unable to launch the code to modify the 'IN' function within my query

    Could someone help me?

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,420
    Provided Answers: 7
    Hes this is what i do


    SQL = ""
    SQL = "SELECT Prm_Importation.Path, Prm_Importation.[Critere importation], Prm_Importation.[Table Destination], Prm_Importation.Confirmation
    FROM Prm_Importation IN '[PathFileName]';"
    'its easyer to read

    then
    SQL = replace(SQL,"[PathFileName]",strFileDB)

    DoCmd.SetWarnings False 'turn off messages
    DoCmd.RunSQL SQL 'EXE the SQL
    DoCmd.SetWarnings True 'turn on message
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Nov 2008
    Posts
    2
    thank you for your code.

    I had a chat on a different forum and we provided me the following VBA code.
    I tried it and it works.

    Code:
    Function ModifySQL(NomRequete As String, NewIn As String) As String
    'Objet : Fonction remplaçant le IN 'db' d'une requête par un nouveau
    'Arguments : NomRequete => nom de la requête dont il faut modifier le SQL
    '            NewIn => Texte de remplacement pour la base cible
     
        Dim sTempSQL    As String
        Dim lStart      As Long
        Dim lEnd        As Long
        Dim qRequete    As QueryDef
    
        'Création de l'objet requête
        Set qRequete = CurrentDb.QueryDefs(NomRequete)
    
        'Récupération du SQL de la requête    
        sTempSQL = qRequete.SQL
    
        'Récupération des positions dans la chaîne
        lStart = InStr(1, sTempSQL, "IN '", vbTextCompare) + 3
        lEnd = InStr(lStart + 1, sTempSQL, "'", vbTextCompare)
    
        'Remplacement de la chaine SQL s'il y a un lStart   
        If lStart <> 0 Then
            sTempSQL = Left(sTempSQL, lStart) & NewIn & Mid(sTempSQL, lEnd)
            qRequete.SQL = sTempSQL
        End If
    
        'Renvoyer le nouveau SQL pour contrôle
        ModifySQL = sTempSQL
    End Function
    I have multiple queries to modify and then, I can easy do a circle.
    anyway, thanks for your helpl.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •