CopyObject command in VBA

Presuming you're doing this via VBA code in your Access Database, use the "DoCmd.CopyObject". But to make sure you don't get "system" tables (hidden ones created by Access), use:

Dim t as TableDef

For Each t In CurrentDb.TableDefs

If t.Attributes = 0 Then 'zero = user table

DoCmd.CopyObject sBUTName, "", acTable, t.Name

End If

Next

You can theoretically use this to copy any Access object (table, query, form, report, etc) within a database or to a different database file.

-----------------------------------------------------------------------------------------------------------------------------------------------

Below is the code that I used. I have all the variable predefined so I did not include them anymore. in the code below. It stores the data table into the dataset but it does not update the dataset. Anyway, it's okay I guess I could do it another way.

Dim table As DataTable

Dim copyTable As DataTable

Dim arrTable(25) As Object

Try

For Each item In checkedItems

strList = strList + item.SubItems(0).Text + vbCr

arrList(z) = item.SubItems(0).Text

z = z + 1

Next

myConnectionSource = New OleDb.OleDbConnection(strConnectionSource)

myConnectionSource.Open()

For Each table In CoaSfaDataSet2.Tables

If table.TableName.ToString.Contains("SFA") And _

table.TableName.ToString.Length < 15 Then

If arrList(x) = table.TableName Then

arrTable(x) = table.Copy()

'copyTable = table.Copy()

CoasfaDataSet.Tables.Add(arrTable(x))

'myDataSet.Tables.Add(copyTable)

x = x + 1

z = z - 1 : If z = 0 Then Exit For

End If

End If

Next table

CoasfaDataSet.AcceptChanges()

myConnectionSource.Close()

myConnectionSource = Nothing

Catch

MsgBox("Cannot proceed! {0}." + e.ToString(), MsgBoxStyle.OkOnly, "NOT SUCCESSFUL")

End Try