Concatenate Sql String

[abenitez77]

I have a string in a variable that has a sql string. I need to pull out the table name and replace it with another name.

These are 2 examples of the string that I get:
"Select * from mytable;"
"Select field1, field2, field3 From table2 Where Field2 = 123 Order by field1"


I want to replace the table name in the string with the name of a table I am holding in a variable. How can i do this? I cannot build out the string with the table name I want, I know how to do this, I need to replace the string I get.

 

[plog]

You need to use Mid (http://www.techonthenet.com/access/functions/string/mid.php) and InStr (http://www.techonthenet.com/access/functions/string/instr.php).

You will need to find 2 points the in string:

pos_A = the position where the 'FROM' in your string ends
pos_B = the position where the table name in your string ends

With those 2 positions you can use Mid to concatenate a new string and replace the table with the one you want to use.

You would use InStr to find pos_A and pos_B. pos_A will be relatively simple--you just look for 'FROM' and add 5 to where it is found in your original string. pos_B would require you to search for an array of words that could follow the table name. Those words are all the SQL keywords that can come after the FROM clause (WHERE, ORDER BY, LEFT JOIN, etc.). You would have to determine which of those was in the table and closest to teh FROM clause. With that you could then backtrack and find pos_B.

 

[MarkK]

Just the table name? Why do you have two differently named tables with exactly the same structure? That is very unusual. All data having the same structure should be in the same table. You should only have to edit the WHERE clause to select a different set of records.

 

[abenitez77]

found the answer...

strOldTable = Split(strSQL, " from ")(1)
strOldTable = Left(strOldTable, InStr(strOldTable, " ")-1
strSQL = Replace(strSQL, strOldTable, strNewTable)