SQL variables

  • Thread starter Thread starter lbsstacey
  • Start date Start date
L

lbsstacey

I have designed a database where I have many queries that look the same but
they each refer to a different table that contains specific client
information. I run my queries from a process form that contains an
abbreviated name that refers to each client. I am wondering how I can change
my query to instead of referring to the table "Terms_BE" I can refer to a
variable "Terms _" & [Forms]![MainForm]![AbbrName]

INSERT INTO Terms_BE ( SubSSn, MBRSSn, Rel, [Employee_First Name],
[Employee_Middle Initial], [Employee_Last Name], EffDate, TermDate, Field21,
Expr1 )
 
As long as you define the query in VBA and don't 'store' it it's easy:

SQLText = "INSERT INTO Terms_ " & [Forms]![MainForm]![AbbrName] & _
" ( SubSSn.....
DoCmd.RunSQL SQLText

As far as I know there's no way to do this with a 'stored' query, other than
reading it in via VBA, then getting the query Text, changing it, etc, which
is much more of a hassle than just doing what I've suggested.
 
I have designed a database where I have many queries that look the same but
they each refer to a different table that contains specific client
information. I run my queries from a process form that contains an
abbreviated name that refers to each client. I am wondering how I can change
my query to instead of referring to the table "Terms_BE" I can refer to a
variable "Terms _" & [Forms]![MainForm]![AbbrName]

INSERT INTO Terms_BE ( SubSSn, MBRSSn, Rel, [Employee_First Name],
[Employee_Middle Initial], [Employee_Last Name], EffDate, TermDate, Field21,
Expr1 )
Click to expand...

Jim gives you good advice, I think it is much easier to work within
VBA.

However, if you want to alter existing query use this code:

Public Sub ReplaceInQueryDefs(strSearch As String, strReplace As
String)
'Run this command in your immediate window (control-g)
'Replace a string in all query definitions
'Example: ReplaceInQueryDefs "U_ParentID", "ParentID"

Dim qdf As QueryDef
Dim qdfs As QueryDefs
Dim strSql As String

Set qdfs = CurrentDb.QueryDefs

For Each qdf In qdfs
strSql = qdf.SQL
If InStr(1, strSql, strSearch) > 0 Then
qdf.SQL = Replace(strSql, strSearch, strReplace)
Debug.Print "Replaced in query : " & qdf.Name
If vbNo = MsgBox("Found!" & vbCrLf & vbCrLf & "String replaced
in " & qdf.Name & _
vbCrLf & vbCrLf & qdf.SQL & "" & _
vbCrLf & vbCrLf & "Click 'Yes' to continue
search, 'No' to stop", _
vbExclamation + vbYesNo, "ReplaceInQueryDefs")
Then
Exit Sub
End If
End If
Next qdf
End Sub

Regards,
Branislav Mihaljev
Microsoft Access MVP
 
hi,
I have designed a database where I have many queries that look the same but
they each refer to a different table that contains specific client
information. I run my queries from a process form that contains an
abbreviated name that refers to each client. I am wondering how I can change
my query to instead of referring to the table "Terms_BE" I can refer to a
variable "Terms _" & [Forms]![MainForm]![AbbrName]
Click to expand...
Why don't you add a field named Type and store the table type "BE" in
it? Then you only need one table and can simply refer to it without
replacing the table name.



mfG
--> stefan <--
 
Back
Top