May 6, 2014 at 12:02 pm
For the most part this is pretty easy once you know where to put things, but what if the query pulls info from a form? In my case, I have a field on my forms that denote what office you are viewing the data from. My current Access queries use the "LIKE [Forms]![theform]![Loc]" criteria. But I'm pretty sure that the SQL server does not see the Access file in a way that I can directly access the form... or can it?
Right now, the only way I see of doing this is making a table to store the current selected office code, then reference that. Is there another way?
May 6, 2014 at 12:10 pm
jdasilva (5/6/2014)
For the most part this is pretty easy once you know where to put things, but what if the query pulls info from a form? In my case, I have a field on my forms that denote what office you are viewing the data from. My current Access queries use the "LIKE [Forms]![theform]![Loc]" criteria. But I'm pretty sure that the SQL server does not see the Access file in a way that I can directly access the form... or can it?Right now, the only way I see of doing this is making a table to store the current selected office code, then reference that. Is there another way?
Yes make the variable portions of your queries parameters.
create procedure MyProcToGetSomeData
(
@LocID int
) as
select YourColumns
from YourTable
where Location = @LocID
_______________________________________________________________
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 6, 2014 at 1:02 pm
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?
May 6, 2014 at 1:22 pm
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.
_______________________________________________________________
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 7, 2014 at 3:00 am
If you want to keep a query in Access, use a pass-through query:
Sub RefreshQuery(Byval Value As String)
' Name of the query in the Access database.
' Replace QueryName by the actual name of the query.
'
Const c_Name As String = "QueryName"
' Name of the stored procedure.
' Replace with the actual name of the SP and its parameter.
'
Const c_SQL As String = "MyProcToGetSomeData @LocID = @P;"
' Connection string to the server.
' Replace by the actual values (see: https://www.connectionstrings.com/sql-server/)
'
Const c_Connect As String = "ODBC;Driver={SQL Server};Server=ServerName;Database=DatabaseName;TrustedConnection=Yes"
Dim qdf As DAO.QueryDef
' Create the query if it does not exist already.
'
If DCount("*", "MSysObjects", "name = '" & c_Name & "'") = 0 Then
SET qdf = CurrentDb.CreateQueryDef("")
With qdf
.Connect = c_Connect
.Name = c_Name
.SQL = c_SQL
End With
End If
Set qdf = CurrentDb.QueryDefs(c_Name)
With qdf
.Connect = c_Connect ' Just in case the connection changed since the last call.
.SQL = Replace(c_SQL , "@P", Value )
End With
Set qdf = Nothing
End Sub
Have a nice day!
May 7, 2014 at 7:36 am
rf44 (5/7/2014)
If you want to keep a query in Access, use a pass-through query:
Sub RefreshQuery(Byval Value As String)' Name of the query in the Access database.
' Replace QueryName by the actual name of the query.
'
Const c_Name As String = "QueryName"
' Name of the stored procedure.
' Replace with the actual name of the SP and its parameter.
'
Const c_SQL As String = "MyProcToGetSomeData @LocID = @P;"
' Connection string to the server.
' Replace by the actual values (see: https://www.connectionstrings.com/sql-server/)
'
Const c_Connect As String = "ODBC;Driver={SQL Server};Server=ServerName;Database=DatabaseName;TrustedConnection=Yes"
Dim qdf As DAO.QueryDef
' Create the query if it does not exist already.
'
If DCount("*", "MSysObjects", "name = '" & c_Name & "'") = 0 Then
SET qdf = CurrentDb.CreateQueryDef("")
With qdf
.Connect = c_Connect
.Name = c_Name
.SQL = c_SQL
End With
End If
Set qdf = CurrentDb.QueryDefs(c_Name)
With qdf
.Connect = c_Connect ' Just in case the connection changed since the last call.
.SQL = Replace(c_SQL , "@P", Value )
End With
Set qdf = Nothing
End Sub
Have a nice day!
Thanks for the info on this. Just to give the background on why I want to do this. I have found that running the queries on Access is fine here (where I have the SQL server), but when the team in Calgary try and run some of the reports (that use queries) there is a noticable lag time. I found that when I tied my Visio floor plans directly to SQL, the data refreshed much faster. So I am looking to do the same with the access queries. I will go over this and give it a try, though much of it is new to me...
Thanks!
May 9, 2014 at 7:52 am
I feel your pain; I did a similar project 9 years ago where I moved an Access 97 database to a SQL 2005 back end, maintaining the Access front end. I used parameterized stored procedures and VBA modules that grabbed the form's selections and passed them through to SQL in a dynamic SQL pass-through. Possibly not the most elegant or desirable solution, but it passed muster with our strict DBAs and the users did not have any problems with it at all.
I wish I could remember more of the details so I could help, but it has been a really long time. Years ago, there was a book about marrying SQL and Access database programming but it was written for the 2000 versions and I don't know if it was ever updated. It was a fantastic resource for me but perhaps a bit out of date for your needs. Some of those "Professional Access Programming" titles will certainly have sections devoted to using SQL Server as your back end. That SQL/Access programming book and O'Reilly's VBA In A Nutshell were lifesavers. So was Google! 😀
Anyway, I just wanted to say good luck; it may seem intimidating now but you will get your arms around it pretty quickly, especially with the knowledge base here. 🙂 Good luck!
May 9, 2014 at 8:01 am
SQL is delicious (5/9/2014)
Years ago, there was a book about marrying SQL and Access database programming but it was written for the 2000 versions and I don't know if it was ever updated. It was a fantastic resource for me but perhaps a bit out of date for your needs
One of the most valueable I ever found is called "Hitchhicker's Guide to Visual Basic & SQL Server", by William R. Vaughn (Microsoft Press ISBN 1-57231-567-9) It's an old book (1997) but one full of info and tricks to marry VB/VBA and SQL Server that are still usable today.
May 9, 2014 at 8:02 am
Have not started to play with doing this, but looking it over, it still seems like the work is being done from access, which is what is causing the slowdown. I was wondering if I had the full query (all offices) done on the SQL server, then used a query on access to filter for just the office number, would that be quicker? That way I would not have to pass anything.
May 9, 2014 at 8:08 am
jdasilva (5/9/2014)
Have not started to play with doing this, but looking it over, it still seems like the work is being done from access, which is what is causing the slowdown. I was wondering if I had the full query (all offices) done on the SQL server, then used a query on access to filter for just the office number, would that be quicker? That way I would not have to pass anything.
Yes; send your requests to SQL. Let it do the heavy lifting.
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.
May 9, 2014 at 8:12 am
is/can your form be based on a single table/sql query?
________________________________________________________________
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 8:26 am
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.
May 9, 2014 at 8:32 am
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.
_______________________________________________________________
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 8:37 am
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:
May 9, 2014 at 9:48 am
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?
Viewing 15 posts - 1 through 15 (of 50 total)
You must be logged in to reply to this topic. Login to reply