Error: The TransferDatabase action was canceled- when trying to import programmatically.

[Me]

Hi, I have a problem with an Access application I wrote to
programmatically Import all objects from an MDB 1 to MDB 2, while
running the code on MDB 3.
The code runs on an Access 2000 MDB. When I try to import forms or any
other "document" type objects (macros, modules, reports or macros)
between two Access 97 databases using my application, it works fine.
But when I use it to import btween two Access 2000 databases,
only tables/queries go through. All other objects terminate with the
error:

2501 The TransferDatabase action was canceled.

at this line:

appAccess.DoCmd.TransferDatabase acImport, "Microsoft
Access",SourceDBPathName, intConst, strdocname, strdocname, False

(its in a big loop to get all the objects from the source to the
destination).


appAccess is initialized so:

Set appAccess = GetObject(TargetDBPathNAme, "Access.Application.9")
the intconst variable holds the constant for the object type, forms,
macros etc.

As mentioned, on two Access 97 db's it works fine ( I have a little
error detecting code, to switch Access.Application.9 to
Access.Application.8 for the different version. but otherwise
everything's the same.


Any insights?

Thanks

HH.

 

[Dirk Goldgar]

Hi, I have a problem with an Access application I wrote to
programmatically Import all objects from an MDB 1 to MDB 2, while
running the code on MDB 3.
The code runs on an Access 2000 MDB. When I try to import forms or any
other "document" type objects (macros, modules, reports or macros)
between two Access 97 databases using my application, it works fine.
But when I use it to import btween two Access 2000 databases,
only tables/queries go through. All other objects terminate with the
error:

2501 The TransferDatabase action was canceled.

at this line:

appAccess.DoCmd.TransferDatabase acImport, "Microsoft
Access",SourceDBPathName, intConst, strdocname, strdocname, False

(its in a big loop to get all the objects from the source to the
destination).


appAccess is initialized so:

Set appAccess = GetObject(TargetDBPathNAme, "Access.Application.9")
the intconst variable holds the constant for the object type, forms,
macros etc.

As mentioned, on two Access 97 db's it works fine ( I have a little
error detecting code, to switch Access.Application.9 to
Access.Application.8 for the different version. but otherwise
everything's the same.


Any insights?

Thanks

HH.

My guess (and it's just a guess) is that even though you're requesting
"Access.Application.9", you're actually getting an Access 97
application. I believe that, since all versions of Access since 97
register themselves under the same class ID, GetObject or CreateObject
will return the most recently registered version of Access regardless of
which application version you request.

You could check this by executing

Debug.Print appAccess.SysCmd(acSysCmdAccessVer) ' or 7

If it is as I expect, and you get 8.0, you've got a bit of a problem to
solve.

+ I think you could cause Access 2000 to register itself first by
opening it manually, then closing it.

+ You could -- if you wanted -- prevent Access 97 from
self-registering by locating and rename the msaccess.srg file in the
Office 97 program folder, and creating an empty on in its place.

+ If you know the path to Access 2000 (or are prepared to use code
to track it down), you could conceivably use Shell to start it, and then
use some API code to find its application window and close it.

 

[Me]

Debug.Print appAccess.SysCmd(acSysCmdAccessVer) ' or 7

If it is as I expect, and you get 8.0, you've got a bit of a problem to
solve.

I get 9.0.... Also I implemented all of your sound suggestions
(regarding API's to programmatically start and close the correct
version of MSACCESS.EXE before opening the instance) and the problem
still persists.... on two different computers.

An ungrateful problem....

Nevertheless, Thanks for your advice.
HH.

 

[Dirk Goldgar]

I get 9.0.... Also I implemented all of your sound suggestions
(regarding API's to programmatically start and close the correct
version of MSACCESS.EXE before opening the instance) and the problem
still persists.... on two different computers.

Bother! Then I've no idea what's going on. Have you traced through
your code step by step to make sure, for example, that you have
successfully opened a current database within the appAccess instance?
Does it make any difference whether the document you're importing to
that instance already exists?

 

[Me]

Bother! Then I've no idea what's going on. Have you traced through

your code step by step to make sure, for example, that you have
successfully opened a current database within the appAccess instance?
Does it make any difference whether the document you're importing to
that instance already exists?

Success...! this is Tintin reporting from the moon...!

the problem was that I was having this statement:
Set cdb = OpenDatabase(TargetdbPathName)

in some other operation before the actual Transferdatabase action, and
didn't close this Database type CDB variable.

Apparently, if you have the target database open in a database type
variable and then use the transaferdatabase action with another
Access.Application variable to the same target database, it will fail
with the 2501 error when trying to copy an object that's not a table
or query.

(this was confusing because the same code still works for copying
tables and queries. also The error most helpfully fails to specify
that the problem is having the database already open, so I had no idea
what's going on till I traced it line by line).

I found the bug by trying to run a downloaded internet function,
http://www.applecore99.com/gen/gen055.asp
for copying a database object did work when called from the immediate
window; only when running the full application it failed to work.

Thanks for the help.


hh.

 

[Me]

Bother! Then I've no idea what's going on. Have you traced through
your code step by step to make sure, for example, that you have
successfully opened a current database within the appAccess instance?
Does it make any difference whether the document you're importing to
that instance already exists?

Success...! this is Tintin reporting from the moon...!

the problem was that I was having this statement:
Set cdb = OpenDatabase(TargetdbPathName)

in some other operation before the actual Transferdatabase action, and
didn't close this Database type CDB variable.

Apparently, if you have the target database open in a database type
variable and then use the transaferdatabase action with another
Access.Application variable to the same target database, it will fail
with the 2501 error when trying to copy an object that's not a table
or query.

(this was confusing because the same code still works for copying
tables and queries. also The error most helpfully fails to specify
that the problem is having the database already open, so I had no idea
what's going on till I traced it line by line).

I found the bug by trying to run a downloaded internet function,
http://www.applecore99.com/gen/gen055.asp
for copying a database object did work when called from the immediate
window; only when running the full application it failed to work.

Thanks for the help.


hh.

 

[Dirk Goldgar]

Success...! this is Tintin reporting from the moon...!

the problem was that I was having this statement:
Set cdb = OpenDatabase(TargetdbPathName)

in some other operation before the actual Transferdatabase action, and
didn't close this Database type CDB variable.

Apparently, if you have the target database open in a database type
variable and then use the transaferdatabase action with another
Access.Application variable to the same target database, it will fail
with the 2501 error when trying to copy an object that's not a table
or query.

(this was confusing because the same code still works for copying
tables and queries. also The error most helpfully fails to specify
that the problem is having the database already open, so I had no idea
what's going on till I traced it line by line).

I found the bug by trying to run a downloaded internet function,
http://www.applecore99.com/gen/gen055.asp
for copying a database object did work when called from the immediate
window; only when running the full application it failed to work.

Interesting. I don't have time to investigate it now, but does it make
a difference if you explicitly open your database object in shared mode?
I see from the help file that shared is supposed to be the default, but
it would be worth checking. On the other hand, it wouldn't surprise me
if TransferDatabase wants to open the target database in exclusive mode
to copy the user-interface objects, and it can't do that if you have
that database open even in shared mode.