Help! Need An Access Guru UPDATE/INSERT INTO
Mar 11, 2005
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?
View Replies
ADVERTISEMENT
Mar 1, 2007
Does anyone know how I would convert pounds to tons in the criteria of a query? I just need to know what and where I should put the formula to make this work?
View 6 Replies
View Related
Aug 22, 2005
Hi, I've had a db built and my gurus gone missing. Anyone out there willing to help me out on issues I've got.
This is a genuine request
Regards
Saf
View 1 Replies
View Related
Jan 29, 2007
Hi,
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).
Thanks for your help
View 1 Replies
View Related
Jul 10, 2014
I am trying to update and insert records with SQL statements. Below my code:
Select Case FirstGRV
Case "Y"
sql = "UPDATE Tbl_SellingPrices " & _
"SET SellingPrice = " & Me!Text2 & ", SellingPriceDateFrom = date(), SellingPriceDateTo = #" & Me!Text3 & "#" & _
"WHERE SellingPriceStockCode = Forms!Frm_GRV!GRVStockCode and SellingPriceDateFrom =#01-01-1900#;"
[Code] ....
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.
View 14 Replies
View Related
Mar 7, 2005
To ensure that each date field (3) on my form is not entered earlier than the previous one shown, I attempted to setup a before update event based upon what I've found in searching on date verification or date validation in the forum.
Note - I defined BadDate in the OptionExplicit...
Private Sub Date2_BeforeUpdate(Cancel As Integer)
If Me!Date2 < Me!Date1 Then
BadDate = MsgBox("Date2 is earlier than Date1, please correct!", vbOKOnly + vbInformation, "Wrong date selected!")
Date2.SetFocus
End If
End Sub
I am using one calendar control to load all 3 dates on the form (via a variable). I want to also check to ensure that Date #3 is later than Date #2 but... the first check isn't working at all. I suspect the calendar control is the culprit.
Thoughts?
MikeG
View 2 Replies
View Related
Nov 27, 2007
Hello,
My problem is the pop up message that appears when I do the insert and update into my tables. It is annoying and i'd like to take it off...
Is there any way to take that off??
Thanks in advance.
View 4 Replies
View Related
Jan 13, 2005
Hi folks,
I need some help to figure this out..
What I am trying to do is, when I click the "Save" button on the form I also want to save some of the fields into another table.
I want to open the other table and search of the key field (command ?)
If found then
----run update sql statement
else
----run insert statement
end if
close the table
I am looking for the command statements... (i have the insert and update sql statements ready)
Thanks in advance..
binjos
View 11 Replies
View Related
Dec 5, 2005
Humm, been going over this and can't figure where I am going wrong.
Linked SQL server table.
SQL permissions are correct (cause every thing works via QA using pass through security).
Form with a CBO to select Item1, once selected list box populates with Items tied to it.
Select item from list box click button to break the tie between the two.
3 tables, Item1 Table, Item2 table and cross reference table that ties them together. The Break Tie basically (should) remove the entry from the cross ref. table. Form allows me to insert a new tie (same premis as break only allows you to select items not tied currently) and will insert a row into the cross ref. table. But it will not allow me to delete, or as a backup plan I tried to zero out the FK values. Delete says not allowed (bad permissions, table read only, etc.) and Update says must use an updatable query.
NOW the queries I have used are basically DELETE FROM CrossRef Where PK = nnnn OR UPDATE CrossRef SET FK1 = 0, FK2 = 0 WHERE PK = nnnn
I mean it does not get any easier than that.
So what am I missing on this? Why won't it let me delete/update that stupid table?
View 1 Replies
View Related
May 23, 2007
Im trying to do an insert an an update in the same function,but it only allow an upate only if a record exist.
Here is my code:
Sub insert(ByVal UserSelection, ByVal Grand_Prix_ID)
'The date function
'IF the 2 or more days left before the race then
' Do the insert
CompareDates(User)
Dim mysql As String
Dim strConn As String
Dim MUser_ID = Request.QueryString("UserID")
Dim Nickname = Request.QueryString("name")
Dim LastGP_ID As Integer = Grand_Prix_ID - 1
strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =" & Server.MapPath("App_DataFantasyF1.mdb") & ";"
If TeamSelection_ID(Grand_Prix_ID) Then
mysql = "INSERT INTO TeamSelection(Grand_Prix_ID, User_ID, Driver_ID1, Driver_ID2, Driver_ID3, Driver_ID4, Driver_ID5, Driver_ID6)" & "VALUES(@Grand_Prix_ID ,@User_ID, @Driver_ID1, @Driver_ID2, @Driver_ID3, @Driver_ID4, @Driver_ID5, @Driver_ID6)"
'else if already selected before do the update
Else
MsgBox("You have made the selection before and You are about to update", MsgBoxStyle.YesNo)
mysql = "UPDATE TeamSelection SET Grand_Prix_ID = @Grand_Prix_ID, User_ID = @User_ID, Driver_ID1 = @DRIVER_ID1, Driver_ID2 = @DRIVER_ID2, Driver_ID3 = @DRIVER_ID3, Driver_ID4 = @DRIVER_ID4, Driver_ID5 = @DRIVER_ID5, Driver_ID6 = @DRIVER_ID6" & ""
mysql = mysql & " WHERE User_ID =" & MUser_ID
mysql = mysql & " AND Grand_Prix_ID =" & UserSelection(7) & ""
Dim Myconn As New OleDbConnection(strConn)
Dim objComm As New OleDbCommand(mysql, Myconn)
Myconn.Open()
With objComm.Parameters
.Add(New OleDbParameter("@Grand_Prix_ID", UserSelection(7)))
.Add(New OleDbParameter("@User_ID", UserSelection(6)))
.Add(New OleDbParameter("@Driver_ID1", UserSelection(0)))
.Add(New OleDbParameter("@Driver_ID2", UserSelection(1)))
.Add(New OleDbParameter("@Driver_ID3", UserSelection(2)))
.Add(New OleDbParameter("@Driver_ID4", UserSelection(3)))
.Add(New OleDbParameter("@Driver_ID5", UserSelection(4)))
.Add(New OleDbParameter("@Driver_ID6", UserSelection(5)))
End With
objComm.ExecuteNonQuery()
Message.Text = "Your Selection has been successfully recieved"
Myconn.Close()
End If
End Sub
View 3 Replies
View Related
Sep 3, 2007
Hey guys-
I have a 'Master Table' that holds all my imported records. After a few queries and whatnot- I need to break it down into specialized tables. For example- my Sales Associate info goes into one table (all their contact info, employee code, etc)- while the product info goes into another table.
As I import daily orders and whatnot- it also brings in the sales associates info. So, I want to have Access check the existing SalesEmployee Table for any existing records (by their employee code)- and if it doesn't exist, append it into the table. HOWEVER- if they are already showing in the SalesEmployee Table, I want it to check to see if their contact info is the same- if not, update it with the new info I am importing.
How do I go about doing this? Is this an update query all by itself? Or, do I need a more complex if/then statements and whatnot?
Thanks!
View 5 Replies
View Related
Dec 23, 2007
I am running a query which uppends the record to one table from another table. My requirement is that, it should check one field of the destination table table before inserting the record. If that field matches, it should run Update query or else it should run Insert Query. How Can I do that?
Please Reply!
View 1 Replies
View Related
Feb 20, 2008
Is it possible to have an insert and update in the same sql statement using MS Access 2002? I could split it into seperate statements but for code purity I would like to have it in one :cool:
View 2 Replies
View Related
Jul 13, 2005
Queries are run on a webpage:
The queries would be a little bit different – instead of just one operator, it would get all operators for each category and then either create a record in Access (if it didn’t already exist) or update a record (if it already existed). For example, the first query might get
JSMITH 22
KWALTON 33
Since these records don’t exist yet, we’d do an insert for each. If the next query (for a different entry/verify category) got
JSMITH 44
VJONES 50
we would update JSMITH (since already inserted after first query) and insert VJONES. This has to be done since not everyone works in every category. Not sure if you can import different spreadsheets into Access and have it determine automatically for each row whether to do an insert or update (of course, we can do this in code in the web page).
Can you help?
View 1 Replies
View Related
Dec 2, 2007
Hi guys,
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.
Thanks in advance!
View 9 Replies
View Related
Aug 7, 2005
thank you in advance for all your BIG help. :)
I have 3 tables - SMaster, SI_map, ILookup
I need to create a form to Add/Update into SMaster and SI_map tables, which has one-to-many relationship, that's why I use a list box to show the values from SI_map.
in SMaster [ Sid, other fields ]
in SI_map [ primary key, Sid, ILookupid ]
in ILookup [ ILookupid, IName ]
questions:
1. How to get the multi-selected values from the list box?
2. insert these multiple records into SI_map table at the same time with SMaster (if I use the same form to populate all these fields)
3. how to populate the records into form for user to see and update the values?
Any suggestion is appreciated, it is very flexible to change any format such as SMaster and SI_map can be separate forms, as long as it works.
Thank you.
View 3 Replies
View Related
Apr 6, 2006
i have a form, which is based on query from multiple tables.. in this form i want to add a unbound textbox in the detail section.. whenever a user leaves this records, the system should insert a record in another table with values from this record including this field... how to do this?
the record is from a query where as the field is unbound, still we need to insert the values from record and this field at the same time into another table..
example: i have item master and item production table.... from joining these table i get a query which will give me item A and prod qty .. when i use this query as form datasource, the detail section has these two fields.. now i want to have an unbound field budget.. so i have the followig in detail sectin
item A production qty budget qty
when users enter values in budgetqty field and moves to next recod,, the system should insert into another table xyz values (itemA, production qty, budget Qty)
how to do this..which event will be good.
urgent reply is expected.
View 2 Replies
View Related
Feb 1, 2005
Hi. I have two forms that both look at the same table. One form is for inputting data and the second for is for closing the job. I want to create a button that when clicked automatically changes a field on the input form and also fills in the current date on a field displayed on the closeout form. I then want it exit my two forms and take me back to my switchboard. Can anyone out there help?..Thanks.
View 6 Replies
View Related
Jun 5, 2013
I have two tables "TABLEA" and "TEMP"
fields in both tables are
Cust ID (Primary key)
Cust Name
Address
Cheque No
Amount
Location
Zone
I need query when i click on command button on form
if "Cust ID" which is primary key in "TEMP" Table match with "Cust ID" from "TABLEA"
It will update the record in "TABLEA" if not then append the record
View 1 Replies
View Related
Dec 14, 2012
The following SQL event will not update the CustomerData table if the FrmContact field on the form is left blank. I have this code in the OnLostFocus Event. Is it possible to have this command update the fields that do have data entered in them even though the FrmContact field is blank?
DoCmd.RunSQL "INSERT INTO CustomerData (CustomerName, CustomerAddress, CustomerCityStateZip, CustomerPhone, CustomerContact, LockRecs) " & " VALUES (""" & Forms!frmflcdeliver.Controls!FrmCompany & """ , """ & Forms!frmflcdeliver.Controls!FrmAddress & """, """ & Forms!frmflcdeliver.Controls!FrmCityStateZip & """, """ & Forms!frmflcdeliver.Controls!FrmPhone & """, """ & Forms!frmflcdeliver.Controls!FrmContact & """, """ & Forms!frmflcdeliver.Controls!FrmLocked & """)"
View 2 Replies
View Related
Mar 17, 2006
Before attempting to create a VBA User Defined Function that will accept arguments for creating UPDATE/INSERT INTO SQL statements, I thought I would check to see if some already exist. It seems like a very tough task to tackle. I'd like it to determine the data type of the the values being placed into the specified fields and subsequently provide the appropriate syntax. (i.e. '" & mString & "', #" & mDate & "#, etc.) Do any exist?
I'm working with MS Access 2003.
Thanks,
Steve G.
:confused:
View 1 Replies
View Related
Mar 17, 2006
Before attempting to create a VBA User Defined Function that will accept arguments for creating UPDATE/INSERT INTO SQL statements, I thought I would check to see if some already exist. It seems like a very tough task to tackle. I'd like it to determine the data type of the the values being placed into the specified fields and subsequently provide the appropriate syntax. (i.e. '" & mString & "', #" & mDate & "#, etc.) Do any exist?
I'm working with MS Access 2003.
Thanks,
Steve G.
View 1 Replies
View Related
Nov 8, 2004
Hi, I was wondering why the following code would give me an invalid SQL statement message:
Dim Rs As New ADODB.Recordset
Rs.Open "Manzanero # 450", CurrentProject.Connection, adOpenKeyset, adLockBatchOptimistic
The error message is:
"Invalid SQL Statement; expected 'Delete', 'Insert', 'Procedure', 'Select', or 'Update"
I'm just trying to open up the table "Manzanero # 450" so that I might add to its contents. I have Microsoft Active X Data Objects 2.6 library included as well. I find it strange since this is basically a line for line copy of a sample I found in a MS Access book. Please help. Thanks! =)
G
View 2 Replies
View Related
Sep 19, 2004
I try to do an INSERT statement throught my ASP to add records to my Access DB. However I get an error when I execute this. The code in ASP is -
Code: strSQL = "INSERT INTO tbl_psm (LocID, Week, Month, Year, Rating, Remark, Action, SubmittedDateTime) VALUES ('" &cint(intLocid)& "', '" &cint(sWeek)& "', '" &cint(sMonth)& "', '" &cint(sYear)& "', '" &cint(sRating)& "', '" &sRemark& "', '" &sAction& "', '" &formatdatetime(date,vbshortdate)& "');" objConn.Execute strSQL
I get the error page -
Microsoft JET Database Engineerror '80040e14'
Syntax error in INSERT INTO statement. /PeriodicWorkScheduleRatingSubmit.asp, line 65
I tried doing a response.write onto the ASP (to get the actual SQL), got the query, copy-pasted it and ran it on MS Access - it worked fine there. The query is -
Code: INSERT INTO tbl_psm (LocID, Week, Month, Year, Rating, Remark, Action, SubmittedDateTime) VALUES ('50', '2', '9', '2004', '3', 'asd', 'asdasd', '9/20/2004');
I can't understand if it works in Access, who not work through ASP?? Is there somethings wrong somewhere? I cannot see it..
Please help..
View 2 Replies
View Related
Nov 28, 2006
Hi,
I'm using access tables to store my data. to retrive data I use jet odbc engine in c++.
I want to insert a large amuont of records (about 20 mega records) to my database in the fasts way i can. if i use sql syntax (insert into table ()....) it's takes for ages (about 500 records per second).
if i'm writing a csv file and then use import (via access) it's much faster but here I have two problems
1.I dont know how to use the access import tool from c++.
2.I dont think I can distributie the access import tool with my product.
so my questions are :
1. Does any know any tool that insert records in an optimize way?
2. How can I use the access import tool in c++?
3. Can i use the jet engine to import csv files?
thank ishay
View 2 Replies
View Related
Dec 13, 2004
HI all-
I have a list of INSERT statements (SQL DML) and I wish to insert this data into an MS Access 2000 table
Unfortunately I cannot find a mechanism to accomplish this in Access. Does one exist? How can I import this data into access in a SQL format?
Thanks in advance!
View 6 Replies
View Related
