G
Guest
HowTo bind a form's recordsource to result of a pass-through query with input parameters using ADODB (not Jet please)
J
Jonathan Parminter
Hi Sparky,
assuming that the pass=through query is a saved query,
then use as the recordsource of the form in exactly the
same mannor as a simple select query.
Luck
Jonathan
please)
assuming that the pass=through query is a saved query,
then use as the recordsource of the form in exactly the
same mannor as a simple select query.
Luck
Jonathan
through query with input parameters using ADODB (not Jet
please)
G
Guest
Folks
He is further info on the subject - first I show and Access-Only solution then discuss a Stored Procedure approac
using SQL Server
------------------------------------ACCESS SOLUTION -----------------------------------------------------------------------------
The form's RecordSource property is initially empty so that the source can be dynamically set by the following code based on a condition I chose
If condition1 The
Me.RecordSource = "qryMyQuery1
Els
Me.RecordSource = "qryMyQuery2
End I
Note that Access runs the query at the moment that the above assignement is made
The query is a typical Access query create in the design view
It has 2 criteria values such as
[Forms]![MyForm]![Control1
an
[Forms]![MyForm]![Control2
This coding approach works great - I can alter the Criteria on-the-fly basd on control values
However I am stuck with a terrible performance hit. So, I want to use a stored procedur
approach without losing the dynamic flexibility of the above solution
-------------------------------------------- STORED PROCEDURE APPROACH --------------------------------------------
I create a stored procedure based on the SQL code in the design view
Then I pass the parameters (which mimic the design view's criteria above) to the sp using th
parameters object
Set cmd = New ADODB.Comman
cmd.ActiveConnection = con
cmd.CommandType = adCmdStoredPro
cmd.CommandText = "spQryMyQuery1" '
cmd.Parameters.Refresh
cmd.Parameters("@P1") = [Forms]![MyForm]![Control1
cmd.Parameters("@P2") = [Forms]![MyForm]![Control2
cmd.Execut
At this point, I have no way to automatically bind a result to the form since
1. Nothing has been assigned to the form's RecordSource propert
2. Nothing has been used to fetch the result set from the cmd.Execut
Some folks use a new Access binding feature invoked as follows
Set Me.Recordset = r
where rs is created and used to fetch the result of the query. I guess that it ma
update the form just like the RecordSource property does in the code shown at the top
I could just grab the rs data and programmatically stuff all the form's 15 fields and forget poking the
RecordSource property as I did in the Access Exampel at the top
Questions
1. Does Set Me.Recordset = rs really bind the result set automatically to the form elements like the RecordSource method
2. Are there better ways to do a dynamic query and automatically bind using a stored procedure
3. Are there any URL's to solution that work or tutorials on the issue? (MSDN, TechNEt, Google, Groups, Web
Spark
He is further info on the subject - first I show and Access-Only solution then discuss a Stored Procedure approac
using SQL Server
------------------------------------ACCESS SOLUTION -----------------------------------------------------------------------------
The form's RecordSource property is initially empty so that the source can be dynamically set by the following code based on a condition I chose
If condition1 The
Me.RecordSource = "qryMyQuery1
Els
Me.RecordSource = "qryMyQuery2
End I
Note that Access runs the query at the moment that the above assignement is made
The query is a typical Access query create in the design view
It has 2 criteria values such as
[Forms]![MyForm]![Control1
an
[Forms]![MyForm]![Control2
This coding approach works great - I can alter the Criteria on-the-fly basd on control values
However I am stuck with a terrible performance hit. So, I want to use a stored procedur
approach without losing the dynamic flexibility of the above solution
-------------------------------------------- STORED PROCEDURE APPROACH --------------------------------------------
I create a stored procedure based on the SQL code in the design view
Then I pass the parameters (which mimic the design view's criteria above) to the sp using th
parameters object
Set cmd = New ADODB.Comman
cmd.ActiveConnection = con
cmd.CommandType = adCmdStoredPro
cmd.CommandText = "spQryMyQuery1" '
cmd.Parameters.Refresh
cmd.Parameters("@P1") = [Forms]![MyForm]![Control1
cmd.Parameters("@P2") = [Forms]![MyForm]![Control2
cmd.Execut
At this point, I have no way to automatically bind a result to the form since
1. Nothing has been assigned to the form's RecordSource propert
2. Nothing has been used to fetch the result set from the cmd.Execut
Some folks use a new Access binding feature invoked as follows
Set Me.Recordset = r
where rs is created and used to fetch the result of the query. I guess that it ma
update the form just like the RecordSource property does in the code shown at the top
I could just grab the rs data and programmatically stuff all the form's 15 fields and forget poking the
RecordSource property as I did in the Access Exampel at the top
Questions
1. Does Set Me.Recordset = rs really bind the result set automatically to the form elements like the RecordSource method
2. Are there better ways to do a dynamic query and automatically bind using a stored procedure
3. Are there any URL's to solution that work or tutorials on the issue? (MSDN, TechNEt, Google, Groups, Web
Spark
M
Mingqing Cheng [MSFT]
Hi Sparky,
From your descriptions, I want to make sure the following questions
1. You are using mdb instead of adp, isn't it?
2. What's the back database you are using now, SQL Server or others?
I understood you would like to simulate pass-through query using ADO. Have
I understood you? If there is anything I misunderstood, please feel free to
let me know
First of all, the following document will show you how to make it
ACC2000: How to Simulate Parameters in an SQL Pass-Through Query
http://support.microsoft.com/?id=232493
However, SQL pass-through queries bypass the Jet engine and execute
directly on
the backend server such as SQL Server or Oracle. The backend engine cannot
derive the values of parameters represented by Access form or report
references. The following example will show how to do it to eliminate this
kind of limitations
(assuming - txtLNameParam is a text box on a form
- The code executes off a button's OnClick event:
=ParamSPT([Forms]![frmSPT_authors])
)
Function ADO_Filter(pfrmFilter As Form)
'!! Add ADO eXtension 2.5 typelib to the code project's references!!
Dim CMD As ADODB.Command
Dim CAT As ADOX.Catalog
Dim RS As ADODB.Recordset
Dim CN As ADODB.Connection
'Handle a null value in text box.
If IsNull(pfrmFilter!txtLNameParam) Then Exit Function
Set CAT = New ADOX.Catalog
CAT.ActiveConnection = CurrentProject.Connection
Set CN = New ADODB.Connection
With CN
.Provider = "SQLOLEDB"
'The Data Source is the server name, not the DSN.
.ConnectionString = "Data Source=accsql2000;Initial
Catalog=Pubs;Integrated Security=SSPI"
.CursorLocation = adUseServer
.Open
End With
Set CMD = CAT.Procedures("SPT_authors").Command
'Since we are not really running the pass-through query to retrieve the
filtered
'records, a Connection (CN) must be defined that points at the SQL
Server and the db.
CMD.ActiveConnection = CN
CMD.CommandText = CMD.CommandText & " Where authors.au_lname = '" &
pfrmFilter!txtLNameParam & "'"
'Note: Because the Command (CMD) object is distinct from the saved
pass-through query,
'we are not modifying the query's SQL with the WHERE clause, unlike the
DAO example.
'Therefore we don't have to restore the original SELECT statement after
modifying
'the query's SQL.
Set RS = New ADODB.Recordset
'Populate the Recordset with records returned from executing the CMD
'object on the server.
RS.Open CMD, , adOpenKeyset, adLockOptimistic
'Set the form's Recordset property to the newly filtered recordset.
Set pfrmFilter.Recordset = RS
Set CMD = Nothing
Set CAT = Nothing
Set RS = Nothing
End Function
Function DAO_Filter(pfrmFilter As Form)
'!! Add DAO 3.6 typelib to the code project's references!!
Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim MyParam As String
Dim SQLString As String, SQLString_Old As String
Dim SPTQueryName As String
'This line is just for this sample. If you switch back
'to DAO from ADO filtering, the form's recordset is still set
'to the ADO-generated recordset. These two lines ensure
'we have the full Jet recordset for the DAO testing.
pfrmFilter.RecordSource = ""
pfrmFilter.RecordSource = "SPT_authors"
Set MyDatabase = CurrentDb()
SPTQueryName = "SPT_authors"
Set MyQueryDef = MyDatabase.QueryDefs(SPTQueryName)
'Handle a null value in text box.
If IsNull(pfrmFilter!txtLNameParam) Then Exit Function
'Parameter value from frmSPT_authors
MyParam = pfrmFilter!txtLNameParam
'Store the original SPT_authors query SQL
SQLString_Old = MyQueryDef.SQL
'Get the SPT_authors query's SQL.
SQLString = MyQueryDef.SQL
'Add the Where clause
SQLString = SQLString & " WHERE authors.au_lname = '" & MyParam & "'"
'Set the query's new SQL
MyQueryDef.SQL = SQLString
'Regenerate the form's dataset
pfrmFilter.Requery
'Reset the query's SQL to its original text.
MyQueryDef.SQL = SQLString_Old
MyQueryDef.Close
MyDatabase.Close
End Function
Hope this helps and if you have any questions or concerns, don't hesitate
to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
From your descriptions, I want to make sure the following questions
1. You are using mdb instead of adp, isn't it?
2. What's the back database you are using now, SQL Server or others?
I understood you would like to simulate pass-through query using ADO. Have
I understood you? If there is anything I misunderstood, please feel free to
let me know
First of all, the following document will show you how to make it
ACC2000: How to Simulate Parameters in an SQL Pass-Through Query
http://support.microsoft.com/?id=232493
However, SQL pass-through queries bypass the Jet engine and execute
directly on
the backend server such as SQL Server or Oracle. The backend engine cannot
derive the values of parameters represented by Access form or report
references. The following example will show how to do it to eliminate this
kind of limitations
(assuming - txtLNameParam is a text box on a form
- The code executes off a button's OnClick event:
=ParamSPT([Forms]![frmSPT_authors])
)
Function ADO_Filter(pfrmFilter As Form)
'!! Add ADO eXtension 2.5 typelib to the code project's references!!
Dim CMD As ADODB.Command
Dim CAT As ADOX.Catalog
Dim RS As ADODB.Recordset
Dim CN As ADODB.Connection
'Handle a null value in text box.
If IsNull(pfrmFilter!txtLNameParam) Then Exit Function
Set CAT = New ADOX.Catalog
CAT.ActiveConnection = CurrentProject.Connection
Set CN = New ADODB.Connection
With CN
.Provider = "SQLOLEDB"
'The Data Source is the server name, not the DSN.
.ConnectionString = "Data Source=accsql2000;Initial
Catalog=Pubs;Integrated Security=SSPI"
.CursorLocation = adUseServer
.Open
End With
Set CMD = CAT.Procedures("SPT_authors").Command
'Since we are not really running the pass-through query to retrieve the
filtered
'records, a Connection (CN) must be defined that points at the SQL
Server and the db.
CMD.ActiveConnection = CN
CMD.CommandText = CMD.CommandText & " Where authors.au_lname = '" &
pfrmFilter!txtLNameParam & "'"
'Note: Because the Command (CMD) object is distinct from the saved
pass-through query,
'we are not modifying the query's SQL with the WHERE clause, unlike the
DAO example.
'Therefore we don't have to restore the original SELECT statement after
modifying
'the query's SQL.
Set RS = New ADODB.Recordset
'Populate the Recordset with records returned from executing the CMD
'object on the server.
RS.Open CMD, , adOpenKeyset, adLockOptimistic
'Set the form's Recordset property to the newly filtered recordset.
Set pfrmFilter.Recordset = RS
Set CMD = Nothing
Set CAT = Nothing
Set RS = Nothing
End Function
Function DAO_Filter(pfrmFilter As Form)
'!! Add DAO 3.6 typelib to the code project's references!!
Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim MyParam As String
Dim SQLString As String, SQLString_Old As String
Dim SPTQueryName As String
'This line is just for this sample. If you switch back
'to DAO from ADO filtering, the form's recordset is still set
'to the ADO-generated recordset. These two lines ensure
'we have the full Jet recordset for the DAO testing.
pfrmFilter.RecordSource = ""
pfrmFilter.RecordSource = "SPT_authors"
Set MyDatabase = CurrentDb()
SPTQueryName = "SPT_authors"
Set MyQueryDef = MyDatabase.QueryDefs(SPTQueryName)
'Handle a null value in text box.
If IsNull(pfrmFilter!txtLNameParam) Then Exit Function
'Parameter value from frmSPT_authors
MyParam = pfrmFilter!txtLNameParam
'Store the original SPT_authors query SQL
SQLString_Old = MyQueryDef.SQL
'Get the SPT_authors query's SQL.
SQLString = MyQueryDef.SQL
'Add the Where clause
SQLString = SQLString & " WHERE authors.au_lname = '" & MyParam & "'"
'Set the query's new SQL
MyQueryDef.SQL = SQLString
'Regenerate the form's dataset
pfrmFilter.Requery
'Reset the query's SQL to its original text.
MyQueryDef.SQL = SQLString_Old
MyQueryDef.Close
MyDatabase.Close
End Function
Hope this helps and if you have any questions or concerns, don't hesitate
to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
G
Guest
How to use the "Me.Recordset = cmd.Execute" to set a form's recordsource property. The solution must allow programmatic changes to the the form's recordsource property, have a SQL Server back-end with a .mdb front-end
I'm using parameters instead of an Access pass-through to avoid bloating the .mdb file on each query
An automatic way to avoid growth in the file size would be cool so users don't have to be gurus to avoid .mdb file bloat
Also, is it impossible to populate a datasheet form with a ecordset whereas a table or query is normally used
I assumed there was some magic in the new Me.recordset paradigm that enabled populating a datasheet as compared to a single control
I'm using parameters instead of an Access pass-through to avoid bloating the .mdb file on each query
An automatic way to avoid growth in the file size would be cool so users don't have to be gurus to avoid .mdb file bloat
Also, is it impossible to populate a datasheet form with a ecordset whereas a table or query is normally used
I assumed there was some magic in the new Me.recordset paradigm that enabled populating a datasheet as compared to a single control
B
Billy Yao [MSFT]
Hi Sparky,
This is Billy and I will cowork with you on this issue as Mingqing is OOF today.
Thank you for your update and let us know your concerns. Please see my follow-up post for further
suggestions.
[Kind reminder]: Next time, please reply only to newsgroups. Thanks in advance!
===============================
-----Original Message-----
Subject: FeedBack: Use ADO recordset as form's recordsource in microsoft.public.access.formscoding
Issue: 22998424
Mingquing:
Thanks for the reply.
I have updated the posting with a title that cites SQL Server and other information.
Here is the code for the query and the capture of the resulting recordset.
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command
conn.Open "Driver={SQL Server};server = MyServer;
database=MyDB;uid=;pwd="
cmd.ActiveConnection = conn
cmd.CommandText = "spMyQuery"
cmd.CommandType = adCmdStoredProc
cmd.Parameters("@P1") = 7
cmd.Parameters("@P2") = 1
Me.SubForm.Form.Recordset = cmd.Execute
At this point, I want a datasheet to be filled with the recordset since there ius no way to Programmatically fill a
datasheet with values - I must use a recordsource property (table or query).
TIA
Sparky
This is Billy and I will cowork with you on this issue as Mingqing is OOF today.
Thank you for your update and let us know your concerns. Please see my follow-up post for further
suggestions.
[Kind reminder]: Next time, please reply only to newsgroups. Thanks in advance!
===============================
-----Original Message-----
Subject: FeedBack: Use ADO recordset as form's recordsource in microsoft.public.access.formscoding
Issue: 22998424
Mingquing:
Thanks for the reply.
I have updated the posting with a title that cites SQL Server and other information.
Here is the code for the query and the capture of the resulting recordset.
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command
conn.Open "Driver={SQL Server};server = MyServer;
database=MyDB;uid=;pwd="
cmd.ActiveConnection = conn
cmd.CommandText = "spMyQuery"
cmd.CommandType = adCmdStoredProc
cmd.Parameters("@P1") = 7
cmd.Parameters("@P2") = 1
Me.SubForm.Form.Recordset = cmd.Execute
At this point, I want a datasheet to be filled with the recordset since there ius no way to Programmatically fill a
datasheet with values - I must use a recordsource property (table or query).
TIA
Sparky
B
Billy Yao [MSFT]
Hi Sparky,
Thank you for your update! After reviewing the whole thread, I understand that you would like to bind Access
Forms to ADO Recordset(s), where the back-end database is a SQL Server database.
Based on my experience, there are limitations when binding forms to the ADO record. (The previous
method "Dynamically set the RecordSource property" seems good, but it meets some performance
issues) In Access 2000, you may meet with the known issue that the bound form is read-only:
227053 ACC2000: Forms Based on ADO Recordsets Are Read-Only
http://support.microsoft.com/?id=227053
In Access 2002 and later, to bind a Microsoft Access form to a recordset, you must set the Recordset
property of the form to a valid Data Access Objects (DAO) or ADO Recordset object. However, there are
still two main requirements for supporting updateability when you bind a form to an ADO recordset that is
using SQL Server data:
The ADO recordset's connection must use the Microsoft Access 10.0 (Access 2002) OLEDB provider as its
service provider.
The ADO recordset's connection must use the Microsoft SQL Server OLEDB provider as its data provider.
When you create ADO recordsets within Microsoft Access, you have a choice as to which ADO connection
will be used by the recordset. Your ADO code can share the ADO connection that Microsoft Access is using
for the SQL Server database currently open in an Access project (ADP) file; or you can programmatically
create a new ADO connection to a different SQL Server database.
For detailed code to bind the form to an ADO recordset (SQL Server), please refer to the topic "Opening a
Separate ADO Connection" in the following KB article #281998:
281998 How to Bind Microsoft Access Forms to ADO Recordsets
http://support.microsoft.com/?id=281998
For the second issue of "a datasheet to be filled with the recordset", I recommend you use the Form
Datasheet View to solve this issue if feasible.
If there is anything else we can assist you with, please feel free to post it in the group. Thanks for your
cooperation.
Best regards,
Billy Yao
Microsoft Online Support
Thank you for your update! After reviewing the whole thread, I understand that you would like to bind Access
Forms to ADO Recordset(s), where the back-end database is a SQL Server database.
Based on my experience, there are limitations when binding forms to the ADO record. (The previous
method "Dynamically set the RecordSource property" seems good, but it meets some performance
issues) In Access 2000, you may meet with the known issue that the bound form is read-only:
227053 ACC2000: Forms Based on ADO Recordsets Are Read-Only
http://support.microsoft.com/?id=227053
In Access 2002 and later, to bind a Microsoft Access form to a recordset, you must set the Recordset
property of the form to a valid Data Access Objects (DAO) or ADO Recordset object. However, there are
still two main requirements for supporting updateability when you bind a form to an ADO recordset that is
using SQL Server data:
The ADO recordset's connection must use the Microsoft Access 10.0 (Access 2002) OLEDB provider as its
service provider.
The ADO recordset's connection must use the Microsoft SQL Server OLEDB provider as its data provider.
When you create ADO recordsets within Microsoft Access, you have a choice as to which ADO connection
will be used by the recordset. Your ADO code can share the ADO connection that Microsoft Access is using
for the SQL Server database currently open in an Access project (ADP) file; or you can programmatically
create a new ADO connection to a different SQL Server database.
For detailed code to bind the form to an ADO recordset (SQL Server), please refer to the topic "Opening a
Separate ADO Connection" in the following KB article #281998:
281998 How to Bind Microsoft Access Forms to ADO Recordsets
http://support.microsoft.com/?id=281998
For the second issue of "a datasheet to be filled with the recordset", I recommend you use the Form
Datasheet View to solve this issue if feasible.
If there is anything else we can assist you with, please feel free to post it in the group. Thanks for your
cooperation.
Best regards,
Billy Yao
Microsoft Online Support
M
Mingqing Cheng [MSFT]
Hi Sparky,
Thanks for your prompt updates!
First of all, there are samples in the KB Billy gives for almost all kinds
of conditions (Esp chapter Opening a Separate ADO Connection)
How to bind Microsoft Access forms to ADO recordsets
http://support.microsoft.com/?id=281998
In this KB, you will find that the definition of connection sould be like
this:
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Access.OLEDB.10.0"
.Properties("Data Provider").Value = "SQLOLEDB"
.Properties("Data Source").Value = "MySQLServer"
.Properties("User ID").Value = "sa"
.Properties("Password").Value = ""
.Properties("Initial Catalog").Value = "NorthwindCS"
.Open
End With
Secondly, the recordset should be set like this so that could put it
determined on the fly (you could add your strings in .source cod)
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = "SELECT * FROM Customers"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
End With
'Set the form's Recordset property to the ADO recordset
Set Me.Recordset = rs
Hope this helps and if you have any difficulties on making this sample,
please feel free to let me know, I will be glad of help
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
Thanks for your prompt updates!
First of all, there are samples in the KB Billy gives for almost all kinds
of conditions (Esp chapter Opening a Separate ADO Connection)
How to bind Microsoft Access forms to ADO recordsets
http://support.microsoft.com/?id=281998
In this KB, you will find that the definition of connection sould be like
this:
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Access.OLEDB.10.0"
.Properties("Data Provider").Value = "SQLOLEDB"
.Properties("Data Source").Value = "MySQLServer"
.Properties("User ID").Value = "sa"
.Properties("Password").Value = ""
.Properties("Initial Catalog").Value = "NorthwindCS"
.Open
End With
Secondly, the recordset should be set like this so that could put it
determined on the fly (you could add your strings in .source cod)
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = "SELECT * FROM Customers"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
End With
'Set the form's Recordset property to the ADO recordset
Set Me.Recordset = rs
Hope this helps and if you have any difficulties on making this sample,
please feel free to let me know, I will be glad of help
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
G
Guest
Hi,
Two additional questions:
1. What about dynamic queries inside stored procedure ?
I build my stored procedure like a dynamic query.
Example of Stored Procedure:
************************************
....
DECLARE @sCmd varchar(255)
SET @sCmd = 'SELECT * FROM Table1 WHERE ' + @Param1 + '=' + @Param2
exec (@sCmd)
************************************
In this case setting RS for the form
Me.SubForm.Form.Recordset = cmd.Execute
always shows the error.
Is there the way how to set ADO recordset for a form if stored procedure uses dynamic queries ?
2. What about stored procedure with multiple recordsets ?
Is it possible to use the same methodology by setting RS to the forms if stored procedure
returns multiple recordsets ?
Regards,
Andrius.
Two additional questions:
1. What about dynamic queries inside stored procedure ?
I build my stored procedure like a dynamic query.
Example of Stored Procedure:
************************************
....
DECLARE @sCmd varchar(255)
SET @sCmd = 'SELECT * FROM Table1 WHERE ' + @Param1 + '=' + @Param2
exec (@sCmd)
************************************
In this case setting RS for the form
Me.SubForm.Form.Recordset = cmd.Execute
always shows the error.
Is there the way how to set ADO recordset for a form if stored procedure uses dynamic queries ?
2. What about stored procedure with multiple recordsets ?
Is it possible to use the same methodology by setting RS to the forms if stored procedure
returns multiple recordsets ?
Regards,
Andrius.
"Billy Yao [MSFT]" said: