SQL Or Stored QUERY
Feb 3, 2008What are the benefits of each a stored query or sql? I suspect that the stored query would run quicker>
View Replies
ADVERTISEMENT
Queries :: Field Cannot Be Stored In Query
Apr 13, 2014I am using a predefined query to define the sql statement that will serve as recordsource to a form. Syntax is fine. The predefined query does not have the primary key of the table. The selection in the recordsource is done using this key. So I add this field to the design of the query. I save the query. It says the query is saved. But it is never saved. Run the query again it is not changed.
View 5 Replies View RelatedReferencing CDbl Value In Stored Query From A New Query
Aug 13, 2007Hello - am hoping someone can help with the below query.
I have a database with two linked tables. Both of them contain a 10-digit ID number. One table (Table 1) has this ID stored as text; the second (Table 2) as a double.
I need to find records in Table 2 that are not in Table 1. I first created a stored query that converted the Table 1 ID into a double using the function CDbl([ID]). I then created a new query left-joining the stored query on Table 2 against the ID. The query displays the stored query's converted ID.
I run the new query. In the results - if there's a match, the ID successfully appears. But where there's no match - instead of a Null or an empty string, "#Error" appears. Can anyone advise why this is, and how I can work around it?
Thanks!
Pass A Parameter When Calling A Stored Query... Without The Prompt.
Jun 29, 2005I this is a simplifed version a saved query called "qryTest"
PARAMETERS [gUserID] Long;
SELECT [Id],'Complaint' AS IncidentType FROM tblComplaints WHERE tblComplaints.[Id] IN (Select Incident_ID from tblNotification where Dept_ID IN (Select Dept_ID from tblUserDepts Where User_Id = [gUserID] ) AND Incident_Type = 'Complaint');
UNION ALL SELECT [Id],'Fall' AS IncidentType, FROM tblFalls tblFalls.[Id] IN (Select Incident_ID from tblNotification where Dept_ID IN (Select Dept_ID from tblUserDepts Where User_Id = [gUserID] ) AND Incident_Type = 'Fall');
how to i call the result from code or from the query manager and include the parameter so there is no prompt that comes up?
like
Me.ListNew.RowSource = "qryTest " & gUserID &";"
or
Select * from qryTest , 31 - where 31 is the value fed to the parameter
??? does anyone know? I know it can be called in APS like this:
Dim Conn
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open strConn ' strconn is my connection string
set rsP=createobject("adodb.recordset")
conn.qryTest 31
.....
I can't believe I can't do it from the query string!
Heeeellpppp!
Reports :: Stored Procedure Parameter Query - SQL Server
Jan 20, 2015I have created a stored procedure parameter query and using access created a report that runs the procedure and creates a report based on a parameter entered:
1) I wanted to know if I can specify a default paramter so if I do not input it returns all records?
2) Can I create a stored procedure so it asks for month, for example if I had a createdDate field of data type datetime and wanted to return records for a specific month?
Queries :: Update Date Field Stored In Table - Query Not Working
Dec 23, 2013Why is this update query not working, I'm trying to update a date field stored in a table.
The new date is passed from a txtbox on a form to the update query!!!
SQL code
UPDATE TblDietPlantemp SET TblDietPlantemp.MealDate = [Forms]![FrmSwitchBoard]![txtCusDate];
Dynamic Query Based On "formulas" Stored In Table
Feb 13, 2008Hi,
I have a table that looks something like this:
Company Product Sales
1 A 5
1 B 2
1 C 7
2 A 8
2 B 1
2 C 2
I want a query to produce a table showing Products A though D which is
the sum of Products A and B. Result:
Company Product Sales
1 A 5
1 B 2
1 C 7
1 D 7
2 A 8
2 B 1
2 C 2
2 D 9
I have many rows and many summations and the current union query and
supporting queries are pretty messy and not easily changeable. What
I'd like to do is have a third table which would hold the summed
product names and the formula used to get to it (Product D, A + B) and
have a query that can be dynamically based off of that table. Do I
need to use VBA? Does anyone have a better idea? Any help or even a
good search term would be appreciated!
Thanks!
Nathan
Stored Procedure
Jun 12, 2007Hi,
I need to write a stored procedure that sits between sql server and MS access. This procedure will make sure that the queries run in access against the data in sql server will not access data for more than two years back. This is so that the queries run against the sql server do not hang up the server for too long.
Thank you.
Stored Calculations
Nov 29, 2004I've read the posts regarding not store calculations. However, I was told by IS to store the mileage IRS deduction as it changed 3 times last year.
Any idea's how to handle this? I need the table to remember how much $$ was paid out in mileage expense. I had set up a field called mexpense and mileage, where =[Mileage]*0.375.
Stored Procedures
Sep 11, 2006Hi,
I am switching from sql server to msaccess and cannot find an equivalent of a stored procedure in access.
The task I want to do is the following.
1. I have a table with many field.s
2. I want to loop through all the fields and replace records which have a value of 0 with NULL.
How could I do this without writing seperate update statements for each of the fields.
Thanks.
Calculating A Stored Value
Jul 1, 2005I apologize that I am not a very good VBA programmer yet but I'm really trying....
I am attempting to calculate a value by adding two fields and storing the result into a third field - - My code only works if both of the fields are not null.
Private Sub PartialDepositAmount_AfterUpdate()
Dim PartialDepositAmount As Currency
Dim FinalDepositAmount As Currency
Dim DepositAmount As Currency
Me.DepositAmount = Me.PartialDepositAmount + Me.FinalDepositAmount
End Sub
If either field is null my result is null but I need to be able to add field one (or field two) to a null and store the result in the totals field.
Any suggestions will be greatly appreciated.
JohnJ
Stored Procedures
May 20, 2005How do you make stored procedures in microsoft access 2003, can you actually do this or is it not part of access?
View 4 Replies View RelatedUsing Stored Procedures In Access
Nov 15, 2007I have an Access mdb that has a bunch of tables linked from a SQL Server. On the SQL Server I have some stored procedures that do inserts into various tables. In the Access db I have some local (lookup) tables, queries, and other things that for various reasons can't/don't need to be stored in the SQL server. I'm using Windows authentication to connect to the SQL server because the security is managed through our domain.
I want to write a form that has unbound text fields that, when I submit the form passes all of those values as parameters to a stored procedure in the SQL server. I have written just such a form in an .adp file, which handles the integrated security nicely.
However, I'd rather do this in a regular .mdb file. I've read up on how I can use an ADO connection to do this, then create a SQL string to EXECUTE the stored proc, grab all the values off the form, and build the SQL.
But the problem with this solution is the authentication - I can't use the SA user and password in this connection string, I'd have to utilize the logged-in user's information.
Is there an easier way to execute the stored procedure from an .mdb? Using the integrated authentication?
Failing that, is there a way that I can create "local" (to the .adp) tables, queries, and so on in an .adp?
Parameters / Stored Procedures
Aug 25, 2006I come from a SQL Server background and am very familiar with store dprocedures and input parameteres.
Is there a way to use a similar method in Access. or example, passing parameters from a button on a form to a query, which can change depending on the input parameter? If so, how ?
Also, how much scope do you have in this for running SQL IF statements and such in the query? It seems that many Access queris can only have one statement at a time.
Set Parameters For Stored Procedure
May 31, 2005I'm running a access 2000 adp front end working from a SQL2000 database. I've got a sproc P_insertsessions i can get this work from a cmd button on a form but i'm having problems setting the paramerters i want it to use controls on the form.
@contractid = contractid
@start_date = startdate
@end_date = enddate
@hours = duration
Can anyone help.
How To Run Instruction Stored In A Memvar??
Mar 10, 2005I'm used to doing the following with Foxpro:
mSetup = "Replace table.desc with alltrim(table2.code)+" ~ "+table2.vno+" ~ "+table2.po+" ~ "+table2.vendor
To run the process I use a macro substitution:
&mSetup
What can I do as an equipvalent with VBA??
Thanks in advance,
Steve
Stored Procedures Disappear
Feb 27, 2008Sometimes when I send a copy of an Access 2003 application to a remote user, a stored procedure or function will disappear. What causes this, and how can I get the entire application over to the users?
Thanks in advance!
Combo Box Values Being Stored In Tables As 1 And 2 Etc..
Feb 12, 2008Hi,
I have combo boxes on a form which are pulling values from a one table and being stored in another table. However they are being stored as '1' or '2' in the other table once selected on the form, as instead of being stored as their literal values ie 'car' or 'van'. Is there any way of making this happen, as it makes reporting a nightmare! Thanks in advance...
Count How Many Lines Stored In A Field.
Jun 26, 2007Is there any way to count how many lines of text are stored in a particular table field? How about counting how many times was the "enter" key pressed?
Thanks
Mafhobb
Null Values In Stored Procedures
Aug 4, 2005Hi,
I am calculating two fields (qtyorder-qtydesp) in a stored procedure.
The second field is from another query (view) which may not have a record relating to the main record (stored procedure).
The problem is the second field is simply left blank but I need it to be 0.
It makes the calculation in turn result in a blank field, i.e. 1-0 should equal 1 but because of the blank field it returns a null value.
I found that the good old Nz function wont work with stored procedures, is there any alternative?
Thanks,
James
Pass List Box Value To SQL Stored Proc
Mar 27, 2008I have and access form that has a list box with dates in it. I need to pick a date and then run a stored procedure in SQL based on that date. Can someone help me with the login to pass on the date?
So if I have listbox1 which is a list of dates from another table
and my procedure is simply
select * from main
where date = *******
How can I pass the list box value to the stored procedure? My procedure works with WHERE date = (SELECT(LEFT(GETDATE(),11))) but I have since learned that todays date will not always be used so I need to pass a value.
Thanks,
Dave
Use Stored Proc As Recordset For Listbox
Mar 29, 2005I'm working on and Access 2003 front end with a SQL Server Back End. I would like to take advantage of the speed of Stored Procs to populate a list box on a form. Here's the code I'm trying to use.
Set cmd = MakeStoredProc("StoredProcName")
Set prm1 = cmd.CreateParameter("ParamName", adInteger, adParamInput, , Me![ID])
cmd.Parameters.Append prm1
rstSource.CursorLocation = adUseClient
rstSource.CursorType = adOpenKeyset
rstSource.LockType = adLockOptimistic
Set rstSource = cmd.Execute
Me![ListBox].Recordset = rstSource
Access doesn't like this. What is wrong. I get the message "Object doesn't support the property or method"
Thanks for your help.
Changing Stored Value Of Option Groups
Aug 8, 2005Sorry if this is an easy one and I just missing it, but I have a form in an Access2003 database with option groups for ratings for 18 individual tasks ie:
Safety: 1. Satisfactory 2. Marginal 3. Unsatisfactory 4. N/A
The value stored in the table is 1,2,3, or 4 for each one, obviously.
How can I change the stored value in the table to "Sat" "Marginal" "Unsat" etc?
Or, if easier, make the report convert the numerical values to names?
SQL Stored Procedure And A Form Recordset
Dec 19, 2005Hi All
Im having some Major issues with this problem, i only hope someone can help me!!
Firstly, let me set the scene. I have 1 Form, And 1 Subform within that form. The form gets the Main Data, the SubForm gets all the Related Data (in this case, its all the Items Of Equipment this client has) and then lists them in the subform. So it could return 1, or it could return 20 records.
What we want from this is to be able to record what the client thought of the piece of equipment, so there is a Option Group on the SubForm, which allows the user to choose 1 of 6 options saying whether it was useful or not and things.
BUT here lies the problem, if the SubForm has returned 20 records, i change the OptionGroup on record one, and every other 19 records become the same. if i change Record 2, record 1 and 3 to 19 are the same.
I want to be able to have a seperate choice for each record returned, but tis not working!!
The subform is populated by calling a SQL SP, i cant set the form to link directly to a view or SP because access doesnt allow me, by saying the recordset is not updatable!
I hope all that makes sense!!
How To Creat Stored Procedure In Ms-Access
Oct 28, 2004Hello,
can we create a stored prodecure in Access 2000 and call it from VB. I've created in SQL Server with ASP but have no idea in Access. Can anyone please tell me how to do it. If so kindly specify some resources from where i can learn more related to this. thank you.
Passing Criteria To A Stored Procedure
Feb 22, 2005Not sure which forum this was under, but I figure Access might be it
I have a stored procedure already written which works fine, if I supply the criteria to it before or at manual execution. I want it to use a field on a form in an Access Data Project as it's criteria (as a form is built off the results of the procedure). I can't find any documentation on how to pass criteria to a stored procedure for use in SELECT WHERE statements.
Can somebody point me in the right direction? Here is my stored procedure:
spGetContact:
Code:CREATE PROCEDURE dbo.[spGetContact](@parHomePhone numeric)AS SELECT dbo.tblContactAddress.AddressLine1, dbo.tblContactAddress.AddressLine2, dbo.tblContactAddress.City, dbo.tblContactAddress.Zip, dbo.tblContactAddress.State, dbo.tblContactEmail.PrimaryEmail, dbo.tblContactEmail.SecondaryEmail, dbo.tblContactPhone.HomePhone, dbo.tblContactPhone.BusPhone, dbo.tblContactPhone.CellPhone, dbo.tblContact.ContactID, dbo.tblContact.FirstName, dbo.tblContact.LastName FROM dbo.tblContact INNER JOINdbo.tblContactAddress ON dbo.tblContact.ContactID = dbo.tblContactAddress.ContactID INNER JOINdbo.tblContactEmail ON dbo.tblContact.ContactID = dbo.tblContactEmail.ContactID INNER JOINdbo.tblContactPhone ON dbo.tblContact.ContactID = dbo.tblContactPhone.ContactIDWHERE (dbo.tblContactPhone.HomePhone = @parHomePhone)GO
When i execute this manually I get the dialog prompt to enter the value for @parHomePhone, which is what I want to automatically pull from txtPhone on the frmSearch form.