Opret forespørgsel i VBA

Example from Access

CreateQueryDef Method Example

This example uses the CreateQueryDef method to create and execute both a temporary and a permanent QueryDef. The GetrstTemp function is required for this procedure to run.

Sub CreateQueryDefX()

    Dim dbsNorthwind As Database
    Dim qdfTemp As QueryDef
    Dim qdfNew As QueryDef

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")

    With dbsNorthwind
        ' Create temporary QueryDef.
        Set qdfTemp = .CreateQueryDef("", _
            "SELECT * FROM Employees")
        ' Open Recordset and print report.
        GetrstTemp qdfTemp
        ' Create permanent QueryDef.
        Set qdfNew = .CreateQueryDef("NewQueryDef", _
            "SELECT * FROM Categories")
        ' Open Recordset and print report.
        GetrstTemp qdfNew
        ' Delete new QueryDef because this is a demonstration.
        .QueryDefs.Delete qdfNew.Name
        .Close
    End With

End Sub

Function GetrstTemp(qdfTemp As QueryDef)

    Dim rstTemp As Recordset

    With qdfTemp
        Debug.Print .Name
        Debug.Print "  " & .SQL
        ' Open Recordset from QueryDef.
        Set rstTemp = .OpenRecordset(dbOpenSnapshot)

        With rstTemp
            ' Populate Recordset and print number of records.
            .MoveLast
            Debug.Print "  Number of records = " & _
                .RecordCount
            Debug.Print
            .Close
        End With

    End With

End Function

simple example

currentdb.QueryDefs("NewQuery").SQL = "SELECT ......"

>>Terry
Jeg fandt denne her og den virker men giver fejl når forespørgelsen eksisterer i forvejen, kan det løses?:

Sub NewQuery()
    Dim dbs As Database, qdf As QueryDef, strSQL As String

    ' Return reference to current database.
    Set dbs = CurrentDb
    strSQL = "SELECT tbl_Med_Akk_Link.MedarbejderIdRef, tbl_Med_Akk_Link.AkkordnrRef, tbl_Med_Akk_Link.Dato, tbl_Med_Akk_Link.Timer  FROM tbl_Med_Akk_Link WHERE (((tbl_Med_Akk_Link.Dato) Between #3/21/2004# And #4/3/2004#));"

    ' Create new query.
    Set qdf = dbs.CreateQueryDef("qry_Periode_til_Krydstab", strSQL)
    DoCmd.OpenQuery qdf.Name
    Set dbs = Nothing
End Sub

You need to delete it first then!

Ja det var også min tanke, jeg kan bare ikke lige få det implementeret i koden, har du et forslag?

On error Resume Next

currentdb.QueryDefs("qry_Periode_til_Krydstab").delete

Den giver fejl:
"Method or data member not found"
og så er .Delete markeret?

CurrentDb.QueryDefs.Delete "qry_Periode_til_Krydstab"

Så var den der :-)
Tak for hjælpen.

selv tak