CopyObject command in VBA

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


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


For Each item In checkedItems

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

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

z = z + 1


myConnectionSource = New OleDb.OleDbConnection(strConnectionSource)


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()



x = x + 1

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

End If

End If

Next table



myConnectionSource = Nothing



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

End Try