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)
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?
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") & ";"
'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()
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!
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?
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:
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?
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).
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 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.
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)
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).
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.
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?
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?
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?
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.
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! =)
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 -
I am at work, and I have acquired a database that prints labels. They now want the database to be coded so that after certain labels are printed the database will print a blank label. I have the code figured out as a Do While statement in order to print the blank label. The problem I am having is that I am trying to use the Insert Into command to insert the filepath into the table that adds the blank label.
|DoCmd.RunSQL "INSERT INTO Rod_tmakLabels ( Print, [Order] ) SELECT Yes AS Expr1, 'Rods Labels' AS Expr2"|
If I run the above command, it just adds the text "Rods Labels" at the end of the table. Is there anyway with the INSERT INTO command that I can insert the new label between the 2nd and 3rd row and add another row? Or is the command designed only to add a new row to the end? I haven't had any luck searching for this yet.
Hi all! I was wondering if you guys can help me out with this one:
I would like to have data auto-inserted from a particular colum in table 'x' in to a defined column in table 'y'. similary, I'd like to achieve auto-updation of this data as well as deletion. Can anyone please provide some guidelines?
A little more detail :
I have a column "Client ID" in the table client_personal_info, the rows of which I'd like to have updated in the "Client ID" column in the client_business_detail table.
So if say, ClientID_1 is a row that has been added by the user, I'd like to have it auto-inserted in the client ID column in the client_business_detail table
I need to devise a query to append data for a particular Department_ID from TBL_NEWDATA to TBL_PERSON_ALLOCATIONS where that data does not already exist there. i.e. for Department_ID 'Research', I would want to append 'Person_ID', 'Department_ID' (in this case: 'Research') to TBL_PERSON_ALLOCATIONS for any tuples not already held.
INSERT INTO TBL_PERSON_ALLOCATIONS (Person_ID, Department_ID) SELECT Person_ID, Department_ID FROM TBL_NEWDATA WHERE TBL_NEWDATA.Department_ID='Form...'
[code]...
This Query takes a single argument from a control (Forms!Main!IN_Department), and this is the Department_ID to be updated.Is there any way to do this using a single query or will I have to use sub queries? I'd hoped not to as to keep the database as concise as possible.
I have a normalized DB with one to many relationships, using Primary and Foreign Keys.
I need to do inserts and maintain the PK/FK relationship, which means when I add a new PK I need to insert that PK as FK in other tables in the same transaction.
How do I do this in Access? SQL Server I use transactions, but I can't lock up the tables in Access like that.
I did a bunch of searching and found nothing, which leads me to believe I am way off in my thinking. Below are the dirty details
I have 2 tables, tblName and tblPhone. 1 name can have many phones.
tblName has PKName. tblPhone has PKPhone, FKName.
I have a form where user enters a new Name and PhoneNumber.
Name gets inserted to tblName, assigned with PKName = 100
Phone should get inserted into tblPhone with PKName.
IE Insert into tblPhone (FKName, PhoneNum) VALUES (100,"212-555-1212").
The dumb way I am doing it now is I insert to tblName, query tblname for the PK, then write to tblPhone. This can't be right.
My membership database has worked fine until recently. Now I cannot save inserted data. On attempted saving "Update or CancelUpdate without AddNew or Edit" appears.
The problem. relates to 2 tables Member and Addresses. PK in the parent table Member is ID. In the Addresses Table the FK is ID. There is a One to One relation between the tables and Referential Integrity is set. I know 1 to 1 is not good but it worked fine in this small database.
