Excel Pivot Tables - Changing External Data Source

I know this is Access, but Excel pivot tables linked to an Access DB are quite useful - but, how do you change the external source to a different Access DB?

I found it here and it bears repeating --> http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Objects/Q_22910802.html

Basically you create another pivot table in the same workbook and then change the source of the old to 'Another PivotTable report or PivotChart report'. Once you've done that you can delete the new pivot table. MS... always consistent.

Change Name to Last Name, First using a query

Use these functions in an update query:

Name: Right([Distributed To],Len([FullName])-InStr([FullName]," ")) & ", " & Left([FullName],InStr([FullName]," ")-1)

Of course this wont work well with those with a middle name...

Of course, you should save names as separate fields; lastname, firstname, middlenamesorinitial, etc.

A real name object should be something like this: surname, givennamefirst, givennamemiddle, calledbyname --- anything else? Or how about priorsurname (for the married or otherwise who have changed their name since employeement.

Combo Box Lookup that populates other fields besides the combo box

On rare occasions -- Lets say you want to populate a table of 'reviewers' using the employee table as a lookup. Your first instinct is to only capture the key of the employee table and use the employee table to display employee data. But the review table is static, that is, the tracking of when a employee reviewed something is done at one point in time. When the employee gets a promotion and a new title or moves to a different department, you don't want that information in the review table -- you want ot populate the same fields in another table in a different table.

You want to select the employee for example using a combobox to lookup the key field and save it in the combobox fields source. But also return various other values and populate other fields.

Set the source of the combo box to include the key column plus the other columns.

Use the after_update event of the combobox to populate the other fields:

Private Sub cb_Employee_FK_AfterUpdate()
Me.Department = cb_Employee_FK.Column(2)
Me.Title = cb_Employee_FK.Column(3)
end sub

Important to remember however is if the property Column Count isn't set to the number returned you wont have access to them using the column(#) property above.

But this displays too many columns in the when using the combobox for a lookup right!?

Hide the ones you don't want by setting the width property 0;1.5;0;0 for example.

Using Pictures in Reports and Forms

The Problem: Large Access database size even after compacting.

Evidently, Access converts pictures into bit map (.BMP) file and a small .jpg or other type of compressed picture is therefore expanded to a larger size. In addition, if you started with a large file that you then scaled to fit in the corner of a report or form the original size is still the size that is stored in your Access database. The result is, everytime you create an additional report in your database (perhaps copying from a already created report), that has two logo images in the reporter header and page header, your database increases in size by 2 MEGABYTES! (if the picture file, expanded is 1MB, which is not that big for a picture file).

Yikes!

The solution: There are two possibilities.

1. If the the database is local, or if the tables are on a shared file server, but the forms, reports, etc are local you can link to the picture instead of embedding it (this is one of the properties for the picture control on the form or report) - this requires the picture file to be on everyone's computer as well as the .mdb file. If the database is shared on the file server, you'll have to make sure the network speed is fast enough not to create an unacceptable delay when opening the reports.

2. The second option, if you embed the picture is to transform the picture to a bitmap or GIF file to begin with so at least you know how large the picture file is. Try copying and pasting the picture into Word, scaling it to the right size and and 'cut'ing and 'paste special' to select a different format. Bitmap will be big but keep some quality, HTML and GIF are smaller but lose quality and perhaps total number of colors.

To have more control you'll need to use a real graphics application such as GIMP. I thought one might be able to save the picture as a GIF file with a transparent background. However, I've read that this i still a problem as access will take a transparent background and add a background to it.