Do pass thru query connections persist?

I have an Access 2003 front end with a SQL Server 2005 Express backend. I was thinking of using pass thru queries as row sources for some combo boxes such as states/countries for addresses. My question is do pass thru queries, when used as a row source, keep a connection to the DB server? Or do they get the data, disconnect and populate the control?

I realize I could populate the controls with code, but this seems less hassle and will overcome the ValueList size limit if needed.Unless you explicitely dis-connect the connection always remains open. this will hold true even if you use a DB control.|||Hi

Access will open a connection the first time it interacts with SQL Server (linked table, pass through) and retain this connection until the application closes. A pass through query, however, is like a client side cursor (as I understand it) - it uses no further server resources once it has run.

BTW - disconnected list filling is perfectly simple and more secure than pass throughs:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsmart01/html/sa01l8.asp
The bottom entry (Assigning recordsets to controls) is one I like.

HTH|||Pootle that article was very helpful. It also mentioned using a properly shaped recordset for reports which was a question of mine on another post.

I did a quick search on properly shaped recordsets but didn't find anything. Does anyone know what it is?

Using a Access Project I was able to have a report use an ADO recordset however it seems the connection and recordset must remain open the whole time the report is open. When using the a recordset with a control I opened the recordset assigned it to the control and closed it and everything was fine. When I did the same for the report it would not open. If I don't close the recordset or connection the report works fine. Is there anyway around this?

Pootle thanks again for the article.|||Using a Access Project I was able to have a report use an ADO recordset however it seems the connection and recordset must remain open the whole time the report is open. When using the a recordset with a control I opened the recordset assigned it to the control and closed it and everything was fine. When I did the same for the report it would not open. If I don't close the recordset or connection the report works fine. Is there anyway around this?The key will be the cursor location - that is the magic setting that takes you into the world of The Disconnected. Did you set it the location to client in your first attempt (remembering that the default is server)?|||Yes, I copied the code verbatim from the control code. In the control code I had not set the connection cursor so I did it in the report code and it still did not work.

Here is my code behind the report.

Private Sub Report_Open(Cancel As Integer)

Dim conGlob As New ADODB.Connection
Dim rst As New ADODB.Recordset

conGlob.ConnectionString = "Provider=SQLOLEDB;" _
& "Data Source=SERVER;" _
& "Initial Catalog=DatabaseTable;" _
& "Trusted_Connection=Yes;"

conGlob.CursorLocation = adUseClient

conGlob.Open

With rst
.ActiveConnection = conGlob
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockReadOnly
End With

rst.Open "usp_GetStates", , , , adCmdStoredProc

Set Me.Recordset = rst

rst.Close
conGlob.Close

End Sub|||Your best bet would be to use an access data project and use stored procedures

First time I tried to "upgrade" a consultants "application" I noticed that one form opened 19 connections

1 for every objects data source and an additional connection for any object that was updateable...it was very ugly and very slow|||There's nothing inherent to disconnected access that means you need to open multiple connections. One form, one connection. 19+ connections sounds ugly and there would have been an overhead but I would have thought the other processes (populating 19 objects for starters) would be the killer. I imagine you smoothed a lot of other rough edges to get the improvement I presume you got.|||There's nothing inherent to disconnected access that means you need to open multiple connections. One form, one connection. 19+ connections sounds ugly and there would have been an overhead but I would have thought the other processes (populating 19 objects for starters) would be the killer. I imagine you smoothed a lot of other rough edges to get the improvement I presume you got.

Yeah, it's called a total rewrite using Java and actually doing data modeling with the business BEFORE we did a conversion|||I may have figured it out. I have been using the activity monitor in Management Studio Express looking at the active processes and locks.

If I set the recordset activeConnection to nothing I can then close the original connection without the report closing. In the activity monitor it looks like the connection times out or something. It doesn't disappear right away like when I close the report but it does after awhile even if the report is still open.