Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Moving Queries for access to SQL Expand / Collapse
Author
Message
Posted Tuesday, May 6, 2014 12:02 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 9:01 AM
Points: 26, Visits: 53
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?
Post #1568123
Posted Tuesday, May 6, 2014 12:10 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:59 PM
Points: 13,007, Visits: 12,426
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1568125
Posted Tuesday, May 6, 2014 1:02 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 9:01 AM
Points: 26, Visits: 53
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?
Post #1568146
Posted Tuesday, May 6, 2014 1:22 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:59 PM
Points: 13,007, Visits: 12,426
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1568154
Posted Wednesday, May 7, 2014 3:00 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 4:36 AM
Points: 83, Visits: 1,030
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!
Post #1568333
Posted Wednesday, May 7, 2014 7:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 9:01 AM
Points: 26, Visits: 53
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!
Post #1568469
Posted Friday, May 9, 2014 7:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 10:44 AM
Points: 48, Visits: 157
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!
Post #1569277
Posted Friday, May 9, 2014 8:01 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 4:36 AM
Points: 83, Visits: 1,030
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.
Post #1569287
Posted Friday, May 9, 2014 8:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 9:01 AM
Points: 26, Visits: 53
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.
Post #1569289
Posted Friday, May 9, 2014 8:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 10:44 AM
Points: 48, Visits: 157
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.

http://support.microsoft.com/kb/286222

Post #1569292
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse