You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Building modern looking data forms & reports in Access 2003 can be difficult. Doing the heavy lifting is easy - there are great tools for building the data-bound portions of the form - but making the interface look great can be a challenge and can require some creative hacks. UtterAccess even has a popular board devoted to helping people build better looking interfaces. In Access 12, we've built a number of features that will help people build great looking apps with far less code, time and effort.
We did work to improve native support for image formats. In the past, you could add pictures but there were some serious limitations. First, when the picture was added it was converted from its native format to a device independent bitmap (dib). This resulted in significant bloat if the image stored data in a compressed file format such as gif and jpg. Even worse in many cases, when the image was converted transparency was lost.
Access 2007 now supports BMP, GIF, JPEG, Exif, PNG, TIFF and DiB without bloating the database size. Images with transparency work great. You can see how we're using transparency below - the buttons are all transparent, as is the image in the title bar.
To ensure that database are backwards compatible, we created a new database property called “Preserve image format.” This property controls if the image is converted to a dib or stored in its native format. ACCDB databases default to storing images in their native format while MDB and ADP database Access 2007 will continue to convert images to DiBs so that the image is available when the form is used by older versions of Access. In a nutshell—if you want your image to be displayed in previous versions of Access make sure preserve image format is set to false. I recommend turning off the property if your app is deployed as a runtime or in an Access 2007 only environment.
Another painful limitation of former versions was displaying images on a form or report that were stored in the file system. The Northwind employees form had 135 lines of code to make this work. Now the image control can be bound to a UNC path including jpg and png files.
AutoFormats are a great way to create a consistent look across an application, but the existing themes were looking pretty dated. In Access 12, there are 25 new themes that you can choose from that will add that final polished look to your application. The Quick Format gallery gives a small preview of the font and color schemes:
In Access 11 the SizeMode property provides enums for Clip, Stretch, and Zoom. These properties do not allow us to create really small horizontal or vertical images that tile across the header. The 2003 behavior was to stretch the image across the entire form or report. In many cases the right effect is to stretch horizontally. There are now two new enums: Stretch Horizontal and Stretch Vertical. This makes it possible to stretch an image across the header of the form when it is the Picture property on the background of a form or report.
We also did work to make the grid control more modern and remove the Windows 95 3-D look. Internally, we draw the grid colors from the exact same color palette as Excel.
A few weeks Deano asked about tab controls:
“Also will tab controls blend correctly when you change the colours? I use Access 2000 and there's a section in the top-right of the tab control that does not match the changed form colour.”
I’m pleased to say that the color now matches the form color. Here is a screen shot from WinXP:
We made a one change to buttons that I think folks will find useful. There is a new enum for backstyle called Transparent and a new property called HandOnHover. Using those two properties and you can create buttons that look like hyperlinks. BTW – have you seen the Vista buttons? I think they look great in our shell.
Cyrus asked about pixel drawing problems on disabled images. This problem has been fixed in builds after beta 2 if preserve image format = false. We remove all the color from the image.
There is still a some button work we want to consider in the next version. Cyrus—we hear you on button background colors and images and text. Another thing we wanted to do this version but didn’t get to was color properties for different mouse over states.
If you want to store images directly in the database—the attachment data-type and control works great. In the past users would use the OLE Object data type. Because of how OLE worked there were bloat issues as it had to store a preview of the file along with the OLE data. Attachments are a new complex data-type that stores multiple attachments to a record in a binary field in a hidden table. We compress the file when it is added if it isn’t already a compressed file type. Here is the dialog to manage attachments:
When the image has focus there is a helpful floating toolbar that allows you to navigate through records and launch the dialog.
There are three display as enums: Paperclip, Image, and Icon. The icon will always show up as a paper clip in datasheet view.
Attachments are exposed in QBE similar to other complex data forms. Users can query for the collection or the expanded version returning a row for each attachment. Let me walk through an example with a simple issues database with one record that has 4 attachments. The query designer exposes three columns that are stored in the hidden complex data table: FileData, FileName, and FileType. Double clicking on the root Attachment node will add the attachment collection to the grid below.
This generates the following SQL:
SELECT Issues.Title, Issues.Attachments
FROM Issues;
The query returns the collection of Attachments.
If you add the Attachment.FileName to the query you get the expanded results set. Here is the SQL:
SELECT Issues.Title, Issues.Attachments.FileName
The datasheet now returns the expanded results:
The collection is exposed through DAO so that you can modify and update it. Here is a short code sample:
Private Sub cmdAddImage_Click()
Dim rsEmployees As DAO.Recordset2
Dim rsPictures As DAO.Recordset2
Set db = CurrentDb
' Get the parent recordset
Set rsEmployees = Me.Recordset
'Put the parent record into edit mode
rsEmployees.Edit
'Get the attachment recordset
Set rsPictures = rsEmployees.Fields("AttachmentCell").Value
'Set first attachment to loaded picture
rsPictures.Edit
rsPictures.Fields("FileData").LoadFromFile ("C:\FileName.jpg")
rsPictures.Update
MsgBox "Picture added"
' Update the parent record
rsEmployees.Update
End Sub
The attachment data-type is available in ACCDB files and link tables to WSS lists. It is not available for MDB and ADP databases. We didn’t expand the 2GB limit for Access databases. If you think you will have lots of data in your app break out the attachment column into its own database and use a link table to join it with the table.
In the next big post, I'll look at Filtering and Grouping. In the meantime, I'll have a smaller post about searching in the navigation pane.
Comments: (25) Collapse
The improvements to image handling (and tab control) are much appreciated! Also, the attachments type (and the way to manipulate it) looks excellent. I think this will be a very useful feature to a lot of people. The only problem would be that many organisations (my current client included) use SQL Server to store their MS Access data, rather than Sharepoint, so the new attachments functionality may not be useable after all. That would be a real shame as it looks great.
Are there any improvements planned for the Listbox/Listview control? It would be really helpful if could get a listview control like the one in VS2005. I want a better control over headers, rows, and columns. And coloring perhaps.
Do any of the AutoFormats use system constants so when users change the OS our apps will match or has Office gone so far away from the OS interface with it's own color scheme that trying to match the OS would just look wrong? Do the AutoFormats use the new fonts? I have noticed that you are using DAO in your example. Is there an internal shift in the focus on DAO as the preffered data access library instead of ADO and have there been any changes made to DAO? Does this mean beta 2 is done and we can, hopefully, expect to see it soon? Thanks,
Steve
I see in your example you are using a new method DAO.Recordset2. Do you have any further information on what added functionality/changes that have been made to DAO? On the topic of ADO/DAO, can we now bind mdb Reports to ADO recordsets. It works fine for Forms/Comboboxes/Listboxes currently, but not Reports :-( This is a real pain when using ADO to connect to SQL Server (not linked tables). Dave
I must say the interface improvements do look great. It's these sorts of details that, like image handling, can mean the difference between a product that flies and a Spruce Goose! Apologies if this has been answered before, but you mentioned database bloat; have there been any improvements to this perennial problem. Already large daatbases can grow by 10 times or more, if there is enough processing going on. It's at its worst while coding...
These features look truly great. Do I understand correctly that we ADP developers won't be able to use the new transparent picture features for buttons etc??? OR did you just set the *default* to DIB, but the feature is still available for ADPs? I can understand how difficult it would be to create a working ADP attachment system without playing with the guts of SQL Server, but... it would be really great if Access had built-in functions to compress and decompress binary data (pictures, documents, etc) for storage in image fields (ACCDB or SQL Server). It's a major pain at present to handle this manually - takes quite a bit of code dealing with compression of binary streams or disk files, and other developers would probably not be able to decompress the data. If it was built into the Access object model, then everyone could benefit. It would also be great if that nice little paper clip field and dropdown selector box was available in ADPs, so we also could use it to manage (manually-handled) attachments. (In general, it would be really nice if we could have unbound (and bound) columns in the datasheet view, where we could put row-specific pictures, icons, text, etc.)
I mentioned it in another post, but the ability to do attachments in an ADP would be truly amazing. Now that SQL Server 05 has the varbinary datatype it is even more usable. If you are using a separate hidden table for storing the attachments, I wonder what would prevent you from doing the same thing with SQL server? I assume in your hidden table you are storing just the binary form of the file anyway? I am probably oversimplifying it, but here is some pseudocode:
if (not exist hidden_attachment_table)
{ create_hidden_table(); // regardless of whether this is an access db or sql db.
} Adam
Love the GUI improvements. I lurk on that UtterAccess board often, and have implemented a handful of their suggestions. Glad to see the enhancements to what I can do right in Access. And, while I'm dreaming about new features: could we create their own color schemes and save them external to Access? (import them in another DB, share with others, etc.). I agree with an earlier comment about list boxes & VS2005. Drag and drop anyone? Maybe that's already on-deck and I've overlooked it...
Eric DB ... this should help with the color schemes ... blogs.msdn.com/.../559111.aspx as for sharing with others ... you can use the hidden functions (right click in the object browser and check the 'Show Hidden members) SaveAsText and LoadFromText to create a text file of the form you are using to to create your custom AutoFormat from. Steve
Thanks for the comments, here's a consolidated response: Listbox control improved ala VS2005. Yes, this would be great, but we weren't able to do this version. Do Autoformats use system colors? New fonts? Yes to the fonts for many of the formats. The templates match the new Office colors, so they'll look appropriate in Office and will follow color setting across Office. The existing system color templates continue. Will new images work in ADP's? Yes, but you have to turn on the feature (it is defaulted off for backwards compatibility). Several comments on attachments and ADPs - yes, we agree here and wish we could have done more. This is a SharePoint symmetrical feature, and the issue is the underlying differences between SharePoint and SQL. Is beta 2 done? Not yet, but we're hard at work on it.
DAO vs. ADO. In general DAO works better against Access. There are some scenarios where you need to run ADO, but outside of those, you're likely to get better perf from DAO. What changed in DAO? Primarily the ability to connect to complex data. Can I bind MDB reports to ADO recordsets? No, you need to use an ADP to do that. Is beta 2 done? Not yet, but we're hard at work on it!
Will we be able to create and edit tables and views in SQL Server 2005 as we were with Access 2003 and SQL Server 2000?
Yes. Beta 2 will support editing tables and views in SQL Server 2005 in ADPs.
Oh my Clint. You made my week! We've been strugling with Management Studio since we switched to SQL Server 2005. This has renewed my faith in Microsoft. Thank you!
"Will new images work in ADP's? Yes, but you have to turn on the feature (it is defaulted off for backwards compatibility)." Can you expand on this a little bit? Does this mean that Access makes it easier to use Image fields when talking to SQL server? Where is this feature enabled/disabled?
There is a property in the Access Settings dialog called Preserve Image Format. If you change that property we store images added to forms and reports Picture property in their native format not the OLE format. The image control now supports a control source--so you can bind it to UNC paths (provide from SQL Server if you like) and have a form display JPG, PNG, BMP, etc. Binding the image control to pictures doesn't require the preserve image format as there isn't a backwards compat issue. Obviously, 2003 won't display images bound to a control source.
Comments: (loading) Collapse