May 9, 2014 at 9:56 am
jdasilva (5/9/2014)
SQL is delicious (5/9/2014)
Sean Lange (5/9/2014)
jdasilva (5/9/2014)
J Livingston SQL (5/9/2014)
is/can your form be based on a single table/sql query?Yes, and it currently is, but that query refines data based on a an office code selected from the opening form (which is refeneced on each other 'sub' form of the main). Basically, I can have all the queries done to hold all the required data of each form/report, but I would then still need to filter for the office code. Again, my issue is when other offices run the reports, there is a noticable lag.
Use a stored proc with a parameter to filter by office. Do not pull all the data back from sql server and store it in Access just to filter most of it out. Get the data you need as you need it from sql server via parameterized stored procedures.
Excellent advice, and that's what I did when I had a similar project. Think of Access as a dainty little kitten that is easily exhausted and SQL Server as an Olympic weightlifter. Make it do the heavy lifting; that's what it's good at. :w00t:
I will have to look more into "stored procs", as this is new to me (maybe just the terminology, or how/where to implement it). I understand about access being weak compared to SQL, and I am trying to get all the work done on SQL, just trying to rap my head around it.
In one case, I have a form with a field that stores the Location, called thisLoc. On this form is a button that runs a report called phonelist. this phonelist report is based on a query that is currently taking the data from 2 SQL tables, and filtering out all but those that match thisLoc.
My problem is where and how to I implement the stored procedure? I am guessing that I create the query in VBA in the form that uses thisLoc as the filter from the SQL data. but then how does the report see this filtered data?
See my post on page 1 of this thread. I provided an example of how to create the proc AND the code needed in VBA to call it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 9, 2014 at 10:08 am
SQL is delicious (5/9/2014)
jdasilva (5/9/2014)
...This article is insightful; I don't want to speak to the performance pitfalls of recent versions of Access because my knowledge is out-of-date and I don't know if it's relevant anymore.
The "performances" (or lack thereof) in the recent versions on Access (from 2007 upward) is a disaster. The same Access application communicating with the same SQL Server database (database located on the same SQL Server machine), running on the same machine with the same O.S. is twice slower when running in an Office 2010 (Access 14) environment than when running in an Office 2003 (Access 11) environment.
May 9, 2014 at 10:21 am
Sean,
I did look at this:
Yes make the variable portions of your queries parameters.
create procedure MyProcToGetSomeData
(
@LocID int
) as
select YourColumns
from YourTable
where Location = @LocID
but again, this is new to me, and I am not exactly sure where to place it. Are you saying create a new query in Access and put this code in it? (using the SQL builder in access)
May 9, 2014 at 10:25 am
this may or may not help you....years back, I used to do something similar to what you are experiencing and running for multiple sites across a slow WAN.
if I recall, this was in Access 2007 and we used runtime distribution and DSNless connections.
the following is what I can easily remember, but please accept that this is not tested in any way and should be only used as pseudo code to give you an idea....
create a view in SQL for all locations, that pulls back all the relevant columns required in Access....can be one or many joined tables.
in Access add this view as a " linked table.....this will depend on how you are connecting to SQL and what autorisation you are using
create a form in Access based on this datasource.
this will now pull everything back from SQL to Access...bad idea.!!!
create a combo/selection box/field in your access form....you appear to have this called <thisLoc>
set your access form recordsource to blank
create a button on your form called "search"
put following code on the click event of the "search" button on your form:
_____________________________________________________________________________
Private Sub Search_Click()
Dim strSQL As String
Dim strWHERE As String
Dim strORDER As String
strSQL = "SELECT <your>, <standard> , <sqlcolumns> FROM <sqlview/table>"
strWHERE = " WHERE 1=1 "
strORDER = " ORDER BY <your choice> "...if rquired
If Me.[<thisLoc>] > 0 Then
strWHERE = strWHERE & " AND LOCATION = " & Me.[<thisLoc>] & " "
End If
...where <thisLoc> is the combo/field on the access form
...the strWHERE clasue can be built upon for each access form selection required
strSQL = strSQL & strWHERE & strORDER
set the form recordsource to match the strSQL and requery
Me.<yourform>.Form.RecordSource = strSQL
Me.<yourform>.Requery
End Sub
if I remember the bigget bugbear was getting the correct placement of the double quotes to parse correctly...and that changed dependent upon what the Access datatype was...:-D
____________________________________________________________________________________
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 9, 2014 at 10:28 am
jdasilva (5/9/2014)
SQL is delicious (5/9/2014)
Sean Lange (5/9/2014)
jdasilva (5/9/2014)
J Livingston SQL (5/9/2014)
is/can your form be based on a single table/sql query?but then how does the report see this filtered data?
The resulting data set will be accessible by the Access report through the query you use to call the stored procedure. In summary:
- You have a strored procedure that accepts one parameter (or several) that is the criteria to filter the returned data set.
- You create a query object or recycle an existing one in Access using a DAO.Querydef object:
Dim qdf As DAO.Querydef
Set qdf = CurrentDb.CreateQueryDef("") ' Create a new query.
' Or:
Set qdf = CurrentDb.QueryDefs("QueryName") ' Use an existing query.
- Then you assign values to 3 (new query) or 2 (existing query) properties of the Querydef object:
qdf.Name = "QueryName" ' New query only.
qdf.Connect = "ODBC;Driver={SQL Server};Server=..." ' Connection string to the SQL Server.
qdf.SQL = "NameOfTheStoredProcedure @Criteria = 'Smith';" ' Name of the S.P. and is parameter(s).
qdf.Close ' Done.
qdf = Nothing ' Clean up.
From now on, you have a query ("QueryName") in your Access database that, when open, will return whatever the S.P. returns and that you can handle as any other Query in Access : Open it in Datasheet view, use it as the RecordSource property of an Access Form or Report, use it as the RowSource property of a Combobox or Listbox control, Create a DAO.Recorset on it, etc.
May 9, 2014 at 10:29 am
jdasilva (5/9/2014)
Sean,I did look at this:
Yes make the variable portions of your queries parameters.
create procedure MyProcToGetSomeData(
@LocID int
) as
select YourColumns
from YourTable
where Location = @LocID
but again, this is new to me, and I am not exactly sure where to place it. Are you saying create a new query in Access and put this code in it? (using the SQL builder in access)
No this would be the stored procedure in sql server. Then you call this stored procedure from Access using the code I posted for that side.
The idea is to stop using queries entirely in Access. If all the data lives in sql server there is no point in using Access for anything other than the GUI front end.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 9, 2014 at 11:03 am
Sean Lange (5/9/2014)
The idea is to stop using queries entirely in Access. If all the data lives in sql server there is no point in using Access for anything other than the GUI front end.
The problem is that the RecordSource property of an Access Form or query must be something on which a Recordset can be open: a SQL expression for an Access database, an Access table, a linked table or an Access query (pass-through or regular). The only exception I know consists in working in the ADP environment but this model of Access project is obsolete and not supported anymore. You can always assign a Recordset object to the intrinsic DAO.Recordset object of a form or a query:
Set Me.Recordset = SomeRecordset
However this is not recommended and can cause instability in the Access application. MS recommend to never address the built-in recordset object of a form or of a query directly, that's why the RecordsetClone object (that cannot be used in this case) exists.
May 9, 2014 at 11:37 am
Sean Lange (5/9/2014)
No this would be the stored procedure in sql server. Then you call this stored procedure from Access using the code I posted for that side.
The idea is to stop using queries entirely in Access. If all the data lives in sql server there is no point in using Access for anything other than the GUI front end.
Ok, this is more clear. Again, really a beginner when it comes to actually working on SQL and the sever. Thanks for bearing with me on this. I feel confident now that I think I can do a test with this.
May 9, 2014 at 12:04 pm
rf44 (5/9/2014)
Sean Lange (5/9/2014)
The idea is to stop using queries entirely in Access. If all the data lives in sql server there is no point in using Access for anything other than the GUI front end.The problem is that the RecordSource property of an Access Form or query must be something on which a Recordset can be open: a SQL expression for an Access database, an Access table, a linked table or an Access query (pass-through or regular). The only exception I know consists in working in the ADP environment but this model of Access project is obsolete and not supported anymore. You can always assign a Recordset object to the intrinsic DAO.Recordset object of a form or a query:
Set Me.Recordset = SomeRecordsetHowever this is not recommended and can cause instability in the Access application. MS recommend to never address the built-in recordset object of a form or of a query directly, that's why the RecordsetClone object (that cannot be used in this case) exists.
Makes me very happy that I don't use Access for anything at all...and haven't in a LONG time. Even when I did a few things with Access I never bound the form to a recordset. That just sounds ugly.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 9, 2014 at 12:55 pm
Sean Lange (5/9/2014)
Makes me very happy that I don't use Access for anything at all...and haven't in a LONG time. Even when I did a few things with Access I never bound the form to a recordset. That just sounds ugly.
In Access (or in any other application, as far as I know) there is no other way to retrieve data from a database than using a Recordset object, either implicit (Forms and Reports have a built-in Recordset) or explicit by declaring a Recordset object then linking it to a data provider.
Even if you use unbound forms, you must use a Recordset to get the data from the database and write the values (directly or using an array and the GetRows method) in the controls of the form. I've never heard of any other mean to perform this task, nor in Access, not in Excel, nor in C++, nor in any .Net languages and I'd be very interested in seeing how this would possibly be done.
May 9, 2014 at 1:00 pm
rf44 (5/9/2014)
Sean Lange (5/9/2014)
Makes me very happy that I don't use Access for anything at all...and haven't in a LONG time. Even when I did a few things with Access I never bound the form to a recordset. That just sounds ugly.In Access (or in any other application, as far as I know) there is no other way to retrieve data from a database than using a Recordset object, either implicit (Forms and Reports have a built-in Recordset) or explicit by declaring a Recordset object then linking it to a data provider.
Even if you use unbound forms, you must use a Recordset to get the data from the database and write the values (directly or using an array and the GetRows method) in the controls of the form. I've never heard of any other mean to perform this task, nor in Access, not in Excel, nor in C++, nor in any .Net languages and I'd be very interested in seeing how this would possibly be done.
Sure. That is why in the example I provided I used a stored proc in sql server to fill a Recordset. I am pretty sure you could use a stored proc as a binding source because it returns a recordset. If not directly you could fill the recordset and then bind your form to the recordset. The thing you were describing previously sounded quite strange but it might be just because a forum is a hard place to hear everything correctly. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 9, 2014 at 1:06 pm
rf44 (5/9/2014)
Sean Lange (5/9/2014)
Makes me very happy that I don't use Access for anything at all...and haven't in a LONG time. Even when I did a few things with Access I never bound the form to a recordset. That just sounds ugly.In Access (or in any other application, as far as I know) there is no other way to retrieve data from a database than using a Recordset object, either implicit (Forms and Reports have a built-in Recordset) or explicit by declaring a Recordset object then linking it to a data provider.
Even if you use unbound forms, you must use a Recordset to get the data from the database and write the values (directly or using an array and the GetRows method) in the controls of the form. I've never heard of any other mean to perform this task, nor in Access, not in Excel, nor in C++, nor in any .Net languages and I'd be very interested in seeing how this would possibly be done.
just to refresh my memory on Access... is it Form.RecordSource or Form.RecordSet that can be set in VBA modules for unbound forms.....would like to clarify my earlier post of pseudocode....:-)
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 9, 2014 at 2:50 pm
J Livingston SQL (5/9/2014)
just to refresh my memory on Access... is it Form.RecordSource or Form.RecordSet that can be set in VBA modules for unbound forms.....would like to clarify my earlier post of pseudocode....:-)
J Livingston SQL (5/9/2014)
just to refresh my memory on Access... is it Form.RecordSource or Form.RecordSet that can be set in VBA modules for unbound forms.....would like to clarify my earlier post of pseudocode....:-)
Both are theorically possible.
1. The normal way of doing things (i.e. the way M.S. recommend) is to assign the RecordSource property of a form to a data provider than can be a table, a linked table, a query or a SQL expression in a string. With that, the form will instanciate its own built-in DAO.Recordset object. You end up with a form that's unbound at design time but dynamically bound at run-time.
2. You can also supply a Recordset object (DAO or ADODB) to the form and assign it to the built-in Recordset of this form (Set Me.Recordset = SomeRecordset). This is not recommended, can create problems and compromise the stability of the application ("Access has encountered an unexpected error and must be closed, blah blah blah...").
3. You can also supply the data to the unbound form using a Recordset or an Array (or a collection) and handle everything manually. This is what I usually do when working with Access and SQL Server:
- A function (a Property of a custom class, actually) creates a temporary Query to communicate with the SQL server and retrieve the data (a temporary query is a query that has no name. It is destroyed when the Querydef is set to Nothing):
Public Function GetRow(Byval RowID As Long) As Variant
Const c_SQL As String = "ProcedureName @RowID = @1;"
Dim qdf as DAO.Querydef
Dim rst As DAO.Recordset
Set qdf = CurrentDb.CreateQuerydef("")
With qdf
.Connect = m_strConnect ' m_strConnect is a member variable of the class that contains the connection string.
.SQL = Replace(c_SQL, "@1", RowID)
Set rst = .OpenRecordset
If rst.Eof = False Then GetRow = rst.GetRows
rst.Close
.Close
End With
Set rst = Nothing
Set qdf = Nothing
End Function
- In the (unbound) form:
Private Sub LoadControls(Byval RowID as long)
Dim var As Variant
Dim i As Long
var = m_clsDataConnector.GetRow(RowID) ' m_clsDataConnector is a member variable of the form that contains a reference to an instance
' of the custom class used to communicate with SQL Server.
If Not Isempty(var) then
For i = 0 To Ubound(var)
m_colControls.Item(i).Value = var(i) ' m_colControl is a member variable of type Collection that contains
' the references to the controls that are data bound.
Next i
Else
' Handle the case of an empty data set.
End If
End Sub
May 12, 2014 at 10:27 am
Sean Lange (5/6/2014)
jdasilva (5/6/2014)
How does SQL get the value of the variable from the form in access?In the example I am working on, I am polling the data for specific info for each person in an office. Currently, since the query is in the access file, I check he current form that calls up the query. In that form is a text field that holds the current office code. Let's say that it is in [Forms]![aform]![loc]. How do I code my SQL statement on the SQL server to see this value?
Or are you saying YES to my statement about making a table that holds the current office location code? If this is what you are suggesting, what happens when multiple offices try and access this query at the same time?
SQL Server cannot see your form. However, your form is where you will execute the stored procedure. From Access it would be something like this:
cmd.CommandText = "MyStoredProc"
dim parm as New ADODB.Parameter
set parm = cmd.CreateParameter("LocID", adInteger)
cmd.Parameters.Append parm
cmd.Parameters("LocID") = [Forms]![aform]![loc]
Set rs = cmd.Execute()
I would recommend NOT using some sort of table to hold the current location. That is not a good approach when you can just use parameters.
Back again... Something still is not clicking for me. I created the stored procedure on the SQL server, but like when making an SQL query, it seems to save it as a file, instead of having it somewhere with the Tables and Views. (started with this : http://technet.microsoft.com/en-us/library/ms345415.aspx )
With your above code, how does Access find the stored procedure? I see nothing that references the SQL server, or how to point to the location it was saved to. I dont see it in the Stored Procedures (using hte objecct explorer).
May 12, 2014 at 10:41 am
jdasilva (5/12/2014)
Sean Lange (5/6/2014)
jdasilva (5/6/2014)
How does SQL get the value of the variable from the form in access?In the example I am working on, I am polling the data for specific info for each person in an office. Currently, since the query is in the access file, I check he current form that calls up the query. In that form is a text field that holds the current office code. Let's say that it is in [Forms]![aform]![loc]. How do I code my SQL statement on the SQL server to see this value?
Or are you saying YES to my statement about making a table that holds the current office location code? If this is what you are suggesting, what happens when multiple offices try and access this query at the same time?
SQL Server cannot see your form. However, your form is where you will execute the stored procedure. From Access it would be something like this:
cmd.CommandText = "MyStoredProc"
dim parm as New ADODB.Parameter
set parm = cmd.CreateParameter("LocID", adInteger)
cmd.Parameters.Append parm
cmd.Parameters("LocID") = [Forms]![aform]![loc]
Set rs = cmd.Execute()
I would recommend NOT using some sort of table to hold the current location. That is not a good approach when you can just use parameters.
Back again... Something still is not clicking for me. I created the stored procedure on the SQL server, but like when making an SQL query, it seems to save it as a file, instead of having it somewhere with the Tables and Views. (started with this : http://technet.microsoft.com/en-us/library/ms345415.aspx )
With your above code, how does Access find the stored procedure? I see nothing that references the SQL server, or how to point to the location it was saved to. I dont see it in the Stored Procedures (using hte objecct explorer).
When you are creating a stored procedure you have to execute the code that creates it on the server. If you save, you are saving a text file with the code. This is NOT the same thing as creating the stored procedure on the sql server. You reference the sql server with your connection string.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 16 through 30 (of 50 total)
You must be logged in to reply to this topic. Login to reply