SQL to use for multiple queries

[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 )

 

[Marshall Barton]

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 )

Posting the same question 5 time in half an hour is a waste
of everyone's time. Please be more patient. If you don't
get a response in a day or two, then repotsing the question
with REPOST in the subject is appropriate. You should also
consider rephrasing your question to make your situation
more understandable.

From what I can tell, your problem has no specific answer
because databases should not have separate tables for the
same kind of data. Instead you should have a single table
with a field that identifies the client along with the
current fields.

 

[AlexM]

Assing the value from your form control to a variable strClient
then do
docmd.runsql "insert into Terms_BE" & strClient & " etc....

 

[Dale Fye]

Agree with Marshall, combine all of these separate client tables into a
single table which contains the ClientID. Then all you have to do is modify
the WHERE clause to refer to the client you are working on.

But to answer your question. There is not much you can do about a saved
query, since you cannot pass it the value of the table you want to use.
Obviously, what you passed us is not a complete query, since there are no
values, provided, but if you replace the "Terms_BE" in your query with
"[TableName]" then you could use code similar to the following to execute
the query:

strSQL = currentdb.QueryDefs("yourQuery").sql
strSQL = Replace("[TableName]", "[Term_" & Forms!MainForm!AbbrName & "]"
Currentdb.Execute strSQL,dbFailOnError

HTH
Dale