Followup on Copy Objects from Another A2007 db using vba



I created a form with several listboxes (one for each type of object...)
allowing a user to select a "sandbox" FE (where newly modified objects have
been tested...) and a "Production" FE (where users log in using Tony's
AutoFE). The user selects the objects he wants to copy in each listbox (one
listbox to a tab...), then clicks the "TransferSelected" button. Herewith the
event procedure:

Private Sub btnTransferSelected_Click()
Dim ctl As Control
Dim lngObjectType As AcObjectType
Dim strObjectType As String
Dim varItem As Variant

For Each ctl In Me.Controls
If TypeName(ctl) = "Listbox" Then
strObjectType = Right(ctl.NAME, Len(ctl.NAME) - 2)
Select Case strObjectType
Case "Query"
lngObjectType = acQuery
Case "Forms"
lngObjectType = acForm
Case "Reports"
lngObjectType = acReport
Case "Module"
lngObjectType = acModule
End Select
If ctl.ItemsSelected.Count > 0 Then
For Each varItem In ctl.ItemsSelected
DoCmd.TransferDatabase acImport, "Microsoft Access",
tbSandbox, lngObjectType, ctl.Column(0, varItem), ctl.Column(0, varItem)
Next varItem
End If
End If
Next ctl
End Sub


Obviously, Column 0 contains the name of the object to be transferred.

The code works if I select exactly one object to be transferred. If I select
multiple objects (e.g. 1 form, 1 query, 1 module, and 1 report), I get error
29068 which says I must stop and try again. An odd message if I've ever seen

Strangely, it properly transfers the first item on the selected list. The
error occurs while it's still pointing to that item, though. In fact, it
seems to transfer two queries OK, but comes up with the error if I try to
transfer two forms. The message comes up, I hit "debug", then F5, and I get
the message again. If I repeat that process 5 times, it transfers the first
selected item in the listbox 5 times!



I realized that the source and object db's on this test were the same db
(should work, but maybe that's the issue....) So, I tried something
different. I instantiated a new Access.Application variable (AppAccess) and
opened a new "current database" using the OpenCurrentDatabase method. Then, I
changed the TransferDatabase command to AppAccess.DoCmd.TransferDatabase....

No joy.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question