Possible To Update 180 Queries With Find / Replace Script Or Function?
Apr 2, 2014
The company I work for is consolidating regional server space onto a single server. I have relinked all of my tables, but my ~180 queries are still pointing to the old server.
My question: Is there a script or other process that can be executed that will search through all my queries within the database to find the string "dbo_tbl" and replace with "dbo_vwtbl"? I would very much like to avoid taking each individual query to a notepad...
Code: INSERT INTO TB_SISTEMAS ( LOGIN, SISTEMA, PERFIL, DATA ) SELECT Left([dbo_BACKUP_ACESSOS.LOGIN],255) AS LOGIN, dbo_BACKUP_ACESSOS.SISTEMA, Left([dbo_BACKUP_ACESSOS.PERFIL],255) AS PERFIL, dbo_BACKUP_ACESSOS.DATA FROM dbo_BACKUP_ACESSOS WHERE (((dbo_BACKUP_ACESSOS.SISTEMA)<>"ACTIVE DIRECTORY") AND ((dbo_BACKUP_ACESSOS.DATA)="2014-03-23"));
But Iwant to be able to use a set of data to be used in the Replace Statement, so I create a table to add each string I would like to have replaced by "nothing", and trying to make the replace query to look there in order to find what to replace.I also created a table where I will list the systems that I dont want in the select, so I removed the "ACTIVE DIRECTORY" and replaced by the colum that have the list of system I dont want listed.This is the result:
Code: INSERT INTO TB_SISTEMAS ( LOGIN, SISTEMA, PERFIL, DATA ) SELECT Replace((Left([dbo_BACKUP_ACESSOS.LOGIN],255)),[PREFIXOS_E_SUFIXOS]![Valor],"") AS LOGIN, dbo_BACKUP_ACESSOS.SISTEMA, Left([dbo_BACKUP_ACESSOS.PERFIL],255) AS PERFIL, dbo_BACKUP_ACESSOS.DATA FROM dbo_BACKUP_ACESSOS WHERE (((dbo_BACKUP_ACESSOS.SISTEMA)<>[SISTEMAS_EXCLUIDOS]![Sistema]) AND ((dbo_BACKUP_ACESSOS.DATA)="2014-03-23"));
The thin is that this keeps asking me to enter the parameter value for "PREFIXOS_E_SUFIXOS!Valor" and for "SISTEMAS_EXCLUIDOS!Sistema"
I have a table of sales information. Part of my products are kits that are a combination of individual products. (Think of it as a value meal at a fast food restaurant. You can order a hamburger, a soda, and a small french fry separately. But if you get the Value Meal #1 you get all three at a discounted price.)
What I would like to do is make a new table/query that extracts out the kits out of the original sales table and replaces the kit record with records that show the components.
I have attached a file to show as an example of how the records current look and how I would like it to look.
I am running a query for an apparel manufacturing facility. In my query I have a table called 'OrderForm' which is where the orders are put in. There are more than one type of fabric that can be a part of an apparel item, so as a result, there are multiple fields pulling from the 'FabricType' table. In order to get this to work in my query I created 'SubTables' for the different fabric fields. For example, I have tblFrontfab, tblbackfab, tblsleevefab, and tblcollarfab which are just extra copies of the 'FabricType' table. The actual question is that when I want to replace characters like ,./& in the fabric field but I can't use the replace function. It says that it is too complex to calculate.
I used this notation Frontfab: Replace([tblFrontfab].[fabric],".","") and the error was that it is to complex to evaluate.
Code: "UPDATE Individuals SET [ShareholderOf] = " & Me.CompanyNo & " WHERE [Name] = '" & PerName & "';"
However, doing so will obviously change the "Shareholder of" field into what the user inputs (Me.CompanyNo). What should I use if I want it to ADD the user input rather than REPLACING the old [shareholderof] value?
I've made a simple form to Login/Logout with radio buttons but the buttons only allow me to push a number as a value, in my case 1 or 2 for Login or Logout.
How would I make an update query to change those numbers to the equivalent text? Or is that not possible in the same field because that is 2 different data types?
We do alot of find and replace in our main form (CTRL+F). The glitch is that once the pop-up box for "Find and Replace" appears, we almost always have to change the search setting from "Whole Field" to "Any Part of Field". Is there a way to set the default to "Any Part of Field" instead so that we don't have to change the setting every time?
I am not sure if my coding is efficient or not but it takes so long to read lets say tbCOMPANY 40k records and find if values of two columns exists in tbRESULT which holds 30k records. Then if it doesn't find any records in the tbRESULT it will just insert the row otherwise an update will be executed.
What I did was:
1. Read tbCOMPANY each row using for loop 2. tbCOMPANY.FindFirst to lookup if values exists in the tbRESULT 3. If tbCOMPANY.noMatch, it will insert into tbRESULT 4. else it will update the matched row in the tbRESULT
Its been taking like over 30 minutes to process this and still ongoing and it will still keep on running. Now Access is showing as Not Responding in the task manager.., Any efficient way to do this?
I have a list of dates of births in a table, but the way they are always imported (from some piece of crappy software somewhere) is in the format of: 01.01.2005 (using full stops).
Access doesn't like this, and insists on slashes, or dashes. Now, if I do a find/replace, it works perfectly, just replacing all the full stops with slashes. However, I want to do this quite regularly...any ideas how to do it programatically in VBA?
I have my main table and one column is a lookup field to another table.
One of the options that was in the dropdown needed to be changed. So I changed the dropdown, but now I need to change all the old entries to reflect this change.
I opened the main table, and thought I could do a find/replace. But it doesn't work. If I do just a 'find' it finds the old text just fine. But if I do the Find/Replace option, it says the 'look for' text was not found. Even tho, I just found it using the find option. If I click on the tab that says find, it will find it again. If I click on the find/replace it can't find it.
any ideas on a way to quickly replace my text in these fileds, without having to go 1 by 1 through each record.
I have several boxes instead of line breaks throughout my table. I would like to do a find and replace. Is this possible???
I can't seem to select the box to cut and paste it into the "find" line, which is too bad, because I might be able to get by with just replacing it with a semi-colon or something.
But, since I'm asking, I might as well go for the gold and ask how to make it show as a new line too, right??
I found in other threads how to use chr(10) and chr(13)... which I did, which created the boxes in some of the fields. (Some of the fields already had boxes). I also read where I should use vbcrlf, but I can't get that to work. In THIS thread: http://forums.aspfree.com/t38413/s.html&highlight=chr%2810%29 someone had the same problem as me, and was told to do an OnFormat Event.. and well, I'm just not advanced enough yet to figure that out. I've done modules, and sql view queries... and a lot of tweaking with the help I've already gotten here.. so I AM learning...
So... is there a way to do this? Preferably cheap and easy.
I have a field that I am running a few queries on. What I want to do is run a macro when the form is opened.
I have been looking on how to write a query that will convert any space within a string to a "+". I cant figure it out...
IN the first part of the macro I am setting the field up so I can hyper link to Mapquest. My problem is that the link wont work with the spaces I have in my address field. So i figure after I running the first query to create the string below, I could then run another query to replace all spaces with a "+"
I have a large macro with a lot of actions to run reports and export them to a shared drive. I basically need to copy the macro, but make it run a different query/report. Is there any way to do a find and replace to change the query and report name within the macro in one step, opposed to going through all the action lines and changing it?
My database has a street address field that I would like find and replace a handful of certain strings with other strings. For example I would like to see:
Sunset Point = Sunset Pt Main = Main Prospect = Prospect Caminito Paseo = Camto Paseo Camino Litoral = Cam Litoral
I would like to perform something exactly like Find and Replace but to multiple strings in ONE function or query to update.
Is this possible? I looked at fReplace() but not sure where to put this.
Is there a way for me to do a find/replace on a '#'? I am trying to remove all '#' in a text field. When I do a find/replace (replace '#' with ''), it removes all my numbers from the text field.
When I use the built in 'Find and Replace' utility in a subform (datasheet view), it does the replace on multile rows but when I try and navigate to another record on the main form I keep getting an error mesage 'Operation Not Supported in Transactions' and have to close the form and all the previous 'find and Replace' I did have been undone and rolled back to their previous state :confused:
Any ideas how I can the 'Find and Replace' to work on teh subform?
