TransferDatabase HELP

[Donna]

I have a macro that has worked great for the past 3 years in Office 2003, but
will not work in 2007.( I have been unable to convert the database. ) I've
enabled macros, I've established trusted locations, but the transfer won't go
anywhere. I am trying to move some tables from one database to another
database that was created around the same time. These databases are
copy/pasted into a new folder and renamed each year (in MyComputer) and the
previous year's data is removed. The structure hasn't been modified at all.
I've checked the user permission's and that carried over just fine.

I tried running the macros from another person's pc who has access to these
databases and it didn't work on his pc either... got all the same messages. I
was kind of hoping it was just my pc, but apparently not.

These databases are very important at this time of year, as they are for the
company budget input. I desperately need to find out what is wrong with the
macros and get it working. Any help would be very much appreciated! Thanks!!

 

[Gina Whipp]

Donna,

Might help if we knew the messages. Also, have you enabled macros? (Under
Trust Settings)

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

I have a macro that has worked great for the past 3 years in Office 2003,
but
will not work in 2007.( I have been unable to convert the database. ) I've
enabled macros, I've established trusted locations, but the transfer won't
go
anywhere. I am trying to move some tables from one database to another
database that was created around the same time. These databases are
copy/pasted into a new folder and renamed each year (in MyComputer) and the
previous year's data is removed. The structure hasn't been modified at all.
I've checked the user permission's and that carried over just fine.

I tried running the macros from another person's pc who has access to these
databases and it didn't work on his pc either... got all the same messages.
I
was kind of hoping it was just my pc, but apparently not.

These databases are very important at this time of year, as they are for the
company budget input. I desperately need to find out what is wrong with the
macros and get it working. Any help would be very much appreciated! Thanks!!

 

[Donna]

Gina,
I get the error 2590: Operation is not supported for this type of object.
Macros have been enabled on both pc's, both databases.
About the only solution we have not tried yet is to modify the sandbox
setting from a 3 to a 2.
There are some other quirky issues with this database that I don't
understand that I would like to discuss with you.

Thanks for the reply.

 

[Gina Whipp]

Donna,

Check the References could be some are *MISSING*. When you say some other
quirky issues, type away... they may gave greater insight into what the
problem is. A thought, are you using any .OCX files, such as Common
Controls or the Calendar Control?

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Gina,
I get the error 2590: Operation is not supported for this type of object.
Macros have been enabled on both pc's, both databases.
About the only solution we have not tried yet is to modify the sandbox
setting from a 3 to a 2.
There are some other quirky issues with this database that I don't
understand that I would like to discuss with you.

Thanks for the reply.

 

[Donna]

Hi again,
I forgot to mention one of the other problems we are having. We cannot get
the database to convert to the 2007 format. This is not the only database
that I've had trouble with. Some of them do and some of them don't. I haven't
figured out why.

I've opened it and tried to do a Save As, but that doesn't work either. Any
thoughts about this?

Donna

 

[Gina Whipp]

Donna,

Please list the References that show up in your database... Go to the Code
Editor - Tools - References.

Are you using any code in your databases? If yes, do you have Option
Compare Database and Option Explicit at the top of every module that has
code? Here's why...

Option Compare Database tells your code how to compare strings, numbers,
etc. Option Explicit tells your code that each variable needs to be defined
for it to be used. This is very important... make sure they're at the top
of each module (commented code (in green, something like: 'commented code)
can go before these, or empty spaces, but nothing else.

Once you have insured those two statements are there in the Code Editor
select... Debug - Compile and fix any issues that come up which might be
declaring variables, etc...

You also didn't mention if you are using and OCX controls in the databases
that won't convert, such as the Calendar Control or the Common Controls.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Hi again,
I forgot to mention one of the other problems we are having. We cannot get
the database to convert to the 2007 format. This is not the only database
that I've had trouble with. Some of them do and some of them don't. I
haven't
figured out why.

I've opened it and tried to do a Save As, but that doesn't work either. Any
thoughts about this?

Donna

 

[Donna]

Gina,
I checked the code. There is very little of it in this database. Ran the
debug and found a few blocks of code that were no longer needed, so I deleted
those. Option Compare and Option Explicit okay.

I don't have an ActiveX controls and no calendar control.

I checked the references in both databases. The only 2 I needed are: VB for
Apps and the MS Access 12.0 object library.
I had OLE Automation and MS DAO 3.6 object library included, but was able to
uncheck them without a warning. I closed the db, went back to check if there
were any MISSING and there were not.

I would like to have this working properly in the next few days or we will
have to resort to a work around, sending the data out to Excel, then
importing into the Master. We may have to open each child db to update their
tables rather than passing the data back at the click of a button. Not a
pleasant thought.

This db has worked for the past 3 yrs with no problems. And now this....
it's very upsetting.

Thanks for working with me on this. I appreciate it very much.
Donna

 

[Gina Whipp]

Donna,

Hoping we can get it running the way it was but still undusre why it's not
running. Next thing to try is checking the Reference for...

Microsoft ActiveX Data Objects 2.5 Library

....and making sure you move the DAO Library to upmost top position. Another
thing to do is remove any SetWarnings lines from your macro and let's see if
a new error will generate and point us in the right direction. Oops, one
more thing... what is the path in the macro and confirm that that is the
correct path by navigating to the destination database via Windows Explorer.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Gina,
I checked the code. There is very little of it in this database. Ran the
debug and found a few blocks of code that were no longer needed, so I
deleted
those. Option Compare and Option Explicit okay.

I don't have an ActiveX controls and no calendar control.

I checked the references in both databases. The only 2 I needed are: VB for
Apps and the MS Access 12.0 object library.
I had OLE Automation and MS DAO 3.6 object library included, but was able to
uncheck them without a warning. I closed the db, went back to check if there
were any MISSING and there were not.

I would like to have this working properly in the next few days or we will
have to resort to a work around, sending the data out to Excel, then
importing into the Master. We may have to open each child db to update their
tables rather than passing the data back at the click of a button. Not a
pleasant thought.

This db has worked for the past 3 yrs with no problems. And now this....
it's very upsetting.

Thanks for working with me on this. I appreciate it very much.
Donna

 

[Donna]

Morning Gina,
I added the MS ADO 2.5 Library to the References. It is in 3rd place. I can
not move it to the top. 1st is Visual Basic for Applications, 2nd is MS
Access 12.0 Object Library, 3rd MS ADO 2.5 Library. I can not move the first
two, and can not uncheck them. I get this message: Can't remove control or
reference; in use. The only thing I had open was the switchboard form, so I
closed it and went back to see if I could move the ADO up, but got the same
message.

It's that way with both databases.

I've checked the path and file name a dozen times to make sure they are
right. I even copy/pasted the path from Explorer over to the Macro so I
wouldn't have to worry about a typo. I know the Child db is finding the
Master db cause it will delete the table in the Master, but can't create the
new table to replace the one it deleted.

As for removing the SetWarnings in the macros; are you talking about all
those yellow triangles with an exclamation point in the center, and when you
hover over the icon it says: This action will not be allowed if the database
is not trusted. ?

I've got those nasty signs in all my macros. I've set up trusted locations
as much as I can and made sure the destination pc (Larry's) trusted locations
were set us also, and that his pc will enable macros. All of that has been
done, and we would have to go to each dept's computer's to set up those
trusted locations and enable macros... and whatever else we are overlooking.
There's something missing... or those warning signs would go away. What are
your thoughts? I'll check back later. Thanks Gina, Donna

 

[Gina Whipp]

Donna,

Is the database in such a state that you can send it to me?

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Morning Gina,
I added the MS ADO 2.5 Library to the References. It is in 3rd place. I can
not move it to the top. 1st is Visual Basic for Applications, 2nd is MS
Access 12.0 Object Library, 3rd MS ADO 2.5 Library. I can not move the first
two, and can not uncheck them. I get this message: Can't remove control or
reference; in use. The only thing I had open was the switchboard form, so I
closed it and went back to see if I could move the ADO up, but got the same
message.

It's that way with both databases.

I've checked the path and file name a dozen times to make sure they are
right. I even copy/pasted the path from Explorer over to the Macro so I
wouldn't have to worry about a typo. I know the Child db is finding the
Master db cause it will delete the table in the Master, but can't create the
new table to replace the one it deleted.

As for removing the SetWarnings in the macros; are you talking about all
those yellow triangles with an exclamation point in the center, and when you
hover over the icon it says: This action will not be allowed if the database
is not trusted. ?

I've got those nasty signs in all my macros. I've set up trusted locations
as much as I can and made sure the destination pc (Larry's) trusted
locations
were set us also, and that his pc will enable macros. All of that has been
done, and we would have to go to each dept's computer's to set up those
trusted locations and enable macros... and whatever else we are overlooking.
There's something missing... or those warning signs would go away. What are
your thoughts? I'll check back later. Thanks Gina, Donna

 

[Donna]

Yes, I can send both of them. Where to?

Donna,

Is the database in such a state that you can send it to me?

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Morning Gina,
I added the MS ADO 2.5 Library to the References. It is in 3rd place. I can
not move it to the top. 1st is Visual Basic for Applications, 2nd is MS
Access 12.0 Object Library, 3rd MS ADO 2.5 Library. I can not move the first
two, and can not uncheck them. I get this message: Can't remove control or
reference; in use. The only thing I had open was the switchboard form, so I
closed it and went back to see if I could move the ADO up, but got the same
message.

It's that way with both databases.

I've checked the path and file name a dozen times to make sure they are
right. I even copy/pasted the path from Explorer over to the Macro so I
wouldn't have to worry about a typo. I know the Child db is finding the
Master db cause it will delete the table in the Master, but can't create the
new table to replace the one it deleted.

As for removing the SetWarnings in the macros; are you talking about all
those yellow triangles with an exclamation point in the center, and when you
hover over the icon it says: This action will not be allowed if the database
is not trusted. ?

I've got those nasty signs in all my macros. I've set up trusted locations
as much as I can and made sure the destination pc (Larry's) trusted
locations
were set us also, and that his pc will enable macros. All of that has been
done, and we would have to go to each dept's computer's to set up those
trusted locations and enable macros... and whatever else we are overlooking.
There's something missing... or those warning signs would go away. What are
your thoughts? I'll check back later. Thanks Gina, Donna

 

[Gina Whipp]

Donna,

1. Please indicate where you want me to send the converted databases back
to.

Issues...

1. You did not have Option Explicit at the top of every module. For why I
had to place those there...

Option Compare Database tells your code how to compare strings, numbers,
etc. Option Explicit tells your code that each variable needs to be defined
for it to be used. This is very important... make sure they're at the top
of each module (commented code (in green, something like: 'commented code)
can go before these, or empty spaces, but nothing else.

....this insures there is no error in the code.

2. You indicated the databases would not convert. Closing the Switchboard
fixed that issue.

3. Unable to test your TransferDatabase issue because I do not have a J:\
drive, however I created a macro and all went well. So my next conclusion
is to make sure you have set the folder you want to transfer to as Trusted.
And make sure Macro Settings are set for *Enable All, etc...*

4. Removed the Reference that fails in Access 2007, *Microsoft Visual Basic
for Applications Extensibility 5.3*. It is a leftover from when this
database was in Access 97.

Observations...

1. Access 2007 kind of picky about having Reserved Words as field names.
Using them will cause you problems as it causes Access problems. My
suggestion would be to adjust those fields names. For a complete list
see...

http://allenbrowne.com/AppIssueBadWord.html

For naming conventions see...

http://www.regina-whipp.com/index_files/NamingConventions.htm

2. Your database is set up more like and Excel spreadsheet (flat file
format) rather then a database (relational). If it is working for you then
by all means leave it as it is but know that set up like a database,
extracting and importing data would be a lot easier.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Yes, I can send both of them. Where to?

 

[Donna]

Hi Gina,
Sorry to take so long to reply. I wanted to let you and anyone following
this thread that I found a solution to the problem. It was the 5 tables in
the Master db that were giving me the most fits. I decided to rebuild one
table, completely start over. I used the Window pane view instead of the
Tabbed view so I could see the old table and the new table side by side. I
had already renamed the old table, so I was able to name the new table with
the "old" name.
After replacing all 5 of those main tables in the Master, I also replaced
the InputData table in the Master where those 5 tables get appended in to.
I rebuilt the Append queries just to be on the safe side.
I went out to the child db's and replaced their InputData table, the one
that gets exported. And I replaced any table that had the same structure by
doing a Copy/Paste of the newly created table.
I deleted all the old tables too.
The TransferDatabase macros now work like a charm!! yea
We are only running into one small snag due to Trusted Locations. The Master
db is stored in a location on the intranet that no one but 3 or 4 people have
permission to. When the child database user clicks on the Export My Data that
runs the macro to transfer their InputData table to the Master db, it doesn't
go anywhere. They don't get any kind of error message either.
Larry can go into each of the child db's and export their data from his
computer and all is good. He had already been doing that anyway, so it's not
a big problem.

I appreciate all the time and effort you spent with us. It was a great help
to look into some areas of the db that we had not looked at before. Thank you
so very much!
Donna

 

[Gina Whipp]

Thank you for sharing that you this issue resolved!

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Hi Gina,
Sorry to take so long to reply. I wanted to let you and anyone following
this thread that I found a solution to the problem. It was the 5 tables in
the Master db that were giving me the most fits. I decided to rebuild one
table, completely start over. I used the Window pane view instead of the
Tabbed view so I could see the old table and the new table side by side. I
had already renamed the old table, so I was able to name the new table with
the "old" name.
After replacing all 5 of those main tables in the Master, I also replaced
the InputData table in the Master where those 5 tables get appended in to.
I rebuilt the Append queries just to be on the safe side.
I went out to the child db's and replaced their InputData table, the one
that gets exported. And I replaced any table that had the same structure by
doing a Copy/Paste of the newly created table.
I deleted all the old tables too.
The TransferDatabase macros now work like a charm!! yea
We are only running into one small snag due to Trusted Locations. The Master
db is stored in a location on the intranet that no one but 3 or 4 people
have
permission to. When the child database user clicks on the Export My Data
that
runs the macro to transfer their InputData table to the Master db, it
doesn't
go anywhere. They don't get any kind of error message either.
Larry can go into each of the child db's and export their data from his
computer and all is good. He had already been doing that anyway, so it's not
a big problem.

I appreciate all the time and effort you spent with us. It was a great help
to look into some areas of the db that we had not looked at before. Thank
you
so very much!
Donna