Help! moving to Off 2010 from XP, some Access capability no longer available?

[FlyingBoat]

We are moving to Office 2010 as standard. Did Access really loose a number of features from 2002 or am I just missing something? I can't believe they just left so much out for such a hit to productivity. Am I the only one using these features on nearly a daily basis such as drag and drop between databases, inserting queries on the selected table or query, etc?



1. Can I no longer drag and drop objects from one database to another? (such as queries, etc.)

2. If there is a field or table change, a query which joins this table to another can no longer be viewed in design view. All I get now is the SQL not the two tables with the join missing in design view! It can't be shown now in design view when one of the join fields is missing or changed! This is a big problem when I have several tables in a large query!

3. I work with dozens of queries and tables in an Access database. I could always be on the query or table I am looking at, Select Insert Query from the menu and then work on a query on this object. With Office 2010, I need to Select Create, then Query Design, then go and find the table or query again from a list box. It is very frustrating that I can no longer simply select to create a query on the object I have selected and instead need to go search and find it again!

4. The properties for an object no longer show up in a list format! I could put a number and description in the property for an object, then easily sort by the property name to provide me the order of processing or the order of output objects.

5. I used to be able to click on the "Modified" date column and sort by date to see the latest query or order in which they were created or modified etc. Very clean and easy to use. Compare this to now where the dates are under each object making it difficult to see and also more difficult to sort by.

6. For some of my more complex tables I get the message "Too many properties" when I try to change the design of the table. The same table is fine in Access 2002.

7. Can't save a Macro as a different name after modifying it.

8. Can't add some commands to Macros.

[help_me_with_access]

are you actually ASKING if these numbered issues are problems? or are they responses to experiments you did yourself?

just at first glance, a lot of what I see here are not true at all. what it looks like to me is that most of your numbered items are general observations, generated with little or no research. but that might not be true for all.

for instance, number 8 probably isn't right. starting in either v.07 or v.10, MS added a huge number of commands to macros, but the default list is severely limited. there's a button in the ribbon to list the hidden commands. but in this instance, remember who the target market is for office products....generally MS targets semi-uneducated or completely uneducated folks. hence, they will initially need help protecting themselves from marginal annoyances that more-seasoned technical people don't even think about anymore. a good e.g. of MS providing features of the sort to these kind of people is, the macro disabling feature that you see by default when opening office files.

as for your opinion on the lack of productivity with newer office software releases, yes that's completely true but it always has been. but you have to remember, business thinking is really no longer "traditional", as it were. unless of course you're working in a highly-political or politically-influenced industry like banking. then you can get away with being a traditional business person (kind of like an IBM consultant....those guys are AWESOME, but many of them I'm sure are strictly business). how that relates to MS software releases though, is very simple in that I believe people in the target market for office software are not traditional business people, kind of like yourself?

it's also my belief that, as technology becomes overly simplified and the focus is shifted completely towards empowering users through "nifty" interfaces, traditional business people that like to think and play the business game will have a limited arena to operate in. there's really no danger either, in newer technologies that sport "neat" features. stuff in the server world like ajax, jquery, ssrs, or even simple scripting features like the auto save on this forum. but at the end of the day, everyday, the bottom line is that newer "stuff" or "features" is always accompanied by a change in security measures (good or bad) and productivity changes, as they are viewed by humans. but in fact, productivity measurement has nothing to do with software, because whether you're working with a command-lined interface or the newest .net features in a 4.0 framework, the fact of the matter is that data is always still processed the exact same way (endianness notwithstanding). relating software to productivity measurement is absolutely irrelevant without a driver for change, which is usually political BS or other worldly things that demand global or regional changes.

I would venture to guess that if you mapped a timeline of 1980 to today and you compared the speed or architecture of software to the increase in population (or anything else that inevitably changes), you would probably see that whatever ratios between the data items you picked to analyze, they would be extremely close if not exactly the same from 1980 to today.

[FlyingBoat]

Thanks, I will look into #8 ribbon to list additional commands. Not as much of a concern thus listed lower. I had looked into the earlier issues, searching google and Access forums but couldn't find anyone else talking about these issues or if they are still there and I am not seeing them. I posed the issues to our Corporate Migration IT, and they gave back idiot answers like, set the db to auto change on field name change to resolve the issue of #2.

There weren't these big losses in capability moving from 2.0 through Access 2003. Thus, I am asking if I am missing something in not seeing the capabilities in 2007 and 2010.

No one else drags and drops objects? Instead you need to close the db with the objects you want to import. Do an import into the one you want to add them to, by finding the DB again, and needing to pick the objects to import. A huge issue if there are many objects in the db. I need to remember the name and find them. Versus for example sorting in the db by modification date and then dragging and dropping them into the new db.

[help_me_with_access]

FlyingBoat,

most of the issues you're talking about here can be solved with a little ingenuity are your part. I'm not criticizing you, but hey I think the bottom line here is that if small issues cause this much havoc in your life you may want to consider moving to newer technologies. like .NET? open source?

I really find the comment about your IT department pretty funny. but there's really no reason to mad about something like that, if indeed your IT department is like most and nothing but a call center. you have to remember that people who work in call centers are, in general, not very educated people. so 80-90% of the time the answer you will get back is not an intuitive one, but rather one that has been scripted or given to them because they don't naturally have the ability to solve undocumented problems. they're not idiots, but they're also not people who should be in leadership roles...which is probably why they are working in a call center. it's a good fit job for them. kind of like most government workers....they're generally not very bright which is why government is a great fit for them. education and common sense is not unfortunately a requirement of many jobs in government. it's always been that way.

[FlyingBoat]

Thanks for the time taking a response. The IT I was referring to was the Migration group who asked us to look at our applications and identify problems with the move to Office 2010.

If you mean by a little ingenuity, that I should do a lot more work, several clicks, looking for queries which I used to be able to drag and drop, deciphering 1000 character SQL statements instead of visually seeing the difference and making changes on the fly, then yes, that can be done. But at a large productivity hit.

Very unfortunate. I am just asking here if there are known work arounds or if I am missing something. I am going to need to keep an old copy of Access around and hopefully they will restore some capability in future releases, but am not hopeful, particularly if no one else is even mentioning it. I don't use Access for application development. I use it to analyze data.

[help_me_with_access]

Boat,

I just read through your list and it doesn't look to me like any of those are real issues that I've ever seen. Except for the message about queries not being allowed view in design mode. That does happen, and the reason I've seen in the past is simply that whatever change is made (regardless of source), the program cannot recognize it or is not written such that design view will display it properly. That doesn't sound at all like it makes sense, I realize, but never the less it is probably that way.

you shouldn't keep an old copy of access around either, especially to analyze newer file types. 1) because you can't open them anyway, and 2) because you risk data corruption.

by the way, this program has not drastically changed from v.02-03 to the present. the drastic nature of changes probably occurred when v.10 was released, as it contained never-before-seen features like data macros and such. and some of the object / object designer formats changed - e.g. the macro and macro builder. introduction of the ribbon was nothing special, as it was just a re-design of the menu options essentially.

so it sounds to me that you've blown many things here up out of proportion, but that's just my opinion. if you want to analyze data, there are MANY tools to do so that are much better than this program. I am interested in where your data comes from and what kind of statistical analysis you are doing on it. care to humor me?

[FlyingBoat]

15 years analyzing and working with Medical claims files, along with some assistant staff trained in working with access. I don't think you know my business and I am very successful with it. I get the work because I can do it much quicker and more accurate than others using systems such as SQL Server, SPS, or SAS, (all of which I have used and am trained on). This discussion is counter productive. I am not asking for a new tool. The tool changed, making many of the functions we use much more difficult. And you flippantly say these are trivial. They are not trivial in analyzing data, looking for issues, etc. when it now takes much more time and causes chances for error due to the increased difficulty caused by lack of function.

I am not responding further to this thread unless there is something of value. I appreciate the time, but you realize if someone comes along and asks why the light switch is no longer on the wall and now you need to walk upstairs to get to the switch in the dark, and you answer that I should get a different house or that this is trivial, that is a bit irritating.

I come across these issues several times a day. Very frustrating that capability was removed. Or maybe it is there and I haven't been able to find it. That is what I am looking for. Is it there or not?

[help_me_with_access]

sorry about the direct comments sir. sounds like you're seasoned enough in business. fair enough.

bottom line, I think the primary issue is that the functionalities you're missing are probably there and you're just not finding them. and yes, I'm well aware of instant panic associated with computer users. I too, am a developer, but not in the medical field. I would assume panic runs more rampant in medical than in other industries.

so hopefully I can give some feedback on your listed issues. try the following, as this is what I can come up with off the top of my head:


1. Can I no longer drag and drop objects from one database to another? (such as queries, etc.)
I can do it in v7 on my local, with every type of object. i'm guessing there's another explanation.


2. If there is a field or table change, a query which joins this table to another can no longer be viewed in design view. All I get now is the SQL not the two tables with the join missing in design view! It can't be shown now in design view when one of the join fields is missing or changed! This is a big problem when I have several tables in a large query!
i think this is a program issue - try copying the sql to another qry object and start over, just as an experiment. be aware that a common error that access gives in this scenario is something like "access cannot display the relationship between tables x and y...". it doesn't necessarily mean that the relationship statement is wrong.


3. I work with dozens of queries and tables in an Access database. I could always be on the query or table I am looking at, Select Insert Query from the menu and then work on a query on this object. With Office 2010, I need to Select Create, then Query Design, then go and find the table or query again from a list box. It is very frustrating that I can no longer simply select to create a query on the object I have selected and instead need to go search and find it again!


4. The properties for an object no longer show up in a list format! I could put a number and description in the property for an object, then easily sort by the property name to provide me the order of processing or the order of output objects.
don't understand...don't show up WHERE? are you using properties for the purpose of seeing object data, like by using the database analyzer for documentation purposes?


5. I used to be able to click on the "Modified" date column and sort by date to see the latest query or order in which they were created or modified etc. Very clean and easy to use. Compare this to now where the dates are under each object making it difficult to see and also more difficult to sort by.
can't this and #4 both be solved by right clicking the db window and resorting/changing "list by" options? i hope not! remember, db window listings and object orderings have a default! and ever since the db window was moved to the left side of the screen by MS, I don't remember ever seeing "list" view as a default for new files.


6. For some of my more complex tables I get the message "Too many properties" when I try to change the design of the table. The same table is fine in Access 2002.
this error, and a few others can occur during import/copy/drag and drop operations because access manipulates the field indexes in such a way that causes it. the solution can be googled, as I've done it. if the error is caused by another op, not sure what to tell you.


7. Can't save a Macro as a different name after modifying it.
what error do you get? that doesn't make sense at all. highlight the macro name, press F2, and type a new name. if you're using the "save object as" option, that doesn't surprise me as the schema of the macro object, I believe changed from v.07 to v.10.


8. Can't add some commands to Macros.
did you push the "list all actions" button on the ribbon? the list is minimal if you dont.

[Piranha]

I'd be happy if anyone could answer #3.

FlyingBoat, I used to work in IT (12 years as a developer and DBA for in Java/.NET and DBMS included Teradata, DB2, and SQL). Now I'm doing actuarial work, and I totally agree with you here on all counts.

This ribbon redesign of Office was done for people who use their computer to print out schedules for their pinto-league baseball teams. To power users, the redesigned interface is just a kick in the groin. Almost every keyboard shortcut is changed. There are a few that were preserved in an "unsupported mode", I guess. The most painful are "Alt-I-Q (number 3 above) and Alt-F-C

They probably did it so that business users would complain and then IT departments would tell them their applications need to be put in the data center so that they could sell more SQL enterprise software. Once it's in there, an operation like adding a column to a table went from taking a few seconds to taking weeks (justification, steering committee, change control, development, testing, back-out plans, implementation, and then two months of troubleshooting. All the while with the IT folks asking, "Why do you want to do this, anyway?"

Don't even get me started on the changes in Excel. Perhaps I'll have to register for another forum. Ok, I'll indulge in one: when you hit the context menu key on a selection, then press 'I', it used to know you wanted to 'I'nsert. Now they have TWO items on the context menu with the shortcut for 'I'. The other one is Hyperl&ink. "Why does hyperlink use the I, was the H already taken?" you may ask. Of course not.

Ok, /rant. In fact, I'll say something nice about the query design window, and that is that you can drag tables/queries from the navigation pane into the query design.

If someone has any info about #3 that would be great. I can't even figure out how make this happen in VBA.

[Piranha]

I think on #4 and #5 what he means is that the navigation pane has replaced the main "Database" window. In there you could display all the objects in a variety of fashions similar to the way windows explorer does, i.e. "List", "Icons", "Details". With "Details" selected, at least the beginning of the Description on an objects properties would display. It was very useful in databases with large numbers of objects. Now to display the details it lists them all clustered together rather than in a column format like windows explorer.

[FlyingBoat]

Piranha, my sentiments exactly! And the same responses from IT. At least I am not alone. Microsoft seriously dumbed this down and as you say it may have been to move people to centralized SQL. Or it is a different sent of developers who could care less what features power users made use of. Are you aware of a thread of complaints to Microsoft where they have responded to this at all?

"Help_me_with_access", thanks for letting me know you could do drag and drop on your end. This let me know there is a way and I found out it was a set up issue on my end. By chance I discovered if the drag from db is opened in exclusive mode, then you cannot drag and drop from it into another db. But if it is opened in non-exclusive mode, then you can drag and drop from it. With the Office 2010 implementation Access was set to open in exclusive mode by default, (something I seldom did before), so that is why I could not drag and drop between db anymore. I switched setting default to non-exclusive mode and I can drag and drop again!

Regarding #2, it doesn't work to take the SQL and copy it into a new object. I get the message, "access cannot represent the the join expression etc" "one or more fields may have been deleted, etc".

This is exactly the situation, say a join field name changed from field1 to being named as field2 or was deleted. Now the whole query which may have 15 tables joined together and have a huge syntax cannot be looked at in design view, where I could have simply created a new join to the new field name. Now I need to handle it all in a huge SQL syntax, or better yet, just open it in Access 2003 so I can view it and draw a line or remove the missing table from the query, etc.

[help_me_with_access]

Regarding #2, it doesn't work to take the SQL and copy it into a new object. I get the message, "access cannot represent the the join expression etc" "one or more fields may have been deleted, etc".

This is exactly the situation, say a join field name changed from field1 to being named as field2 or was deleted. Now the whole query which may have 15 tables joined together and have a huge syntax cannot be looked at in design view, where I could have simply created a new join to the new field name. Now I need to handle it all in a huge SQL syntax, or better yet, just open it in Access 2003 so I can view it and draw a line or remove the missing table from the query, etc.

yeah see...and this is where sql skills REALLY come in handy. when you have to make up for issues where programs fall short. at my last employer, I simply modified sql statements when I got those errors. that works fine but you have to know how to write acc-flavored sql.

[Piranha]

The point is that in the older, superior, version of the product it was not typically necessary to edit the SQL. It sounds like FlyingBoat can do that, it's just that it wasn't necessary before.

FlyingBoat, what are the circumstances here with modifying an object that is already included in queries? Are you removing columns and replacing them, or renaming them in place? I've found a few occasions where Access 2010 handled this pretty well. Sometimes these operations in access are entries into the journal of irreproducible results, but it seemed if I opened up access, then simply opened any of the queries that use a table I was about to change, and closed those queries again before I made the change, then the change would cascade through. It was as though the metadata got cached somewhere. I have noticed that something like a column rename if the column stays in place, or removing and adding a column of the same name but in a different ordinal position would cause no problems.

Again, I'm just curious what kind of changes your making.

[FlyingBoat]

yeah see...and this is where sql skills REALLY come in handy. when you have to make up for issues where programs fall short. at my last employer, I simply modified sql statements when I got those errors. that works fine but you have to know how to write acc-flavored sql.

Now you are implying I don't have SQL skills. Your attitude and tone is getting tiresome. I have written tons of SQL, of course I can change the SQL. It just is a lot easier in the earlier versions of access to make the visual changes then to use the new versions and not see the design view and make changes in SQL.

[FlyingBoat]

I am not designing applications for these tasks, but rather analyzing data. Sometimes the data I receive from sources have their field names changed slightly as an example. When I import, say this quarter's data, maybe they changed Member_ID to MemberID in their excel file or text header. Or it can be from a new source of data where the fields are just slightly different. Or maybe I want to bring over some queries which are very similar that I have created for a different client. Tables names are the same but there could be a field name difference as in above. Yes, I can go through and validate that no field name was changed. There are several mechanisms of working through the issue. Just much easier to do, as you say, in the older, superior version, where I can spot the issue and make the visual changes very rapidly rather than editing pages of SQL, or going back and changing names which are perhaps in linked tables where there are other queries already created using the new name, etc.

The basic issue here, is it appears the new versions of access cannot display queries in design view where there is a joined field which is no longer there. Old versions of access would tell you there was a problem in not being able to make the join, but would then show you the design view with the tables unjoined, which from there you could just join to the slightly different named field, or whatever.

It sounds like the metadata issue is another problem I certainly don't want to come across. I don't want changes cascading through that I didn't intend.