alter query (saved) criteria and update terms then run as passthrough (querydefs)

[gangel]

Hi everyone,


SO i have a Mysql db and i have a number of queries that i am currently updating the database with using docmd.execute strsql, failonerror

however... these go via normal db queries (as i understand) and this is slow over odbc.

I am trying to optimise my vba for faster writing.

My understanding is i should use currentdb.querydefs("MY SAVED QUERY").execute.

Now this My saved queryu has to be an acutal saved query.

What i havent learnt yet (self taught amateur) is how to change the criteria and "update to" parts of a saved query and how to insert my variables into it if i form it in VBA.


So what i BELIEVE i have to do it:

1. have an sql string in VBA with SET [field] = " & variable & " etc etc
(Some of these variables are from DAO recordsets)
2. Take this sql string and stick it as the sql for the saved variable..
Query(SavedQuery).sql = strSQL
(i know thats the wrong code, but thats what i need to do)

Then RUN that with Querydefs.

Am i on the right track!

Any adbvice much appreciated :")

Im all for reading and learning as i have this past year but i dont know the right questions to ask to get the right answers.. perhaps my terminology is a bit off!

[davegri]

Below function will create a named query from an SQL string.

'---------------------------------------------------------------------------------------
' Procedure : fcnCustomizeSQL
' DateTime  : 9/26/2006 20:57
' Author    : Davegri
' Purpose   : Attach new SQL property to an existing querydef. If the Query doesn't exist,
'           : create it with the passed SQL. Created query name passed in string qName.
'---------------------------------------------------------------------------------------
'
Function fcnCustomizeSQL(qName As String, strPassedSQL As String) 
    Dim qthisQuery As DAO.QueryDef
    On Error GoTo fcnCustomizeSQL_Error
    'if the query has been deleted, create it
    If DCount("Name", "MSysObjects", "[Name] = " & Chr$(39) & qName & Chr$(39)) = 0 Then
        Set qthisQuery = CurrentDb.CreateQueryDef(qName, strPassedSQL)
        Set qthisQuery = Nothing
        Exit Function
    End If
    'else modify it
    Set qthisQuery = CurrentDb.QueryDefs(qName)
    qthisQuery.SQL = strPassedSQL
fcnCustomizeSQL_EXIT:
    Set qthisQuery = Nothing
    Exit Function
fcnCustomizeSQL_Error:
    MsgBox Err.Number & ", " & Err.Description & ", fcnCustomizeSQL"
    Resume fcnCustomizeSQL_EXIT
End Function

[gangel]

wow thanks ill look at it and have a crack