• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 212
  • Last Modified:

PT form's WHERE-statement

Hi experts!

Have a Access 2k3 front-end with SQL server 2005.
I have a certain form which is based on a view with a
WHERE-statement in the Form's record source.  
How do I pass the statement to the view? (Forms![frm_cust]![Cust_ID] )

Stored procedures are a bridge too far right now.. I guess.. Mmm.. I'm learning :)
A step-by-step explanation concerning SP might work out...!
0
joop123456
Asked:
joop123456
  • 10
  • 9
  • 3
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
0
 
joop123456Author Commented:
@aneeshattingal

Tnx, look like a good start to learn SP's. I'm going to experiment the coming days with it...

What about my question? Pass form's WHERE-statement to a view (or SP)
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Actually I didn't understand it clearly. If the view is there you can do like this

SELECT * from someView where someColumn=@SomeVariable

0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
joop123456Author Commented:
What I need to accomplish is this:

When printing a report via a form(-button),
the report should only download the records which are on the form.

SELECT qryOrderPRINT.* FROM qryOrderPRINT WHERE (((qryOrderPRINT.OrderID)=[Forms]![frmOrderPRINT]![OrderID]));

qryOrderPRINT is a view, but don't know how to get the WHERE statement passed
to the server...  Right now the reports have a upload-size up to 5-6 Mbyte per report...
Not very convenient/handy with 2 branch-offices...
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Your query looks ok, but i m unsure on how to pass a value from the access front end ...
I hope some one else will help you
0
 
joop123456Author Commented:
I should have been posting it as well in MS Access zone,...?!?  might have been a good idea... thanks any way..!
0
 
joop123456Author Commented:
Posted it as well in the MS Access zone. (FOLLOW-UP)
When giving the right answer there, be sure to leave a message here as well for 500 points extra!

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_22739447.html
0
 
frankyteeCommented:
easiest way to do this is using DAO and ODBC (if you are using ADO let me know and I'll come up with an alternative)
1) if not already, set up an ODBC connection to your SQL Server
2) to pass the where clause,  create a variable in Access say
dim sWhere as string
sWhere = "mySQLField = " & Forms![frm_cust]![Cust_ID] )
if your cust_ID is a a string (text value) then you'll need to wrap it in single quotes as shown below:
sWhere = "mySQLField = '" & Forms![frm_cust]![Cust_ID] )  & "'"
now you have the where clause set in a string variable without reference to access objects which are out of scop wrt SQL Server.
2) now in Access, using DAO create a pass through query to your SQL Server.
the connection strings would be as per your ODBC connection, for eg:
ODBC;DSN=myDSNname;UID=myuserid;SERVER=mySQLServer;
3) the sql for this query would be something like:
"select * from my SQLview WHERE " & sWhere
naturally make sure your sWHere variable is within scope of the code where you create your DAO query (within the same procedure etc)
save this query with a name that makes sense to you, for eg
qSQLmyView etc
4) if you double click on this pass thru query you will get the filtered results
5) if you want to "download" the results from this query to internally within Access, create a 2nd query "make" table query and with an sql like:
select * into mytemporaryAccessTable from qSQLmyView
where qSQLmyView is the query defined in 3) above
before you run this query make sure you delete any existing table with the same name.
Performance wise this is the quickest way to get the data into your Access db.
All of the above can be coded and automated using DAO and VBA in Access. If this is what you want let me know and i'll post some code.

if you use ADO instead, you would have to loop through record by record to get the data into your Access db.





0
 
joop123456Author Commented:
This seems to be what I need...!! Looking forward to see some code...!

DAO or ADO, don't know which is best. Both work with Access 2k3 right?!? Anyway the clients make use of file-DSN's ODBC (So all tables and views in it as well, don't know if this has something to do with it)

If there aren't real big differences when it comes to the functionality on the end, take the easiest one to implement. Have no plans to build it out later on..   (that was one of the advantages of ADO, if I'm right... read some quick comparisons..)

Tnx
0
 
joop123456Author Commented:
@frankytee

Think DAO is the way. It seems most convenient...
0
 
frankyteeCommented:
it's the weekend here already in sydney so I'll post some code (DAO) on monday.  it will work for views and stored procs.
Sadly DAO is sort of being phased out by Microsoft cause they've been pushing ADO but in an Access environment (including Access front end to SQL Server back end) you can do a lot more with DAO and it runs quicker in terms of getting data into the front end.
0
 
joop123456Author Commented:
Allright.. have a good weekend!
0
 
frankyteeCommented:
copy and paste this function and change the parameters accordingly, and then call this function from your form.
be sure to backup your Access db first!!

Function fnQuerySQLServer() As Boolean
    'inside a code module, check tools->References and ensure that
    'microsoft dao 3.6 Object library is checked
    'if not you have to register that dll library
    'the file is usually found under C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll
    '-------------------------------------------------------------------------------------------------
    'backup your access database before running this function!!!!
    'replace tablenames, query names, sql strings accordingly
    '-------------------------------------------------------------------------------------------------

    Dim d As DAO.Database, qODBC As DAO.QueryDef
    Dim sql As String, sqODBC As String, sConnectDAO As String
    Dim sWhere As String
   
    Set d = CurrentDb
       
    DoCmd.SetWarnings False
    DoCmd.Hourglass True
   
    'whatever query you want to name it as
    sqODBC = "qODBC_mySQLServer"
   
    'warning: below removes whatever temp query and table you want to store your results
    On Error Resume Next
    DoCmd.DeleteObject acQuery, sqODBC
    DoCmd.DeleteObject acTable, "myTempAccessTableThatStoresResultSetFromSQLServer"
   
    On Error GoTo errH
   
    '--------------------------------------------------------------------------------------------------------------------
    'defining the pass thru query
    '--------------------------------------------------------------------------------------------------------------------
    'if you need to get your criteria from a form control etc it would be something like
    sWhere = " WHERE CustID = " & Forms![frm_cust]![Cust_ID]
   
    sql = "select * from whateverSQLServerView " & sWhere
   
    '--------------------------------------------------------------------------------------------------------------------
    'if it is a stored proc, an eg of a stored proc with two parameters , 1st numeric, 2nd a string parameter would be:
    'sql = "exec whateverStoredProc 100, 'whateverStringParameter'"
    '--------------------------------------------------------------------------------------------------------------------
    Set qODBC = d.CreateQueryDef(sqODBC, sql)
   
    'note the APP argument may not be required, play around with the connect string to see what works
    sConnectDAO = "ODBC;DSN=whateverDSN;UID=whateverUserID;APP=Microsoft Office 2003;DATABASE=whateverdatabase"
    qODBC.Connect = "ODBC; " & sConnectDAO
    '--------------------------------------------------------------------------------------------------------------------
    'if your stored proc does not return records (for eg if it inserts/updates a table etc then below should be set to false
    qODBC.ReturnsRecords = True
    '--------------------------------------------------------------------------------------------------------------------
    qODBC.Close
   
    '--------------------------------------------------------------------------------------------------------------------
    'now define another sql statement to execute your pass thru query
    '--------------------------------------------------------------------------------------------------------------------
    sql = "select * into myTempAccessTableThatStoresResultSetFromSQLServer from " & sqODBC
    d.Execute sql

    Set d = Nothing
    Set q = Nothing
    DoCmd.SetWarnings True
    DoCmd.Hourglass False

End Function
0
 
frankyteeCommented:
forgot to put the error handler errH so i've added it below.

Function fnQuerySQLServer() As Boolean
    'inside a code module, check tools->References and ensure that
    'microsoft dao 3.6 Object library is checked
    'if not you have to register that dll library
    'the file is usually found under C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll
    '-------------------------------------------------------------------------------------------------
    'backup your access database before running this function!!!!
    'replace tablenames, query names, sql strings accordingly
    '-------------------------------------------------------------------------------------------------

    Dim d As DAO.Database, qODBC As DAO.QueryDef
    Dim sql As String, sqODBC As String, sConnectDAO As String
    Dim sWhere As String
   
    Set d = CurrentDb
       
    DoCmd.SetWarnings False
    DoCmd.Hourglass True
   
    'whatever query you want to name it as
    sqODBC = "qODBC_mySQLServer"
   
    'warning: below removes whatever temp query and table you want to store your results
    On Error Resume Next
    DoCmd.DeleteObject acQuery, sqODBC
    DoCmd.DeleteObject acTable, "myTempAccessTableThatStoresResultSetFromSQLServer"
   
    On Error GoTo errH
   
    '--------------------------------------------------------------------------------------------------------------------
    'defining the pass thru query
    '--------------------------------------------------------------------------------------------------------------------
    'if you need to get your criteria from a form control etc it would be something like
    sWhere = " WHERE CustID = " & Forms![frm_cust]![Cust_ID]
   
    sql = "select * from whateverSQLServerView " & sWhere
   
    '--------------------------------------------------------------------------------------------------------------------
    'if it is a stored proc, an eg of a stored proc with two parameters , 1st numeric, 2nd a string parameter would be:
    'sql = "exec whateverStoredProc 100, 'whateverStringParameter'"
    '--------------------------------------------------------------------------------------------------------------------
    Set qODBC = d.CreateQueryDef(sqODBC, sql)
   
    'note the APP argument may not be required, play around with the connect string to see what works
    sConnectDAO = "ODBC;DSN=whateverDSN;UID=whateverUserID;APP=Microsoft Office 2003;DATABASE=whateverdatabase"
    qODBC.Connect = "ODBC; " & sConnectDAO
    '--------------------------------------------------------------------------------------------------------------------
    'if your stored proc does not return records (for eg if it inserts/updates a table etc then below should be set to false
    qODBC.ReturnsRecords = True
    '--------------------------------------------------------------------------------------------------------------------
    qODBC.Close
   
    '--------------------------------------------------------------------------------------------------------------------
    'now define another sql statemetn to execute your pass thru query
    '--------------------------------------------------------------------------------------------------------------------
    sql = "select * into myTempAccessTableThatStoresResultSetFromSQLServer from " & sqODBC
    d.Execute sql
   
comExit:
    Set d = Nothing
    Set q = Nothing
    DoCmd.SetWarnings True
    DoCmd.Hourglass False
    Exit Function
   
errH:
    Beep
    MsgBox Err & " " & Err.Description
    Resume comExit
   

End Function

0
 
frankyteeCommented:
i found more typos above

qODBC.Connect = "ODBC; " & sConnectDAO
should be
qODBC.Connect = sConnectDAO

and
Set q = Nothing
should be
Set qODBC = Nothing
0
 
frankyteeCommented:
joop123456,
have you tried this yet?
0
 
joop123456Author Commented:
Hi Frankytee!

Have been away a lot in the past 3 days. Still holiday here.. That means that my agenda is 100% under my control these days...  ;-)
Anyway, I gave the code a thought the last days.. And today I'm going to see how far I can get with it.. At first sight it seemed not the easiest thing I did with Access... But we'll see today!
Keep you posted..  

(BTW, are you interested in consultancy work as well? have some partial Access projects right now concerning Access/MSSQL/XML and some are concerning programming crosstab-forms with dynamic columns etc...)
0
 
joop123456Author Commented:
Hi Frankytee!

I guess I was - and still am - struggling with how to call this function properly..

My idea earlier was, a main-form which has a form-button, with the function in it,
pointing to a second form etc.

But the records are read only, right?!?

So it is best to point from a formbutton directly to the report (with temptable as report's
recordsource) instead. This situation will cover 99% of all my large download-size
"Filtered-based-on-formfield" reports for now.

The code underneath is the code which I use for nearly all my reports, based on form-field.
Can I put the to be called function fnQuerySQLServer somewhere on top in this code?

Private Sub Command7_Click()
On Error GoTo Err_Print_Invoice_Click

    Dim strDocNaam As String

    strDocNaam = "rpt_PrintInvoice"
    DoCmd.OpenReport strDocNaam, acPreview, "qryPrintInvoice_filtered_on_form_field"
    Exit Sub

Exit_Print_Invoice_Click:
    Exit Sub
 
Err_Print_Invoice_Click:
    Const conErrDoCmdgeannuleerd = 2501
    If (Err = conErrDoCmdgeannuleerd) Then
        Resume Exit_Print_Invoice_Click
    Else
        MsgBox Err.Description
        Resume Exit_Print_Invoice_Click
    End If
       
End Sub


BTW, from today and on, holiday is over, so I'll react asap on your comment(s)
as soon they'll get in my mailbox..!
0
 
frankyteeCommented:
re:
1) But the records are read only, right?!?
i take you mean the result set from your stored proc. if the stored proc returns a select statement then it would most likely be read only. but from your comments it looks like you are using the stored proc to drive a report so you would only require it as read only anyway.
when i exec sp from access i generally store the results locally in the access table that way if required i can manipulate the data (for reporting purposes) rather than base the report on a pass thru query. if you directly link the report to the sp you have to keep exec the sp every time you test the report for formatting, fine tuning etc
so if it was me i would run the report like
DoCmd.OpenReport strDocNaam, acPreview, "whatevertemptablestoringSQLstoredprocdata"

2) simply run the function before you open the report

Dim strDocNaam As String
call fnQuerySQLServer

strDocNaam = "rpt_PrintInvoice"
DoCmd.OpenReport strDocNaam, acPreview, "qryPrintInvoice_filtered_on_form_field"
etc

i would execute the function in isolation first to make sure it works as intended before integrating with your report

3) re: are you interested in consultancy work as well?

i'm no authority on xml but i've worked on enough projects to know that its caused a lot of grief for those  trying to make it work as officially intended.
for access/sql server you can contact me at *** nonononono *** @yahoo.com and we can discuss further. i'm from sydney australia.


Admin Edit - no email addresses in postings, please.

Vee_Mod
0
 
joop123456Author Commented:
Sorry Frankytee...  you are most helpfull....!! but too many things were going on in our company... gave me headache for a couple of weeks... everything is more or less solved now.
The subject is still actual though... coming I'll start up this issue in our db again...  I can send you details in the coming weeks on this for consultancy work. (If you're still interested anyway.. can imagine that you might be less interested on this moment than before....)
0
 
frankyteeCommented:
oh, you're back.
i can't help you much on the xml and i only have sql 2000 not 2005. so if thats still feasible then email me and we can discuss further re consultancy etc
0
 
frankyteeCommented:
also if you email me put something like "ExpExchange Joop" in the subject otherwise i'll think its spam.
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 10
  • 9
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now