UDFs That Accept Arguments To Process UPDATE/INSERT INTO SQL Stmts?
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?
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 have this update query that does not quite work. I have attached a picture of the design view. What I need to do is process each line and update the processed field (yes no) to minus one to then exclude that record. Unfortunately when this query runs, it sets the processed field after matching all records.
I am trying to update a recordset using VBA based on the max "process instance" from another table. After the code executes, the field I am updating is still blank.
Code: Set rs = db.OpenRecordset("myTable", dbOpenDynaset) If Not (rs.BOF And rs.EOF) Then rs.MoveFirst Do Until rs.EOF = True emplid = rs![Employee Number]
I'm not sure if I am biting off more than I can chew. I have a text field in each record in my database (Inherited) The db has nearly 5,000 records. I would like to split the field into records in a seperate table. An Example of the table as is now;
Is this possible in one hit or do I need to process the records without dates first and then run another process to split those with Dates? I say dates but the field is a text field. About 15-20% of the records contain dates which are always enclosed in parenthesis.
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?
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.
Hi. I'm putting together a database of property clients and i have almost finnished but as always i seem to get so very close to the end of a project and i hit a wall.
background:- I have about 5 different tables which mostly have relationships but there is one main table (clientDetails) which holds the main record this has a very predictable clientID primary Key. the other tables have equally predictable keys for the tables ie: LettingID for a letting client insuranceID for insurance clients etc.. the deal is really that EVERYONE is a client so they get the main details filled out and another record in another table using the primary key of the main table in all other tables. one client can have many other records relating to them useing the primary key.
The Problem:- I have made lots of nice forms that all link up together so you can edit and brose all of the records in the database, and this works very well.. however my problem comes when i want to add a new record in one table that relates to the same record in another using the forms not the table view. eg. on form 1 (clientDetails) i add a new client to the database all fine even using the autonum for the PK. now i bring up the record of that client and i have a button (addProperty) this brings up another form (propertyDetails) using the PK from the previous. this works fine if there is already an entry in the DB but if not then the PK is autonumbered..
What i need:- I need to be able to open up a form using PK then (addProperty) button to the next form which finds that record using the pk (clientID) and FK (propertyID) and if there is no FK relating to the PK then to autonumber and use that... so i basically transfer the PK from form 1 to form 2 regarless of any other data relating to it, and auto numbering to the next number in that table for the FK.
eg.
form1 form 2 ------- > --------- PK=100 PK=100 FK=5
this is probibly really basic.. but i hope someone can help ..
I have got an unbound access form, and in this form I gather 2 dates (i.e. through txt fields). Once I have these fields, I want to open a form that has one list box on it. This list box should populate based on the dates that I gather on the previous form. How do I specify this through the DoCmd.OpenForm arguments? Currently I have the following code, but it is not working:
Form 1: Dim whereClause As String whereClause = "SELECT * FROM qryInvoice WHERE tblInv.InvDate Between #" & txtStartDate & "# And #" & txtEndDate & "#" & ";" DoCmd.OpenForm "frmInvoiceFax", acNormal, , , , , whereClause
Form 2: public Sub Form_Load(args As String) MsgBox args lstInvoice.Rowsource = whereClause End Sub
I know I am not doing it the right way (because it is not working), but I can't actually find how to do it. Help!
I'm trying to QUERY an ACCESS database called ARQUIVO and i would like to have from the column EMPRESAS all the DISTINCT records that have the same 'aviacao' in the INDUSTRIA column.
and it goes like this: Code:<%Set rse = Server.CreateObject("ADODB.Recordset")sSQL = "SELECT DISTINCT empresa FROM arquivo WHERE industria='Aviacao'" rse.open sSQL,con, adOpenStatic, adLockPessimistic, adCmdText%>
But all I get in a 500 error.
The fact is that if I use "*" instead of "empresa" the query runs but i list all the records with 'aviacao' in INDUSTRIA
I have this SQL query made in ACCESS - that does what I want - but it doesn't rune in mine ASP page.
SELECT DISTINCT arquivo.EMPRESA, arquivo.INDUSTRIA FROM arquivo WHERE (((arquivo.INDUSTRIA)="Câmaras Municipais"));
Can you help me? I get the error wrong type of arguments in my function as follows Private Sub Command0_Click() Call ANewDBWithPass("c:BELest", "secret") End Sub
Function ANewDBWithPass(ByVal tName As String) '// adds the current date to the name of the database Dim wsp As Workspace Dim db2 As Database Set wsp = DBEngine.Workspaces(0) If Right(tName, 4) = ".mdb" Then ' Remove .mdb from the name tName = Left(tName, Len(tName) - 4) End If ' Add date and extension tName = tName & Format(Date, "dd-mm-yyyy") & ".mdb" ' Create database ',Set db2 = wsp.CreateDatabase(tName, dbLangGeneral) Set db2 = wsp.CreateDatabase(tName, dbLangGeneral & ";pwd=" & strPassword) db2.Close End Function
