My problem is I need to know if the execution of the SQL statement was successful or not. I use the RecordsAffected method, but it always returns 0, no matter what.
I have a date field with dates stored as MM/DD/YY, ie.10/13/09 and I want to use an UPDATE statement to change the year from 09 to 2009 but keep the 10/13/. The issue I'm encountering is when I use a wildcard in the SET portion it takes that as literal and updates with the wildcard. ie. Set Date = '*2009' WHERE Date LIKE '*09' updates the column with *2009 as the entry and not 10/13/2009. How can I update my field so I end up with 10/13/2009, not *2009?
i need to append data that is currently in a single MS Access table into multiple MYSQL tables that have primary keys and auto_ids etc. Therefore I need to run the queries in order so that I can use the new auto_ids correctly, see below:
Is it possible to have a value in an excel 2010 cell be used to update an access 2010 db?
For example, if in a spreadsheet "test" cell A2=3 then in a db "test" a column "value" is 3. However, if the value in the spreadsheet changes so does the db.
I'm having problems with quotation marks in a sql statement. The string is an array separated by a semicolon.
120/80;70;5'6";125
this string represents patient vitals. I'm using the string to update a record. But I get hung up with the quotation mark.
I've tried: 120/80;70;5''6'"';125 which is a enclosing the quotation mark with apostrophies, but this does not seem to work. The sql still gets hung up. My sql statment looks something like:
original string: 120/80;70;5'6";125
strPreOpVits = "120/80;70;5''6'"';125"
mysql = "UPDATE mytable SET PreOpVits = '" & strPreOpVits & "' " & _ "WHERE nID = " & myRecID
docmd.runsql mysql
I've narrowed it down to the quotation marks and I'm unsure how to handle these. I get a runtime 3075 - Syntax Error.
Here is the code that I use to convert the original string
Public Function FixQuotesInSql(strToFix As String) Dim lgth, y As Long Dim strTemp, char2Add As Variant 'This routine fixes the use of apostrophe and quotation marks in an SQL sequence 'If the apostrophe is at the beginning or end of the string it replaces with 3 x "'" or "'''" 'If in the middle of the string then replaces with 2 x "'" or "''"
I was wondering if someone could help? I am using Access 2002 and I am struggling to find out out how you can insert/update/delete records through a form using the design view. Is this possible or do you need to do this another way?
Could some one point me in the direction of a comprehensive tutorial or outline some instructions for what I need to do?
I need to create a form that inserts people's details into a table
When user types in a surname as a parameter query, up pops the form with the details of the person stored in the database, and the user can update the details through the form and the details are saved to the table they came from.
I have an asp.net application where the user can add a new client name and password to a database, but I want to do a check to make sure that client name is not already in there. I was working with and INSERT INTO statement this here:
INSERT INTO [clients] ([clientName], [passWord]) VALUES (@clientName, @passWord) WHERE NOT EXISTS (SELECT [clients].[clientName] FROM [clients] WHERE [clients].[clientName] = clientName)
But I guess you can't use a WHERE clause with an INSERT statement.. So I am trying to use an UPDATE statement. This here:
UPDATE clients SET clientName = @clientName, passWord = @passWord WHERE NOT EXISTS (SELECT [clients].[clientName] FROM [clients] WHERE [clients].[clientName] = @test)
When I run it in access, it doesnt add a new row if I try to add a client name that is already in there, but if I try to add one that isn't it tells me it's going to UPDATE 13 rows which would be all the rows in there. Anybody have any ideas how I can do this?
I have a database to track temporary decertification's. I have the expiration and max dates calculated out from the original dates at the top of each box. The temp expiration date is calculated by adding 267 days from the first date . When we enter an extension, the new expiration date is 30 days from the extension date. My question is, how can I make the expiration date update when a new extension is put in.
For ex.
Temp Decert Date: 05 Dec 2014 Temp Decert Extens 1: Temp Decert Extens 2: Temp Decert Extens 3: Temp Experation Date: 31 Aug 2015 Max Temp Date: 04 Dec 2015 how can I make the expiration date update to go 30 days from what is in the extens field 1, 2, and 3 (respectively) instead of 267 days from the original date? So I want it to look like this after updating a field Temp Decert Date: 05 Dec 2014 Temp Decert Extens 1: 30 Aug 2015 Temp Decert Extens 2: Temp Decert Extens 3: Temp Experation Date: 29 Sep 2015 Max Temp Date: 04 Dec 2015
I have an Access crosstab query that I have exported to an Excel Spreadsheet. I have the spreadsheet formatted using conditional formatting and I'd rather not have to reset it every morning. It's a single spreadsheet (the columns/rows will not deviate greatly day to day) and should be very simple, but I'm not getting it for some reason.
So if I have "Test.accdb" and it contains "qryX" as my crosstab and "Sheet1.xls" is my formatted Excel spreadsheet, how do I code for the latest "QryX" to go in and replace the old "QryX" data in "Sheet1.xls" ?
I have a form with two tables referenced. I am using the form only to update one of the tables. I am using the other table to pull a reference field. When I add my second table using the query builder, it makes it to where I can't edit/add in my form. I assume its because of the SQL insert statement, but I don't see this statement and can't find where it is to edit it. How to have the form only update one of the tables, while just using the second table as a reference for a field?
We are using MS Access as the backend to our application which has been written in delphi and have run into a problem that we have not been able to solve. Hoping someone has run into this before or any suggestions are much appreciated.
The problem:
MS Access runs slowly for client PC's after a update or insert.
- I am using ADO to connect to the Access database, which is using the OLEDB for ODBC Provider. - The application I have sends queries (both select and update) direct to the database (ie client datasets are used). - When only select queries are sent to the DB the response time is fine. - When an update or insert query is sent to the DB the response time of the PC it is run on is fine. - When an update or insert query is sent to the DB the response time of any other client PCs running the application take about 5 to 6 times longer to run queries than before the updateinsert query was done. This is the issue that I am having. - Any client PC's that display this slower response time, can have their response time returned to normal by closing down the application and restarting it. - No more than 3 PC's connected at one time to the DB. - Maximum database size of 150MB. - Problem occurs on various network setups, including domain and workgroup. - Problem only surfaces for users at times well after any application updates have been applied (ie several weeks after, and then once the problem starts it continues). - It does not occur for all user sites.
I have tried and thoroughly tested the following to no avail... - Applied all the latest microsoft updates - Closing and re-opening the ADO connection after updatesinserts - Changed the ADO provider to Jet 4 - Saving the DB in Access 2000 or 2002 format - Set the Default record locking to 'No Locks' and 'All records' and 'Edited record' - Used 'Open databases using record-level locking' selected and unselected - Many application techniques (using delphi) to work around the issue. Many of which have indeed improved general response times, but have not resolved this particular issue.
The only thing I have tried that has resolved the issue is... - Upsizing the database to SQL Server (Unfortunately this option is not a viable one for us at this stage, so I need to find a resolution to it while still using the Access DB).
I have two queries, one base upon the other. I would like to combine them (If Possible) into one query so I can embed them into a form or report. I have tried without success at finding the answer on the forum as well as searching the web.
The table lists employee numbers and dates they worked. I need a count of how many employees worked each year, based upon the paycheck date, not the actual date worked. Pay check dates are two weeks apart. An extreme example, is the first pay check date of 2010 was on 1/1/2010, but all the days worked were in 2009, this would have to be included in 2010 not 2009(See the query for further date calculation understanding). Anyway, the date calculations are not the issue here.
I only have one table, so if I am not mistaken, I can't use the WHERE (SELECT... JOIN) feature. I also was unsuccessful at using SQL DISTINCT.
I am running ACCESS 2010 Tables are ACCESS 2007.
OS is Windows 7 Ultimate.
I have included a same database with the queries. qryEmployeesAnually2 is the results I am trying to achieve.
I've set up a piece of VBA code to loop through and email various recipients data, in the form of records within the email body. This works fine, but occasionally produces an error whereby the email output body lines are duplicated spuriously. This only seems to happen when running a large number of email loops.
The data source for the email body content is a table, which for each 'loop' is refreshed with new data by 1) deleting records from that table, and then 2) appending data to the table.
The portion of code for the loop & email:
Do 'Build outputbodytext DoCmd.OpenQuery ("EmailQ2") DoCmd.OpenQuery ("EmailQ1") 'set up email Set rec2 = CurrentDb.OpenRecordset("outputbodytext") strSendTo = rec2("Email")
I have an Access 2010 database connected to several SharePoint lists. I'd like to be able to print a report (based on a query) and add that report as a .pdf attachment to the record I was looking at when I generated the report.
At the moment I can save the report as a .pdf to a local file store, then upload it as an attachment, but is there any way to skip that step and add it to the correct record automatically?
I would like a user to specify the total number of records they need from a query result. The result must be random, therefore cannot filter on any field or data in the table.
On a form, the user enters the total # of records they need and selects a button that executes this:
DoCmd.RunSQL.SELECT DcountRDP.RDP_IND, DcountRDP.GRP_ST_CD, DcountRDP.AUDIT_STATUS, DcountRDP.AUDIT_TYPE, DcountRDP.AUD_ID, DcountRDP.ACURT_IND, DcountRDP.AUD_DT FROM DcountRDP WHERE DcountRDP.MaxRecords = [Forms]![Open_Frm]![IL]
This is not working at all.
How can I make it possible for the user to enter a total needed or be prompted to enter a total and then randomly produce the first top # of records that they specify?
I am trying to get a form to insert info into a access database. I am using dreamweaver 2004, i created the form with 4 text fields and a submit button. Ive added a ODBC connection and dreamweaver can qurery my table.
I added an insert record server behaviour, and i figured dreamweaver does all the work for you but when i saved and uploaded the page to my test server it would not submit and redirect to my chosen page, it only refresh the form.
Has anyone out there done this before and can notice a step that im missing.
Do i need to setup a recordset? because ive tried that as well and no success
Is there a way to merge duplicate/similar Access 2010 records into one record?
I have an Access table with 1,000 duplicate records, although they are similar and not exact duplicates. As you can see below, some records contain information that other records do not. Yet, the primary key is the same for all duplicate records. I want to find a way to merge data from filled cells of duplicate records into empty cells for each duplicate record. I do not want to concatenate the data (i.e. combine last and first name, etc.). I only want to fill empty cells if there is a match for it in a duplicate record. I will delete the newly exact duplicate records later. Short of correcting the records by hand.
Example
Code: LastName FirstName SSN Address Phone Email Doe John 123-45-7891 123 Anywhere St. NULL john(at)gmail.com Doe John 123-45-7891 NULL (123)456-7890 NULL
Desired Result
Code: LastName FirstName SSN Address Phone Email Doe John 123-45-7891 123 Anywhere St. (123)456-7890 john(at)gmail.com Doe John 123-45-7891 123 Anywhere St. (123)456-7890 john(at)gmail.com
I have an Access 2010 database .. and will be adding about 25,000 records a day to it. So it will get pretty big fast .. so 9 million a year. Is this too big for Access ?? Or is it more a issue of file size .. I know that Access 2010 can go up to 2 GB. I am interested in how I can maximize my performance and not have the database.The data is as follows .. One table. The other tables will be small, just this one will increase in size really.
Id .. Autonumber (primary key) When .. datetime Radio .. integer (indexed .. w duplicates) Group .. integer (indexed .. w duplicates) Type .. text 4 TransType .. text 2
I am considering a purge of old records if needed .. like only keeping the most previous 5 million records or some reasonable number. Like .. then I would think I should try to number my records backwards (using long int rather than autonumber and number records backwards on import) .. ie record 1 would be the most recent one and only keep the last X number of them for performance reasons.
