VBA - How would I write this better?

[Programmer - wannaB]

I'm using some code provided by Wayne-I-M, but rather then duplicating the
AcExport line for each table, I wanted to loop through one line of code for
a list of tables. This works, but I thought I would post it out and see if
anyone would like to comment on what could be done better (different)..
I appreciate all comments, Thank you..

Public Sub Export()

Dim db As Database
Dim mylist, tbl
Set db = CurrentDb()
mylist = Array("Detail", "AIN", "NoteLog", "Info", "Profile", "Commentary",
"Country", _
"Econ", "Fin ", "Type", "Limits", "PDtable", "ranger", "Overlay")

'Use AcExport rather then AcImport to Export tables to destination DB
without deleting the tables first – this is to prevent tables from coming in
with a 1 at the end of the table name.
For Each tbl In mylist
DoCmd.TransferDatabase acExport, "Microsoft Access", _
"D:\Data\DBA\Dev\ProductionDB.mdb", _
acTable, tbl, tbl, structureonly:=False
Next
End Sub

 

[Roger Carlson]

The only thing I would do different would be to store the table names in a
table and open a recordset to loop through. With yours, if you ever need to
add a table to the list of exports, you have to modify code. If you store
them in a table, all you have to do is add a value to the table. This means
that an end-user could do it. I try to limit the amount of programming
support a database application requires.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[vivian]

"Programmer - wannaB" <[email protected]> a écrit
dans le message de groupe de discussion :
(e-mail address removed)...

 

[Wayne-I-M]

Hi

The reason I don't use a loop is that the tables I export to (and import)
are always the same (for this particular code segment) . So it's just easier
for me to write a line for each table.
But
I agree with Roger that a table with a field containing the table names
would be a very good idea if you may ever change them.

 

[George Nicholson]

Consider adding a DoEvents before your Next statement, just to make sure
each TransferDatabase finishes and you don't stumble across timing issues.

That said, I'm really not sure how much of that suggestion is necessary and
how much is just personal preference. When dealing with file, screen, table,
query or form processing, I don't like assuming that each line of code runs
instantaneously and that the next line will wait until the previous finishes
execution. The only real argument against DoEvents inclusion would be
performance related and if it causes noticeable performance degradation then
I'd be very curious as to what else the processor is trying to do at that
point in time.

 

[Programmer - wannaB]

Thanks Roger, That sounds like a great plan, but it also initiates another
thought and that would be to use variables for the FilePath and FileName as
well, and maybe pull those values from a form, in which case this could be
turned into a CLICK procedure.

This was just a quick thought and I haven't had time to attempt it, yet.
Please let me know if you believe I've missed something..
Thank you all very much for all your assistance.