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?
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.
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! =)
The user is currently using c:main.mdb In main.mdb, I have a form, and have a combobox with two value, which is db1.mdb and db2.mdb.
If the user choose db2.mdb, delete all the current db1.mdb link tables(if there is any), and import all the tables from db2.mdb as link table If the user choose db1.mdb, delete all the current db2.mdb link tables(if there is any), and import all the tables from db1.mdb as link table
And I need to perform some vba on the afterUpdate event of combobox.
My question is, how to 1)delete all link tables using vba 2)Import all tables from a certain mdb file as linked table using vba?
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)
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).
This stuff can sure be stressful. :) Anyway! This is all linked to a form, but it's queries I'm sure I'm having the problem with. I'm hoping someone can help me out here.
I have fields in a subform which I've got an update query running after the focus is lost on a field, and it puts these values in a table I created. I'll call this table A. This is so that I may run another update query on the form when this is done, and have it take the values from this table created and add them to a total in another. I'll call this second table B.
I know this design behind this is flawed because in theory I don't need the 'temporary store' (A), but it's in the design of the thing to show that the values are infact being stored. As I'm sure you can understand, in order to keep doing this each time I open the form, I need to clear A. Preferably on a button press on the same form so that I may have the totals all calculated on B and then clear A right after.
The problem I'm having is that I'm using a delete query on a button press, and instead of just clearing the fields I need it to, it's deleting everything in A. I should mention that in A I have an ID field which should be telling the update/delete query which records to update/delete from in the criteria. This is a problem because it's deleting all the records on the form as I have them linked to A.
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?
I am accessing an oracle database that has several thousand records in it. I am quering for specific requirements, but would like to save my query results in a local access database for faster searching capabilities. Is there a way for me to set up a query that will go out to my oracle table files, select the records that pertain to my search criteria, and add records to locally stored tables without duplicating itself each time that I run the query? I would appreciate any assistance in this matter. Thanks for your help!
What I'm trying to do is making Cascade update and delete between two tables. For example in my case I have Field called Full name in table1 and I want the same field in table2 that will update when something is added in table1 and vice versa. I have tried relationship but I got an error that those fields does not have any unique value.
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?
