Apply parameter to queryDef (1 Viewer)

[kirkm]

Struck a snag with this

   Set r = CurrentDb.OpenRecordset(sq2$)
        cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & "\\JOHN\Desktop\Build11.mdb"
        cnn.Open
            r.MoveFirst
            Do
                sql$ = "SELECT AComment, Title FROM tblMain4"
                sql$ = sql$ & " WHERE Title = p0;"
                 ' sql$ = sql$ & Chr$(34) & r!TTitle & Chr$(34)

                With CurrentDb.CreateQueryDef("", sql)
                    .Parameters("p0") = r!TTitle
                End With
              
                With rx
                    .Open sql$, cnn, adOpenStatic, adLockReadOnly
                   ' Do Stuff
                    .Close
                End With
                r.MoveNext
            Loop Until r.EOF
        cnn.Close
    r.Close
    Set r = Nothing

I'm being told tblMain4 can't be found and it's because (I think)

With CurrentDb.CreateQueryDef("", sql)
is wrong. It isn't the currentDB but the one I've opened the connection to. How would I change that line ?

 

[June7]

You want to modify a query object in Build11.mdb? Try:

cnn.CreateQueryDef

 

[kirkm]

Doesn't seem to work June, although it capitalises there's no intellisense (drop down thingy) and run time error 3001.

 

[June7]

Please explain exactly what you are trying to accomplish.

Do you want to just modify a parameter for an existing query object? Or do you want to create a new query object? What is parameter "p0"?

So far I have been able to create a QueryDef by following code in https://docs.microsoft.com/en-us/of...-reference/database-createquerydef-method-dao

Use full file path in place of "Northwind.mdb"

 

[kirkm]

Hi June, p0 is just the name of the parameter. My goal was to get data from an .mdb file on another computer (via LAN) and add it into a database on this computer. And using Parameters which I believe is a more robust way to send text, as dates and formatting are handled automatically. Although Googling for info on Access Parameters brings up stuff about typing inside square brackets, so that and DAO Vs ADODB took a bit of nutting out. Or fluking out would be more accurate. Anyone I succeeded, so here is the code fyi, which works here. Thanks for your help and interest

Private Sub btnSheetMusic_Click()

    Dim sql As String, sq2 As String
    Dim r As DAO.Recordset, r2 As DAO.Recordset
    sq2$ = "SELECT CDTRacks.TTrack, CDTracks.TInfo, CDTracks.TTitle"
    sq2$ = sq2$ & " FROM CDTRacks"
    sq2$ = sq2$ & " WHERE (((CDTRacks.TCat)=" & ThisDisk()
    sq2$ = sq2$ & ")) ORDER BY CDTRacks.TTrack;"
    
    Set r = CurrentDb.OpenRecordset(sq2$)
        r.MoveFirst
        Do
            sql$ = "SELECT Prefix, Title FROM SM WHERE Title = p0;"

            With ConnDB("\\JOHN\Desktop\Build11.mdb").CreateQueryDef("", sql)
                .Parameters("p0") = r!TTitle
                Set r2 = .OpenRecordset
            End With

            With r2
                 If .RecordCount <> 0 Then
                    'do stuff with r2 (the connected-to db). If valid add to and Update fields in r (this db)
                End If
                .Close
            End With
            r.MoveNext
        Loop Until r.EOF

    r.Close
    Set r = Nothing
    Set r2 = Nothing

End Sub

Function ConnDB(dbName) As DAO.Database
    Set ConnDB = DAO.OpenDatabase(dbName)
End Function