CODEWITHSUNDEEP
ms-accessvbams-access-2010
user2362865
user2362865

Reputation: 1

Open recordset with OBDC tables, dbSeeChanges not working

I have been struggling all day with running a loop through a recordset in MS Access 2010. I've been able to do this before, but never when the recordset is referencing the primary key. I've resolved the dbSeeChanges error, but it's still not working. Thanks for any advice, and happy Thanksgiving!

Private Sub Command26_Click()

    Dim Query As String
    Dim db As Database
    Dim rs As Recordset
    Dim url1 As String
    Dim url3 As String
    Dim fullurl As String

    url1 = "http://arlenmusic.azurewebsites.net/access-event-musicians.php?
    event_musician_id="
    url3 = "&action=add"
    Query = "SELECT dbo_event_musician.job_number, 
    dbo_event_musician.event_musician_id, dbo_event_musician.sub_event_id, 
    dbo_event_musician.name, dbo_event_musician.instrument_key, 
    dbo_event_musician.set_up_time, dbo_event_musician.start_time, 
    dbo_event_musician.booked_until, dbo_event_musician.special_songs, 
    dbo_event_musician.attire, dbo_event_musician.include_status, 
    dbo_event_musician.sub, dbo_event_musician.status, dbo_event_musician.notes, 
    dbo_event_musician.date_entered from dbo_event_musician WHERE   
    (((dbo_event_musician.sub_event_id) Like [Forms]![event_scheduling]!
    [sub_event_selector])) ORDER BY dbo_event_musician.instrument_key;"

    Set db = CurrentDb
    Set rs = db.OpenRecordset(Query, , dbSeeChanges)
    Do Until rs.EOF
        fullurl = url1 + rs!event_musician_id + url3    
        Application.FollowHyperlink (fullurl)         
        rs.MoveNext     
    Loop     

    rs.Close
    Set rs = Nothing 
    Set db = Nothing    

End Sub

Upvotes: 0

Views: 1936

Answers (1)

this
this

Reputation: 1426

From your comments, it's actually not the dbSeeChanges that's the problem. Rather, it's that you are using Database.OpenRecordset, but it contains expression [Forms]![event_scheduling]![sub_event_selector], which is foreign to DAO.

See, it works in Access because you are going through the Access layer and Access recognizes those expressions and resolve them for you. But when you're using DAO directly (e.g. Database.Execute or Database.OpenRecordset), you are in fact bypassing Access and thus not getting those expression resolved. DAO correctly recognizes those as unresolved identifier and gives you the "too few parameters" error.

One way to resolve this is to make use of QueryDef.Parameters, then perform an OpenRecordset.

Here's a sample untested aircode to illustrate the point.

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset

Set db = CurrentDb
Set qdf = db.CreateQueryDef
qdf.SQL = "PARAMETERS [MyFirstParameter] TEXT, [MySecondParameter] LONG; SELECT a, b, c FROM myTable WHERE a = [MyFirstParameter] AND b = [MySecondParameter];"
qdf.Parameters("MyFirstParameter").Value = "abc"
qdf.Parameters("MySecondParameter").Value = 2
Set rs = qdf.OpenRecordset(dbOpenDynaset, dbSeeChanges)

You can consult MSDN for more documentation here. The advantage of that approach is that you can specify what will be parameterized and how it should be resolved, then directly assign the value from where you were originally reading. That way you can avoid using fragile expressions like Forms!MyForm!MyControl which won't be validated until runtime and could give unexpected results.

The alternative is to wrap the expression in an Eval() which will allow it to work in both contexts but I really do not like that because it basically allows arbitrary code execution in SQL and I'd prefer to avoid that.

Upvotes: 2

Related Questions