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.