I want update a table (in Access 2010) based on the results from a pass-through query that pulls data from an old Fox Pro database.
With the following query I push data into my access table (everything works fine):
Code:
INSERT INTO Bolle ( esercizio, numero_documento, ...) SELECT Bolle_Arca.[esercizio], Bolle_Arca.[numero_documento], Bolle_Arca.[testata_codice], ... FROM Bolle_Arca WHERE Bolle_Arca.[testata_codice] NOT IN (SELECT Bolle.testata_codice FROM Bolle)
When I try to update the same table using the same pass-through query:
Code: UPDATE Bolle INNER JOIN Bolle_Arca ON Bolle.riga_codice = Bolle_Arca.riga_codice SET Bolle.esercizio = Bolle_Arca.esercizio , Bolle.numero_documento = Bolle_Arca.numero_documento, ... WHERE Bolle.spedito = False and Bolle.data_invio_mail_spedito is null and (Bolle.tracking is null or Bolle.tracking = '') and Bolle.testata_codice = Bolle_Arca.testata_codice;
I get this error: "Operation must use an updatable query"
I am trying to run a pass-through query in access with a variable that a user enters in a form. I can't seem to get this to work. Can somebody please help? The variable should be where the 121212 is in the code.
Code
1:FROM doc..eco_summary es 2:INNER JOIN 3:((((mart..DM_Map dmm LEFT JOIN mart..DM_PI dpi ON dmm.Acct_ID = dpi.Acct_ID) 4:INNER JOIN mart..DM_Note dmn ON dmm.Acct_ID = dmn.Acct_ID) 5:INNER JOIN mart..DM_ACCT dma ON dmn.Acct_ID = dma.Acct_ID) 6:LEFT JOIN mart..DM_RE dmr ON dmn.Acct_ID = dmr.Acct_ID) ON es.L_loannum = dmm.Acct_ID 7:INNER JOIN weis..eco_loan_origination elo ON es.L_num = elo.num 8:where es.L_num = 121212
Can anyone help? I have a query that queries against 11 linked views against an SQL Server backend.
The query is running dog slow, so I want to convert it to a pass-through query so that the processing is done server side rather than Access having to drag thousands of records across the network, but don't know where to start.
The problem is that I need to convert the Access SQL to a version of SQL that SQL Server understands.
Is there a tool I can use that does this automatically (keeping my fingers crossed here)? If not then does anyone know where I can find out how to do this?
First I'm an average access user, but don't know much about VB. I've created a subform with a querydef that works fine standalone, but as soon as I move my subform onto my main form the the querydef no longer runs.
I'm probably trying to be too clever here, but I have an update process that uses many queries to update the various tables in the project.
In general, these queries must be run in the correct sequence. So I name them "update 01 chg Field1" "update 02 get Field2-3 from table2" ... "update 15 make summary table".... etc...
In VBA, I then run 'docmd.openquery ...' for each query in the list.
But as the project evolves, I have to change the query sequence, or rename the descriptive part of the name. I then have to go in to the code and change the sequence/correct the names for each.
I had this "Better Idea":
for each qrydef in db.querydefs ' only run if name starts in update if mid$(qrydef.name,1,6)="update" then docmd.openquery grydef.name ' log that the query ran, etc... end if next
This seems to work wonderfully! The qrydef are coming out in alphabetical order, which is what I want, but I can't find anywhere in the docs that says that they HAVE to come out in alphabetical order. Nothing that says they will and nothing that says they may not.
I've tested creating the queries in arbitrary sequence, or renaming the queries, and so far they are always in alphabetical sequence.
SO, is what I am doing safe? Will querydefs always return the queries in alphabetical sequence, or am I likely to have a nasty data destroying missequence at some time in the future?
I have been struggling with modifying a graph that I have made into a counting graph, I have wasted over 4 hours trying to do this modification unsuccessfully.
I am attaching 4 pictures, The Depth_Ranges Table Visual is the query which creates graph 1 you can also see in the additional attached picture of the design view the code that creates this graph 1.
Now I need to add a field in the Design view of that query in order to have a result of the graph 2 that you see in the picture.
Basically, creating counter that will count how many values are in each bin labels that you can see on the picture of graph 2.
I have a access 2010 split database and i need to modify one form on the front end (it is an accde file). However, when i open the Front end, i cannot find the possibility to go into the form design mode.
How do i need to do it? do i need to restart from the unsplit database?
The both the front end and the back end are password protected.
I'm far from being an Access expert, so forgive me if I don't make perfect sense. I have a database where questions and pages are entered on a daily basis. I have a table with three columns: data, pages, and questions. I have a query where I can sumarize the data by Month. (I created a query by using the wizard and I chose to summarize by Month). This returns all of the data, summarized by Month. I want to limit it further by year, so that it would return 12 months of data. I am at a loss! I've tried typing [Type Year] but I get no responses. I was able to create a limiting one where I type in the month and year [Type Month and Year] that works great, but it doesn't translate into the year only.
I have a Form with embedded Subform, everything works well as far as moving through records. Scrolling through records on main table via Form brings up correct records of child table on Subform, so the link between the 2 tables is done properly.
I use this logic to lock/unlock a field in the subform.
Code: Private Sub Form_Current() If IsNull(Me.Parent!Spec2) Then Me!Value2.BorderStyle = Transparent Me!Value2.SpecialEffect = Flat Me!Value2.BackStyle = Transparent
[Code] ...
I confirmed with debug that the logic does set the properties properly. They are updated once and that's that. Any further passes through the logic do not update on the form. They do pass through correctly, changes are just ignored.
It doesn't matter if I use the logic from the Form and refer down, or in the Subform and refer up, the Subform never gets updated. I tried Repaint, Refresh, Requery, nothing works. I cannot count the permutations I've tried.
SELECT clvpertech.clvtech1 AS Technician, Sum(clvpertech.CLVCode) AS CLVs FROM clvpertech GROUP BY clvpertech.clvtech1;
This is returning a sum of all the procedures a technician does for the entire database. This is working.
I need to break this down by day, week , month, and year base off of the datecomp1 or datecomp2 or datecomp3 or datecomp4 fields having a date in them.
I made this query to get the daily results and it works SELECT clvpertech2.clvtech1 AS Technician, Sum(clvpertech2.CLVCode) AS CLVs FROM clvpertech2 WHERE (((clvpertech2.datecomp1)=Date$())) OR (((clvpertech2.datecomp2)=Date$())) OR (((clvpertech2.datecomp3)=Date$())) OR (((clvpertech2.datecomp4)=Date$())) GROUP BY clvpertech2.clvtech1;
How can I add different queries to get the results by breaking down the date field for year, week, and month?
I tried this for the month but I get Invalid Procedure Call when I try to run it???
SELECT clvpertech2.clvtech1 AS Technician, Sum(clvpertech2.CLVCode) AS CLVs FROM clvpertech2 WHERE (((clvpertech2.datecomp1)=DatePart("mmm",[datecomp1]))) OR (((clvpertech2.datecomp2)=DatePart("mmm",[datecomp2]))) OR (((clvpertech2.datecomp3)=DatePart("mmm",[datecomp3]))) OR (((clvpertech2.datecomp4)=DatePart("mmm",[datecomp4]))) GROUP BY clvpertech2.clvtech1;
I am trying to modify the output to only show discontinued items. While the query is in datasheet view, the text is read as "YES" or "NO" under discounted items. So, I tried entering "YES" in criteria while in design view, but keep getting an error message stating, "Data type mismatch in criteria expression".
whenever I run/execute a query in Access it is modifying the first record on the table in which it is calling the data from.For example if the first record might contain the following:
Record A: John Doe, Oct, 2014, Account is Active
And lets say I am running a query to pulling records from Nov 2014.The Month and Year Fields in the above example for Record A gets modified to the query search parameters for Nov.Is there some of of record lock or controls that I need to adjust to prevent this from occurring?
Is it possible to direct the placement/order new of fields when modifying an Ms Access database in code?
I need to modify the schema of an MS Access database via code - but I want to be able to direct the order or placement of the fields within the tables.
For example - if TableOne has 3 fields - Field10, Field20 and Field30.
I would like to be able to add say Field15 between Field10 and Field20 - not just append it to the end of existing fields.
I believe it is possible as you can do it within Ms Access itself. I can use ADO, DAO, ADOX or SQL for that matter - but it seems all of these offer no placement of the field within the table.
Hi, Self learning trying to modify a query fieldname and criteria thru code.
Have a small form with a button making a copy of a query/s (eventually making about 50 copies). Once these have been made, would like to open the query up, which I can do, then modify both the fieldname and the field criteria to suit my needs from parameters set in the form.
I have an existing query, created using the query wizard which works just fine - however, I would like to modify it to return only instances where there are 3 or more records appearing.
Essentially, its an employee history report for a particular action done by those employees, which returns all records between two dates as specified by the user. What I would like to do is only show those employees who have had more than three instances of this action in the given date period.
I am thinking along the lines of DCount? but how it would be phrased in the query?
Good afternoon, I have a form with a subform and in the first txtbox of the subform in the GotFocus event I have a little procedure which checks the txtboxs on the parent to make sure that there is data in all four of the txtboxes. This works great the first time and it pops up a msgbox and it even setsfocus on the txtbox with no data in it, but if I tab into the subform a second time and there still is no data in one of the txtboxs on the parent form, nothing happens, no message and no setting focus on the txtbox with no data in it. Does anyone know of a way to get this procedure to re-set everytime a user tries to enter the subform? Thank you in advance to anyone offering and ideas and suggestions.
I want to be able to pass arguments to an access file on start up. I want to be able to grab this value and then perform an action based on this.
Background: The program will send an email with details for a change request, the recipient will then need to click on a link to accept or reject this change. So I want them to be able to click the link which will trigger the program to save accept or reject.
I don't know if this is thebest place for this, but here goes....
I have an Access FE using MYSQL 4.1 BE. I have several forms and PassThrough queries (using MYSQL syntax not supported or available in Access).
How can I use form field values to use as criteria for these queries?
FOr example, in a pure Access database. I may have a form 'Employees' with a text box 'EmployeeName'. THen have a query....
SELECT * FROM tblEMployees WHERE EmployeeName = [Forms]![Employees]![EmployeeName]
If I were to add this variable to my pass-through queries, it errors. How can I do the same thing, passing my form fileds to an Access Query? I am trying to do this right in the SQL view of the queries, not in VBA or Macros or anything.
I'm trying to create a PTQ and just cant seem to find the source table.
The name of the source table is PRM1_ORG_MTRX3_N
If I write my query like this, I get an error code that states username.PRM1_ORG_MTRX3_N is an undefined name. It adds my userid to the beginning of the table name.
Then I rewrote the query like this, and I got and error code that said PRM1.ORG_MTRX3_N is an undefined name. Does anyone know what else I can try here to get this query going? Thanks
Can you do a pass through Query to a pivot table when your query has parameters? I'm reading like you can't, but nothing has been said concretely yet. When I do it, it gives me an error "Trouble Obtaining Data" when I try and set the layout.
I have created a simple query that is used as the record source of one of my forms. I want to pass the query a different criterion each time the form is opened.
Any suggestion/example on who to do that will be very much appreciated.
I have an Access front-end connected to Oracle tables. When the user opens up the main form to add a new entry, I need to be able to pull the next sequential record number. However, I can't get this to happen, without filling out the entire record, saving, and then it will committ the number.
Can this be done with a pass-through query to the Oracle table, find the next sequential number, and then add it to my record id (ie, 06-1050, with 06 being the year and 1050 the next sequential number). I do have two fields in the Oracle table TXTYear and LNGNumber. This concatenated field is related to three additional tables that have more information. Each of these tables need to cascade a new record.
I have a VERY simple select statement that I am using in a pass-through query: "SELECT * FROM dbo_vReturn;", but I cannot get this to work. Each time I run the query it generates an error: "ODBC call failed - Invalid object name "dbo_vReturn" (#208)".
The dbo_vRteturn is a view in the backend. I can open this table/view from Access using an ODBC connection but when I try to run the pass-through query it falls over!
Does anyone have any idea what is causing this?:confused:
