ADODB query with parameters (1 Viewer)

[kirkm]

Someone very kindly showed me this concept, and function

Function Matches(L As String, C As String) As DAO.Recordset
    Const sql As String = _
        "SELECT t.* " & _
        "FROM tblMain As t " & _
        "WHERE t.Lake = p0 " & _
        "AND t.Region = p1 "
 
    With CurrentDb.CreateQueryDef("", sql)
        .Parameters("p0") = L
        .Parameters("p1") = C
        Set Matches = .OpenRecordset
        .Close
    End With

End Function

Which works very nicely, but I'm now trying to use this with a Connction to another db and run into a few problems.

I'm opening the db with

Sub OpenBackend() 'Access
    Set cnn = New adodb.Connection
    cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & BackendFileSpec
    cnn.Open
End Sub

where BackendFileSpec is the path & name of the other db. Then I'm attempting to call the function with

OpenBackend
    Dim rx As adodb.Recordset
    Set rx = Matches(CStr(Lake), CStr(Region))

In the function I've changed 'As DAO.Recordset' to ' As ADODB.Recordset
' (although it did not capitalize) and 'Currentdb' to 'cnn'.

I'm getting error 'Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.' so suspect it's all wrong!
Any help appreciated.

 

[arnelgp]

No need to use Adodb.recordset.
Import the table from another db as linked table.
Use the function as is.

 

[kirkm]

Thanks, that's a good idea. But I'd still be interested to get it working the other way.

 

[arnelgp]

Here is the equivalent to ado.
Open sampleform in sample db.
Review the code on open event of the form as well as code in module1

 

[kirkm]

Thank you very much for that and using the same details as my example! It's exactly what I was after, and easy to follow.
I was a bit worried as I've Office 2003 mdb files but everything worked the same. It's also OK with a VB6 front end, which I didn't think to say before (and couldn't link tables to).
Lots of stuff I doubt I could have ever worked out... thanks again

 

[MarkK]

Hey, that looks like code I would write, but, to connect to a different file, you can just put an IN clause in the SQL, if you want...

Function Matches(L As String, C As String) As DAO.Recordset
    Const sql As String = _
        "SELECT t.* " & _
        "FROM tblMain As t [COLOR="Blue"]IN 'C:\Database\MyTestDb.accdb'[/COLOR] " & _
        "WHERE t.Lake = p0 " & _
        "AND t.Region = p1 "
 
    With CurrentDb.CreateQueryDef("", sql)
        .Parameters("p0") = L
        .Parameters("p1") = C
        Set Matches = .OpenRecordset
        .Close
    End With

End Function

...which is, I think, your simplest solution.
Mark

 

[kirkm]

Mark it may have been you who gave me the use parameters advice. I liked it so much I've been changing some older stuff which uses Access tables, but from Excel and VB6. Hence the ADODB connections. I have it working with arnelgp's code, but quite like the IN switch.
Except the "With Currentdb" isn't valid. Any suggestions there?

 

[kirkm]

I've struck a bit of a problem with fields returned in the Recordset. I think there's a 256 char limit (although I appear to getting 264), then it's padded with weird rubbish.

Is this an issue with ADODB. Is there any workaround?

 

[MarkK]

...from Excel and VB6. Hence the ADODB connections.

Keep in mind that in Excel and VB you can still set a reference to DAO, and use DAO instead of ADODB. Your choice.
And in code, you can expose your own 'CurrentDb', like...

Global Const [COLOR="DarkRed"]DATA_PATH[/COLOR] As String = "C:\Database\MyTestDb.accdb"

Function [COLOR="blue"]MyCurrentDb[/COLOR]() As DAO.Database
    Set MyCurrentDb = DAO.OpenDatabase([COLOR="darkred"]DATA_PATH[/COLOR])
End Function

Function Matches(L As String, C As String) As DAO.Recordset
    Const sql As String = _
        "SELECT t.* " & _
        "FROM tblMain As t " & _
        "WHERE t.Lake = p0 " & _
        "AND t.Region = p1 "
 
    With [COLOR="Blue"]MyCurrentDb[/COLOR].CreateQueryDef("", sql)
        .Parameters("p0") = L
        .Parameters("p1") = C
        Set Matches = .OpenRecordset
        .Close
    End With
End Function

So you can use DAO just as easily as ADO in Excel or VB.
hth
Mark

 

[kirkm]

Mark, I'm getting error "Unrecognized database format" on
With MyCurrentDB.CreateQueryDef("", sql)

Is this because it's an mdb file and not accdb ?

 

[MarkK]

That seems possible. To connect to an mdb file, make sure your DAO reference is set to an item called "Microsoft DAO 3.6 Object Library" in the list in References. Alternatively, I think you can create that object using...

set dbs = CreateObject("DAO.DBEngine.36")

hth
Mark

 

[kirkm]

Thanks Mark, I had 3.51 and changing that to 3.6 removed the error.
But I'm still getting fields truncated to 256ish chars. Is there any way around that, do you know? Without the whole field it defeats the purpose. This was (probably) a problem before with the non-parameter version but I hadn't realised it.

 

[MarkK]

But I'm still getting fields truncated to 256ish chars. Is there any way around that, do you know?

What does this mean? Is there code? SQL? VBA?
Mark

 

[arnelgp]

Have you tried enumerating all the fields in the select statement and putting the memo field first in the enumeration

 

[kirkm]

My apoogies I have sorted the problem and it's totally my fault!

In the Sql SELECT I had joined 2 fields "Field1 & Field2 As abc" and this somehow limited the length of what was returned. It took me a while to work that out. I can easily change that and join them later. It was just to made it suit some later code. arnelgp, I think that's what you mean by 'enumerate' and no, memo fields aren't first. Should they be? (Its is working ok now). Many thanks to you and Mark for all the help with this.