Call Variables stored in Table

[hmiller]

Hey there folks,

I was wondering if there was a way to store a list of variables in a
table and then call them one at a time in some loop method.

Here's what I've got:

A table "Tab Names" with 50 values all running down the first column
named "Tabs"

I then have a snippet of VBA that executes a SQL statement to make a
table.
- The SQL has a variable in it "tName"
- The SQL is run through the execute command not DoCmd.RunSQL

Is there a way to look to the table, grab the first variable, run the
SQL with that variable, and then loop through the next 49 variables
until the last variable in the table.

Thanks in advance for you expertice,

[pietlinden]

hm*****@hartfor d.edu wrote:

Hey there folks,

I was wondering if there was a way to store a list of variables in a
table and then call them one at a time in some loop method.

Here's what I've got:

A table "Tab Names" with 50 values all running down the first column
named "Tabs"

I then have a snippet of VBA that executes a SQL statement to make a
table.
- The SQL has a variable in it "tName"
- The SQL is run through the execute command not DoCmd.RunSQL

Is there a way to look to the table, grab the first variable, run the
SQL with that variable, and then loop through the next 49 variables
until the last variable in the table.

Thanks in advance for you expertise

Sure, not hard at all.

Public Sub ProcessSQLs()
dim rs as DAO.Recordset
dim strSQL As String

'--read the individual field values in the column...
set rs = DBEngine(0)(0). OpenRecordset(" SELECT [Tabs] FROM [Tab
Names]", dbOpenSnapshot)

do until rs.EOF
'---build SQL statement in a string variable
strSQL = "INSERT INTO... WHERE SomeField=" & cQUOTE &
rs.Fields("tNam e") & cQUOTE

'--Execute the statement
DBEngine(0)(0). Execute strSQL, dbFailOnError
'--Move to next record
rs.MoveNext
loop '---repeat with each record in the recorset

'--clean up variables.
rs.close
set rs=nothing

End Sub

[Larry Linson]

The logic of what you are doing is, to say the least, "unusual." If you
explain what you have and what you are trying to accomplish, there's a
really good chance that someone will be able to suggest a simpler approach
than creating so many (temporary?) tables. At best, if you do this, you
will have to Compact and Repair frequently to prevent database bloat.

However, to answer your question: You can construct the entire SQL
statement, and probably will only need a couple of concatenations. The
"source" of a Db.Execute is a string. Here's a quick example, using a
delete query because it was simpler... you code your own loop, table read,
etc. use your own text as basis for the SQL, and retrieve your table name
from your "table of tablenames." For the query you describe, you won't need
a WHERE clause. "tblDelMe" is a little test table with a text field for
primary key.

Function ExecuteQuery(ps trK As String) As Boolean
On Error GoTo Proc_Error
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strQ As String
Dim strT As String
strT = "tblDelMe"
strQ = "DELETE * FROM " & strT & " WHERE [ID] = """ & pstrK & """"
Set db = CurrentDb()
db.Execute (strQ)
ExecuteQuery = True
Proc_Exit:
On Error GoTo 0
Exit Function

Proc_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in
procedure ExecuteQuery of Module basDAOMiscCode"
ExecuteQuery = False
Resume Proc_Exit
End Function

Larry Linson
Microsoft Access MVP

<hm*****@hartfo rd.eduwrote in message
news:11******** **************@ f16g2000cwb.goo glegroups.com.. .

Hey there folks,

I was wondering if there was a way to store a list of variables in a
table and then call them one at a time in some loop method.

Here's what I've got:

A table "Tab Names" with 50 values all running down the first column
named "Tabs"

I then have a snippet of VBA that executes a SQL statement to make a
table.
- The SQL has a variable in it "tName"
- The SQL is run through the execute command not DoCmd.RunSQL

Is there a way to look to the table, grab the first variable, run the
SQL with that variable, and then loop through the next 49 variables
until the last variable in the table.

Thanks in advance for you expertice,

[hmiller]

Larry,

I am interested to learn of another way to minimize DB bloat. Here's a
little more background, let me know if my thinking is correct. I've
only been working with VBA for two months now.

I need to allow people who don't know how to code in VBA the ability to
change the variables in my table "Tab Names". My ultimate goal is to
create a form that would allow other Non-VBA savye employees to add or
delete companies from the table.

The table is a list of 50 or so company codes, ie WalMart. The SQL I
have takes these variables and creates andexports an Excel Worksheet
with the worksheet tab named as the variable, ie Walmart. The SQL also
uses the variable to select only the records in the main table with
that name. So there's a big table with a bunch of company info, then
the SQL resultant selects only the records with that company name. It's
just a way to seperate out the data for ease of analysis.

If you folks know of a way to minimize the number of tables, and bloat
while still allowing for a end Form alteration of the variable on the
list.

I really appreicate your help her everyone, this is my number one
source for "to the point" VBA help, thanks!

[hmiller]

Just in case any one would like to see the final solution I settled on
here it is:

Public Sub Company_Tab()
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strComps As String
Set rs = CurrentDb.OpenR ecordset("Tab Names", dbOpenSnapshot)

Do Until rs.EOF

strComps = rs.Fields("Tab Name")

strSQL = "SELECT Companies." & strComps & "INTO tbl" & strComps"

CurrentDb.Execu te strSQL, dbFailonError

rs.MoveNext

Loop

rs.Close

Set rs = Nothing
End Sub