Access Front End and SQL Server Back End

[Kevin_S]

Hi Everyone,

I've been working on a little problem with the help of Mission2Java and I wanted to post a few questions here to try and further the solution to this probme along.

First - A simple question: When dealing with linked SQL Server tables in an A2002 front end does Access, when the db is opened, spool all of the records for the tables... or does this only happen when - either the user opens the table directly OR opens a form based on the table? If this only occurs when the user opens a table or a form based on a table then I have a solution to my problem if Access spools on the opening of the db then....

The Problem:
I have a recordset that was retrieved from a stored procedure. I need to be able to set a form's recordsource to be this retrieved recordset. Is there a way to do this..? and if so could someone possibly explain how?

 

[Kevin_S]

Hi Pat - thanks for the response!

My intro was a little misleading on the help mission2java was providing me - we were working on this through e-mail and not a post so this was the first one on the topic -(I didn't want you to think I was breaking forum etiquette and double-posting )

So - if I'm interpreting your post correctly - If I have the linked SQL Server tables in my front-end then Access will not spool all of the records? AND - as long as I use queries with select statements for the basis of forms and reports I will only pull across to the linked tables the data requested???

What I'm dealing with here is that we, for the lack of a better word, have HUGE gaps in our network, where over 60% of our users are using dial-up connections to link into office dbs. The creator of the previous version of this db (working on a broken hand-me-down) created this app with a split fe/be Access database with both the front end AND back end in Access. Our users experienced Looooonnnnnggg delays in getting into the app as they had to wait for Access to spool all of the records.

I was hoping that since the db has been re-designed and normalized - in addition to moving the back-end to SQL Server and basing forms/reports on queries -- that some of this lag will be resolved (hence looking at stored procedure -- but -- as you said --- if the data is not updateable --- this will not work)

I realize that a system is only as fast as the weakest link (network) but I was trying to find a few work arounds....


Any/All insight provided is fully appreciated,

Thanks-Kevin

 

[mission2java_78]

Are you all set kevin?

Kevin you don't need a sproc though. But thats upto you.

Jon

 

[Kevin_S]

Hi Pat and Jon,

The performance problem I feel is, unfortunitly, not a result of too much data being transferred but a result of the time it takes for Access to relink to the back end. This location was (in a previous version) to a file setting on a network share drive but is now on a SQL Server. I am going to do some testing this afternoon with connection speeds and I should be able to tell more after that testing. I plan on having a field location connect using the new version (w/ SQL server) without any data at all & then the new version with the normal dataset. This will help me nail down wether I'm dealing with slow performance due to the amount of data being transferred or due to a slow relink to tables (I'm guessing this is the culprit)

Pat - I am also interested in the senario you provided too. At this time I use an unbound form with a combo box and a listbox. The combo box is used to select a location - the listbox then displays all records related to the selected location. The user then double-clicks the preferred record in the listbox and the main form is opened and filtered to show just that record.

Soooo....

If I'm following your logic correctly I could have the combo box (based on a lookup table in the front end) still select the location and then fill in the listboxes where clause of its query -- then on the double-click event on the listbox records -- fill in the main form's where clause in its query with the requested record ID, and then open the form???

Is this the right logic?

Thanks,
Kevin

 

[mission2java_78]

Are you filtering or using tight SQL Stmnts to limit your recordsource?

Jon

 

[Kevin_S]

Currently I'm filtering the selection made on the "Search" form for the record to display on the "main" form with this:
................................................................
If Me.List0.ListCount < 1 Then
Exit Sub
Else
Dim strFormName As String
strFormName = "frmMain"
DoCmd.OpenForm strFormName, , , "[SubmissionID]=" & "'" & Me!List0 & "'"
End If
.................................................................

So - what I need to do then instead of this (again if I'm following the logic) is that instead of filtering the form 'frmMain' (which is bound to a table) I need to instead base the form off of a query and then pass the selection made in the listbox over to the where statement in the form....Right?

thx
Kev

 

[mission2java_78]

Kevin remember that last email I sent you?

I told you to use an sql stmnt basically a WHERE clause to generate the recordsource. I misplaced your email.

Do not filter your form...just dynamically change the source of your data using SQL.

Jon

 

[Kevin_S]

Yeah I got it - I'm going to give it a shot right now and I'll post back after I test it out....

Thanks,
kevin

 

[mission2java_78]

I think he's basing this on specific criteria on a form. I might be mistaken. This criteria could change over and over. I think rather than having 'n' different queries that using some code to accomplish this is a lot quicker.

Jon

 

[Kevin_S]

I got it working now....

Here's what I did for the test:

Built 2 forms:
frmSearch
frmMain

Built 2 Queries:
qryListbox
qryMainForm

On the form 'frmSearch'

1 combo box with units - 1 listbox based off of query 'qryListbox'
-combobox filters listbox query and narrows down records to be selected by user.

On the form 'frmMain'

Based form off of query which has the WHERE clause that looks to the selection made in the frmSearch Listbox and returns only the selected row.

How it comes together:

User selects entry in combobox on frmSearch which populates the listbox query. User double-clicks chosen record which then opens the frmMain and the query from the frmMain looks at the search form for the selected record and then returns that single record.

For the test this worked great and there was NO noticeable delay in the db (which I still am a bit skeptical about... We are doing further testing today and I should know more then.)

Thank you both for helping me work the solution out. I appreciate it!

Thanks Again,
kevin