Array Loop CopyObject

  • Thread starter Thread starter a
  • Start date Start date
A

a

Dear all

DoCmd.CopyObject """D:\ BackUp.mdb""", "tblTable1", acTable, "tbltable1"

I want to use array to give me sequence number for the tbltable1

I mean every time I use docmd.copyobject

Tbltable1 change to tbltable2 change to tbltable3 and so on

I don't know how to use array and loop

DoCmd.CopyObject """D:\ BackUp.mdb""", ARRAYNAMEHERE, acTable, "tbltable1"
 
Hi,

To use an array - you need to put stuff into it. If I understand what you wish to do, then perhaps the best way to go about that - allowing for simple future additions, deletions, name changes; easy control of which tables are being copied - is for you to make a new table with only one text column named TableName. Call the table tblBackup. In this instance, instead of using a programmed array, we will use the table you create as our array. If there aren't any table names in tblBackup, then the function will open a message box and tell you this, and quit. In the simplest terms, a table is an array that someone wrote down on virtual paper for less volatile storage.

I've put blow by blow comments in to help you see how I am using the table.

=======================

Public Function RunBackup() As Boolean

'backup table object will be set to our tblBackup
Dim rs As DAO.Recordset

'set the DAO object reference
Set rs = CurrentDb.OpenRecordset("tblBackup")

'move to last record to initiate the count
rs.MoveLast

'do we have any records? if not then
If rs.RecordCount = 0 Then
'no records found, tell someone about it
MsgBox "No names found, nothing to do!", vbCritical, "NO BACKUP"
'set function false - no backup done
RunBackup = False
'stop running the code
Exit Function
End If

'we have records, move to back to the first row
rs.MoveFirst

'loop through the rows to the end
Do While Not rs.EOF
'run the copyobject macro using the current tablename
DoCmd.CopyObject "D:\ BackUp.mdb", rs!TableName, acTable, rs!TableName
'move to the next row/tablename
rs.MoveNext
'keep going until we run out
Loop
'set function true - backup has been accomplished.
RunBackup = True

Exit Function

=============================

Hope this works for you...
Gordon
 
Back
Top