joop123456
asked on
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...!
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]
Aneesh
http://sequelserver.blogspot.com/2007/07/2007071505an-introduction-to-stored.html
ASKER
@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)
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)
Actually I didn't understand it clearly. If the view is there you can do like this
SELECT * from someView where someColumn=@SomeVariable
SELECT * from someView where someColumn=@SomeVariable
ASKER
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)=
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...
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)=
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...
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
I hope some one else will help you
ASKER
I should have been posting it as well in MS Access zone,...?!? might have been a good idea... thanks any way..!
ASKER
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!
https://www.experts-exchange.com/questions/22739447/Pass-form's-WHERE-statement.html
When giving the right answer there, be sure to leave a message here as well for 500 points extra!
https://www.experts-exchange.com/questions/22739447/Pass-form's-WHERE-statement.html
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=myu
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.
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=myu
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.
ASKER
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
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
ASKER
@frankytee
Think DAO is the way. It seems most convenient...
Think DAO is the way. It seems most convenient...
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.
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.
ASKER
Allright.. have a good weekend!
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, "myTempAccessTableThatStor
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=
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 myTempAccessTableThatStore
d.Execute sql
Set d = Nothing
Set q = Nothing
DoCmd.SetWarnings True
DoCmd.Hourglass False
End Function
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, "myTempAccessTableThatStor
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=
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 myTempAccessTableThatStore
d.Execute sql
Set d = Nothing
Set q = Nothing
DoCmd.SetWarnings True
DoCmd.Hourglass False
End Function
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, "myTempAccessTableThatStor
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=
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 myTempAccessTableThatStore
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
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, "myTempAccessTableThatStor
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=
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 myTempAccessTableThatStore
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
i found more typos above
qODBC.Connect = "ODBC; " & sConnectDAO
should be
qODBC.Connect = sConnectDAO
and
Set q = Nothing
should be
Set qODBC = Nothing
qODBC.Connect = "ODBC; " & sConnectDAO
should be
qODBC.Connect = sConnectDAO
and
Set q = Nothing
should be
Set qODBC = Nothing
joop123456,
have you tried this yet?
have you tried this yet?
ASKER
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...)
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...)
ASKER
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-formfie
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_
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..!
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-formfie
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_
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..!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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....)
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....)
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
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
also if you email me put something like "ExpExchange Joop" in the subject otherwise i'll think its spam.