ARRAY QUESTION...

[Steve P]

Hi everyone...

I'm working on a database right now and am writing a macro that will perform
a number of simple append queries in addition to some more complex
statistical analysis. I'm not good at all with arrays and have no idea how
to use them, but I know they could drastically improve my code's efficiency
if properly used.

I have a step in my macro that deletes records from a number of tables whose
names start with "OUTPUT". I'd like an array at the beginning of the script
to identify all tables with "OUTPUT" at the start of the table name and later
use that list to delete all records from those tables. As I add more tables
in the script, I'm trying to keep from hardcoding repetitive stuff and know
an array could really help.

Any ideas? I guess this question is more of a tutorial request on how to
use arrays. I do have a very specific use currently, but I need to get
better with arrays in general...PLEASE HELP!!! THANKS!

 

[Dirk Goldgar]

Hi everyone...

I'm working on a database right now and am writing a macro that will
perform
a number of simple append queries in addition to some more complex
statistical analysis. I'm not good at all with arrays and have no idea
how
to use them, but I know they could drastically improve my code's
efficiency
if properly used.

I have a step in my macro that deletes records from a number of tables
whose
names start with "OUTPUT". I'd like an array at the beginning of the
script
to identify all tables with "OUTPUT" at the start of the table name and
later
use that list to delete all records from those tables. As I add more
tables
in the script, I'm trying to keep from hardcoding repetitive stuff and
know
an array could really help.

Any ideas? I guess this question is more of a tutorial request on how to
use arrays. I do have a very specific use currently, but I need to get
better with arrays in general...PLEASE HELP!!! THANKS!

First question: you say "macro", but are you actually talking about VBA
code? It seems to me if you are contemplating using arrays, you must be
talking about VBA, not the Access macro language.

Second question: do you *always* want to empty all tables whose names begin
with "OUTPUT"? If so, you can do it without using an array, by looping
through the TableDefs collection like this:

'----- start of code -----
Sub EmptyAllOutputTables()

On Error GoTo Err_Handler

Dim db As DAO.Database
Dim tdf As DAO.TableDef

Set db = CurrentDb

For Each tdf in db.TableDefs

If tdf.Name Like "OUTPUT*" Then
db.Execute "DELETE FROM [" & tdf.Name & "]", dbFailOnError
End If

Next tdf

Exit_Point:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub
'----- end of code -----

That's untested air code, but it should be very close to working code --
*IF* you always want to empty all tables whose names start with "OUTPUT".

 

[Alex Dybenko]

Hi,
you can not use arrays in macros, only in VBA. Furthermore I am not sure
that array can really help here, I think you need to open recordset on
msysobject to get all tables with "OUTPUT" or use tabledefs collection.

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com