Binding mainform and subforms to SQL-server-view

[JonOle]

In Access 2007 I have a main form with several subforms. All forms are unbound, and the recordsets for the forms are set during the OnOpen-event in the main-form.

I use ADO and OleDB to connect to the SQL-server and to bind the recordsets with the forms.

Everything works OK when the forms open the first time.

In the main form I have a combo that resets the recordsets for all the forms depending on the selected value. When I do this, the recordset in the main form changes ok, but all the subforms are getting closed, so it's impossible to set their recordset-property. The subforms are not linked to the main form.

I have spent a couple of days trying to solve this, I'll post some of my code if that can help.

Regards
Jon Ole

[NeoPa]

I'd be interested in seeing the code that sets the Record Sources for the subforms. In the AfterUpdate event of the ComboBox I expect.

[JonOle]

I'd be interested in seeing the code that sets the Record Sources for the subforms. In the AfterUpdate event of the ComboBox I expect.

I've placed the code on the click-event in the combo:

Private Sub PersonLst_Click()
GjeldendePersonID = Me.ActiveControl
SettSkjemaFamPersRstEr
End Sub

GjeldendePerson ID is the global variable that holds the primary key. SettSkjemaFamPe rsRstEr is placed in a module and goes on like this:

Public Sub SettSkjemaFamPersRstEr()
SettFamilierPersonerRst
SettTreningsdetaljerRst
SettFamilieRst
SettAdresseRst
SettEpostRst
SettTransRst
SettOrdreRst
SettDugnadRst
End Sub

SettFamilierPer sonerRst replaces the recordset of the main form:

Public Sub SettFamilierPersonerRst()
Dim FormRst As New ADODB.Recordset
Dim SQLstr As String
Dim Conn As ADODB.Connection
Set Conn = New ADODB.Connection
With Conn
    .Provider = "Microsoft.Access.OLEDB.10.0"
    .Properties("Data Provider") = "SQLOLEDB"
    .Properties("Initial Catalog") = "kunstlop"
    .Properties("Data Source") = "myserver\SQLEXPRESS"
    .Properties("User ID") = "UID"
    .Properties("Password") = "pwd"
    .Open
End With
If Nz(GjeldendePersonID, 0) = 0 Then
    SQLstr = "Select TOP 1 * from vw_Form_PersonerFamilie "
'This is used when the form opens the first time.
Else
'This is used when the sub is called from the combo
    SQLstr = "Select * from vw_Form_PersonerFamilie where personid= " & GjeldendePersonID
End If
FormRst.Open SQLstr, Conn, adOpenKeyset, adLockOptimistic
Set Forms!PersonerFamilie.Recordset = FormRst
Set FormRst = Nothing
Set Conn = Nothing
End Sub

SettTreningsdet aljerRst replaces the recordset on the first subform:

Public Sub SettTreningsdetaljerRst()
Dim Conn As ADODB.Connection
Set Conn = New ADODB.Connection
With Conn
    .Provider = "Microsoft.Access.OLEDB.10.0"
    .Properties("Data Provider") = "SQLOLEDB"
    .Properties("Initial Catalog") = "kunstlop"
    .Properties("Data Source") = "myserver\SQLEXPRESS"
    .Properties("User ID") = "UID"
    .Properties("Password") = "pwd"
    .Open
End With
Dim TreningsTidRst As New ADODB.Recordset
TreningsTidRst.Open "Select * from qTreningLopereGjeldende where personid= " & GjeldendePersonID, Conn, adOpenKeyset, adLockOptimistic
If Not TreningsTidRst.EOF Then Set Forms!PersonerFamilie.sTreningstider.Form.Recordset = TreningsTidRst
Set TreningsTidRst = Nothing
Set Conn = Nothing
End Sub

(The conn-settings will of course be placed in a global function if I got this to work)

The remaining Sett*-statements takes care of the other subforms in the same way.

I would really appreciate some help with this :-)

[NeoPa]

I'm not ignoring this. I just had lots to keep me busy today.

I hope to get on to it later, but if you see nothing more before tomorrow please feel free to **Bump** it.

[NeoPa]

The first thing I notice is that the event you're using is not correct. You are assuming the ComboBox will only ever be set by the mouse. Also that all clicks will change the value. This is not reliable and the AfterUpdate event should be used instead.

I doubt that is your fundamental problem though.

It seems to me that in the most part you are making this a little more complicated than necessary. Specifying a different (generally filtered) record source every time instead of setting the record source once, and simply changing the filter whenever a new value is selected.

Does this make sense?

Can we proceed on these lines?

[JonOle]

The first thing I notice is that the event you're using is not correct. You are assuming the ComboBox will only ever be set by the mouse. Also that all clicks will change the value. This is not reliable and the AfterUpdate event should be used instead.

I doubt that is your fundamental problem though.

It seems to me that in the most part you are making this a little more complicated than necessary. Specifying a different (generally filtered) record source every time instead of setting the record source once, and simply changing the filter whenever a new value is selected.

Does this make sense?

Can we proceed on these lines?

Yes, this makes absolutely sense to me, and I agree that this is too complicated. I suppose it has been like this because I use the tools that I knows - a rather common habit, I think.

My goal is to gain acceptable speed in communication with my sql-server when connecting via vpn. It would have been very easy to use ODBC-linked tables, but I don't want to wait 5 minutes for the form to refresh... Therefore I want to gather only the information from the sql-server I need. The recordsource should also be updateable (I know the recordset in my code isn't, but they will be with the uniquetable-property).

Can you lead my on a better way? I have thought of pt-queries or sending parameters to stored procedures, but I haven't had time to experiment with this. And I was probably locked in my own stubbornness about wanting the revordset-method to work :-)

[NeoPa]

I don't see any problem with using the recordset method (except we may have to be creative about restricting the number of records when it is first opened).

Otherwise, I would simply change the .FilterBy & .FilterOn properties when the selection is changed. I can help with that if you need it, but I don't think you're likely to need much help with this. It's a lot simpler than what you're already doing.

I'm afraid that, though I understand some of the concepts you're using here, I have very little experience with making connections in code. Obviously I will help as much as I can if you need it, but that side of things is not a strong point for me.

Let me know if that helps or you want further explanation / help.

[NeoPa]

I should mention also, that where before you used "TOP 1" in your original record source, this will not be used when filtering. Instead, apply a filter of, possibly, an empty string. Subsequent uses will set a filter to match the selected value, but first time you clearly don't want too much delay for data. Filtering to an empty list should minimise that in a similar way to using "TOP 1".

[JonOle]

I don't see any problem with using the recordset method (except we may have to be creative about restricting the number of records when it is first opened).

Otherwise, I would simply change the .FilterBy & .FilterOn properties when the selection is changed. I can help with that if you need it, but I don't think you're likely to need much help with this. It's a lot simpler than what you're already doing.

I'm afraid that, though I understand some of the concepts you're using here, I have very little experience with making connections in code. Obviously I will help as much as I can if you need it, but that side of things is not a strong point for me.

Let me know if that helps or you want further explanation / help.

I don't think the connection is the problem here, but it can be something there that triggers Access to close the subforms when I reset the recordset in the mainform. But I would like to hear your suggestions - I think you have knowledge that could help me further on.

If I use filtering in the form, I would have to bind the form to the entire recordset, wouldn't I? Meaning that the whole table will be transferred from the sql-server to the client where Access frontend is, every time the query is refreshed? And again: This will take time and network resources?

If it is so: That's why I wanted to filter the data on the sql-server-side, and they will be - with creating a new recordset everytime I need new data in the forms.

But - I just don't understand why these subforms are closing when they not are connected with the mainform. So: If i cannot solve this, it might be a solution to use pass-through-queries as a recordsource. Then I can replace the query with a new one each time I need to update the data. Just want be less complicated, though ... :-)

[NeoPa]

...
If I use filtering in the form, I would have to bind the form to the entire recordset, wouldn't I? Meaning that the whole table will be transferred from the sql-server to the client where Access frontend is, every time the query is refreshed? And again: This will take time and network resources?
...

I don't think it would mean that if the filter is set at the start. However, you can only be sure if you test it.

My experience (not with SQL Server back end mind) is that applying a filter is passed back intelligently to the source (thereby stopping tranference of unrequired data). Check it out and see what you find in your case.

[JonOle]

I don't think it would mean that if the filter is set at the start. However, you can only be sure if you test it.

My experience (not with SQL Server back end mind) is that applying a filter is passed back intelligently to the source (thereby stopping tranference of unrequired data). Check it out and see what you find in your case.

I've tested some changes:

In the mainform I open the main recordset, use it to fill the combo and bind it to the form.recordset. It's filtered, but I see on the SQL-server trace that the whole table is transferred from the server to the client:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo err:
    Application.Echo False
    GjeldendePersonID = 48
    Dim FormRst As New ADODB.Recordset
    Dim SQLstr As String
    SQLstr = "Select * from vw_Form_PersonerFamilie ORDER BY Fornavn"

    FormRst.Open SQLstr,  Forms!PersonerFamilie.sAdresse.Form.Recordset.ActiveConnection, adOpenKeyset, adLockOptimistic
    Dim Teller
    Teller = 0
    Me.PersonLst.RowSource = ""

    With FormRst
        If .EOF = False Then
            Do Until .EOF
            Me.PersonLst.AddItem Item:=!PersonID & ";" & !Fornavn & ";" & !Mellomnavn & ";" & !Etternavn
            Teller = Teller + 1
            .MoveNext
            Loop
        End If

        .MoveFirst
    End With
    
    Set Forms!PersonerFamilie.Recordset = FormRst
    Set FormRst = Nothing
    
    If Nz(Me.OpenArgs, 0) <> 0 Then
        Me.Filter = "PersonID=" & Me.OpenArgs
    Else
        Me.Filter = "personID=" & GjeldendePersonID
    End If
    Me.FilterOn = True
    Application.Echo True
Exit Sub

err:
    Application.Echo True
    MsgBox err.Description
End Sub

I've this code on the afterupdate-event in the combo:

Private Sub PersonLst_AfterUpdate()
Dim FiltStr As String
Dim FiltStrFID As String
Dim NotUtover As Boolean
Dim NotFam As Boolean

FiltStr = "PersonID=" & Me.PersonLst
If Nz(Me.PersonLst, 0) <> 0 Then
    Me.FilterOn = False
        Me.Filter = FiltStr
    Me.FilterOn = True
End If

NotUtover = (Nz(Me.MedlemTypeID, 0) <> 3 And Nz(Me.MedlemTypeID, 0) <> 4)
Me.sTreningstider.Form.visible = (NotUtover = 0)

NotFam = Nz(Me.FamilieID, 0) = 0
Me.sFamilie.Form.visible = (NotFam = 0)

If NotUtover = False Then
    Me.sTreningstider.Form.FilterOn = False
        Me.sTreningstider.Form.Filter = FiltStr
    Me.sTreningstider.Form.FilterOn = True
End If
If NotFam = False Then
    FiltStrFID = "FamilieID=" & Me.FamilieID
    Me.sFamilie.Form.FilterOn = False
        Me.sFamilie.Form.Filter = FiltStrFID
    Me.sFamilie.Form.FilterOn = True
End If
End sub

It was impossible to filter the subforms so they didn't return any data when the recordsets where empty, so I just made them invisible when their primarykey was empty

I did also streamline the connections so they now share the first established connection with the server, making the form open a lot faster.

I'm not quite satisfied, because when the form opens the first time, every record in every table are transferred from the server, but the positive is that I can navigate in the form without having to retreive new data from the server (unless I am updating, of course)

[NeoPa]

So, if I understand you correctly, you are in the situation where all the data is transferred at startup (opening the main form), which is an appreciable delay, but from that point on the filtering enables you to see exactly what you need at any time without further delays?

Not what I'd hoped, but it sounds like it may be the best option if you can live with the big delay up-front.

It may be easier to live with if you pop up a form while this is all going on explaining the delay and requestion patience. This may involve keeping the other forms hidden until they're ready of course.

[NeoPa]

You probably don't need telling, but be very careful about using .ReQuery in this situation ;D

[JonOle]

You probably don't need telling, but be very careful about using .ReQuery in this situation ;D

Thank you very much for your help! No - I want use requery on these recordsets, will see how far I get with refresh.

By the way: Do you know if it is a built-in function in Access for sending group-email? I consider using a subform or listbox to mark several email-adresses and use these to open a new email in Outlook. I haven't started to look at this subject yet - well, - looking for a shortcut to information :-)

[NeoPa]

This isn't an easy subject. Mainly because of all the malware out there.

I am yet to do a similar project on my own databases, but I will look into Application Automation (of Outlook) certainly.