Eileen's Lounge

Attachment for my previous post

Thanks! I can reproduce the problem.

I've just tried out Office 2010 SP1 for the issue I raised in this post:
http://www.eileenslounge.com/viewtopic. ... =60#p48408" onclick="window.open(this.href);return false;

Access version number after the SP1 patch was:
Access 2010 (14.0.6024.1000) SP1 MSO (14.0.6023.1000)


Can someone please confirm, I think its a bigger mess than before!

I'll get to the points.

1) For an existing database created before Office 2010 SP1 (based upon an original Access 2003 database) the fixed width issue still remains after a text-file export, as in the example in above post, it's not fixed width!

2) For a newly created database after Office 2010 SP1 (Access 2007 or Access 2003 format), and using the example above, it actually comes out as fixed width text file export!

However, for some reason, the length of the fixed-width for the example above (field a) is DOUBLE the length that you specify in the export spec.
So, If I said field (a) should be a length of say 50 in the export spec. It actually gets exported into the text file as a field length of 100.
Try it with a smaller value - field (a) export as 10 chars in the export spec. Then see that it actually gets exported as 20 chars in the text file...

I tried it with a database newly created in Access 2010 SP1, using Access 2000 database format. The table from your example was exported as a fixed-width text file with the correct widths: 255, 10, 2. The widths were not doubled.

Hans, for the export spec for field (a), specify that the export length as 10 (not the default 255).

OK, I see the same effect now - 20 characters are exported from field a instead of 10.

They clearly haven't got it right yet!

I have just had another instance of an annoying little bug that I don't remember from previous versions.

Sometimes, when I go to make a new query, the Property sheet opens in front of the Add Table dialog, and anything I try to do results in Access beeping at me.
The only thing I am allowed to do is choose Add Table or Close, but I can't actually see either.

Whenever this happens I forget how to get out of the predicament. I think that putting the focus onto another program then returning to Access bring Add Table to the front.

I've been using Access 2010 but haven't noticed this behaviour. What I would try:

Pressing the Escape key to close the active window (the Show Table dialog perhaps);
Try Alt-F4 to do the same, although this might close Access entirely depending on where the focus is;
When I get a chance I would move the Properties Window so it doesn't obscure the Show Table dialog the next time;
Pressing F4 to toggle the Properties Window.

But you may have tried these already

Thanks

When I get a chance I would move the Properties Window so it doesn't obscure the Show Table dialog the next time;

I move the Properties Window many, many times every day.

By moving the Properties Window back into the "wrong" position, I was able to recreate this problem "on demand".

Yes Escape works and so does Alt+F4
F4 does not.

Here is another Access 2010 quirk; I won't exactly call it a bug, but it is different than prior versions.

In a nutshell, if you change the RecordSource of a report in the Open event, Access blows away any OrderBy clause.

We don't tend to use this technique anymore, but we still had an old report that used it. Basically code looked like this:
If you examine the Me.OrderBy property after the above code, there is nothing in it in Access2010!

You can get around the issue by setting the OrderBy AFTER resetting the RecordSource.

Certainly not a big thing, but when someone complains that a report (that has been around forever and has not been modified) is no longer sorting properly, at least this might save you some time!

Thanks! Indeed not a big one, but useful to know.

Having created and opened a DAO.Recordset, I was unable to use the following means of reference to a field:

rs.FirstName
'ERROR: Method or data member not found

This then led to the same error with rs.Fields("SomeOther"), which was working prior to the first error.

I use 'DAO.' consistently, moved the DAO reference above the ActiveX, (failed) to compile, compact & repair, and even imported to blank database (in case of corruption).

I never use this method of reference - rs.FirstName - it was just to demonstrate the alternatives. I won't, now, ever use it, and maybe it was an issue in earlier Access' as well?

I would not recommend this means of reference, but it could be an issue with older databases being upgraded/ improved

A specific field is not a property of a recordset, that's why rs.FirstName fails. You can use the following methods to refer to the FirstName field:

rs.Fields!FirstName
rs!FirstName
rs.Fields("FirstName")
rs("FirstName")

and, if FirstName is the 3rd field in the table:

rs.Fields(2)
rs(2)

(Fields start counting at 0)

Hi Hans.

I know all this but understood that I could also use 'rs.FieldName'. Looks like I've been misled - and it's probably just as well that we can't use this method.

At least it's not a bug

Mark L wrote:Here is another Access 2010 quirk; I won't exactly call it a bug, but it is different than prior versions.
In a nutshell, if you change the RecordSource of a report in the Open event, Access blows away any OrderBy clause.

I have just come across something similar, when doing some work in 2010 on a db that has worked for years.

If you change the RecordSource of a report in the Open Event, Access 2010 ignores a Where clause used to open the report. 2007 does not ignore it.

DoCmd.OpenReport stDocName, acPreview, , strWhere, , strArgs
strArgs passes the name of the query to use as a recordsource.

added
Further investigation revealed that if the report does not have a record source, and I set it in the Open event, the Where clause works.
But if it has a record source, and I change it in the Open event, the Where clause does not work.

That subtle difference saved me a lot of trouble.

See if the hotfix mentioned in Another hotfix for Access 2010 SP1 helps.

New Bug maybe...

I am using Windows 7 Enterprise, MS Office Professional Plus 2010 (Access 2010 Version: 14.0.6112.5000 32 bit)

I have several linked tables to SQL Server 2008. Frequently when I run a query, or double click linked table the results are not refreshed properly.

Sometimes it's just One column, but others will refresh properly. I have tried the "Refresh" and "Refresh ALL". I am wondering if Access
or the ODBC caches some data and doesn't refresh properly or is it SQL 2008?

Same MDB opend in 2003 NO ISSUE. I have seen this on several computers at my company.

It can be VERY CONFUSING for the users as well as us developers.

Has anyone seen anything like this? Do you have any suggestions?

I have tried to relink the tables, I have tried to drop the tables and add them again but nothing changes.

Just curious.

Thanks,

Welcome to Eileen's Lounge! We have some members who work with Access and SQL Server, I hope they'll have a suggestion for you.

There have been some odd issues with Access 2010 and ODBC links to SQL Server 2008. Since you have SP1 installed, you may want to look at a hotfix that Microsoft has available to correct some issues with linked tables, but before you do, let's find out a bit more about your environment. Are you running on a Domain with a server, or is your workstation in workgroup mode? I've personally battled a nasty performance issue when workstations were configured in workgroup mode. Also, what ODBC driver are you using with SQL Server. We recommend either the standard one that ships with Windows 7, or the Native Client 10 driver. Are you seeing that kind of behavior on forms and reports as well as in queries and tables? Finally, have you installed SP1 for Windows 7?

I am running Windows 7 SP1

As for the ODBC connection. I am not sure how to find out what version is being use.

It says SQL Server. I see now there are actually a few choices... SQL Native Client, SQL Server, and SQL Server Natibve Client 10.0

Maybe I should try the others and see how well they work.

Generally I see this issue on Datasheets on a form, or query results or when I open up a table by double clicking. It doesn't happen everytime.

The resultsets are not extremely large maybe like 1,000 to 3,000 rows. or even smaller.

Sometimes I see the first 40 rows are correct, then when I page down I see the same data over and over like paging
portion isn't working like it did in 2003 / 2007. And maybe this is what the issue is.