Visual Basic 6.0 on Windows Vista

The Visual Basic team is committed to "It Just Works" compatibility for Visual Basic 6.0 applications on Windows® Vista™.

The Access Dashboard

The Access Dashboard launches multiple versions of MS Access plus allows you to Backup, Repair, Compact, and Decompile database files. Free!

Cascade to Null in Jet

Allen Browne, Access MVP, explains Cascade to Null Relations feature in Access (Jet), where related records can be automatically set to Null rather than deleted when the primary record is deleted.

BTW, looks like this is a first explanation of this feature, at least Google does not know any other.

Does "Export" equals "Import from opposite side"?

Just saw, that Tony D'Ambra offers Ezy Exporter Premium Edition, which exports Access object in bulk. And I wondering – why can’t we just use Access import feature to do the same? Did I miss something?
:-)

Protection from MDE recovery

iTech Masters, which offers reverse engineering service MDE to MDB, now offering new utility - MDE Source Code Protector v1.02 for Microsoft® Access, which helps you to protect MDE better. MDE still can be reverse engineered, but now it will be much more complicated task to read a source code. Utility removes “compiler junk” from MDE, so all variable names get converted to Variable1, Variable2, etc., and variable types become Variant or Object, so at the end you get much less understandable code!
FMS Inc offer a similar solution - Total Visual CodeTools, but there you have to modify source code in MDB, before compiling it to MDE, and looks like MDE Source Code Protector is more convenient solution.

Export to Excel range

Ken Snell, Access MVP, has made some investigations on exporting data from Access to Excel spreadsheet, specifying range argument. He was very kind and allowed me to post results here. Thank you Ken!

BTW – Access help states that this Range argument is only for importing, and for export it will fail. Thanks to Bill Mosca, Access MVP, who mentioned this in our discussin!

Ok, here a story from Ken:

Having read some posts in newsgroups about using the Range argument for exporting queries/tables to EXCEL file, I decided to do some testing today to figure out what actually works and what doesn't work when using this argument (note that this use is an undocumented feature in ACCESS).
Here are the results of my tests for others' info/entertainment.

EXCEL FILE DOES NOT ALREADY EXIST
If the EXCEL file will be created by TransferSpreadsheet, the Range argument can be used to create a range in the new file that describes the cells that contain the exported data on the worksheet. This Range argument also is used to name the worksheet onto which the exported data are written. This overrides the normal operation of TransferSpreadsheet, which is to name the worksheet using the name of the table or query being exported. For example, this action:
DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel9, "QueryName", _
"C:\Test.xls",, "MyName"

will create the file "C:\Test.xls" and the data will be written onto the first worksheet, which will be named MyName (the Worksheet.Name property, not the Worksheet.CodeName property); and the cells into which the data are written will be a Range named MyName in the new file. This range will include the field names that are exported as the first row of data, and the range will begin in cell A1.

EXCEL FILE ALREADY EXISTS
The Range argument can be used to identify the actual Range into which the exported data are written. TransferSpreadsheet ignores worksheet names when looking for the Range in the workbook file. It looks specifically for a defined Range of cells.
However, it is in this situation where I believe many posters have run into problems with this undocumented feature.
If the Range exists (cell range, that is) AND if that range encompasses more than a single cell (at least two cells), the data are exported to that range of cells. If the number of records and/or fields are more or fewer than the "size" of the range (number of rows and columns), the data are correctly exported and the Range is redefined to match the size of the exported data in terms of width and depth of the range (number of rows and number of columns). Note that any formatting in the cells within this range is retained (e.g., Bold, Highlight color, font color, etc.).

1. If the Range does not exist in the workbook file, TransferSpreadsheet creates a new worksheet, names it with the Range argument value, writes the data onto that worksheet, and creates a new Range (also named with the Range argument value)to define the cells that contain the exported data. If a worksheet with the same name as what is in the Range argument already exists in the workbook file, the new worksheet that is created is named using standard EXCEL process, namely, the Range argument name followed by a 1. Thus, if I use MyName as the Range argument and export to an existing file, I can get one of the following results:

• File already contains a worksheet named MyName but does not contain a Range named MyName: A new worksheet named MyName1 is created, the data are written onto that worksheet, and a new Range named MyName is defined for the cells that received those exported data.


• File does not contain a worksheet named MyName and does not contain a Range named MyName: A new worksheet named MyName is created, the data are written onto that worksheet, and a new Range named MyName is defined for the cells that received those exported data.

2. If the Range exists (cell range, that is) AND if the Range consists of a single cell (e.g., A1), then strange things happen -- note that it doesn't matter if the Range starts in cell A1 or not. And because of these strange things, this is where the feature is unusable for exporting. I haven't defined exact "rules" to describe what happens (although it appears that how far the range is moved appears to be "the original row number plus 93" columns (if the Range was originally in column A), but here are my observations in this situation (I won't guarantee that you won't see different behaviors):

• If the worksheet name is the same name as the Range name, and the Range begins in cell A1, the exported data are written to the worksheet that contains the Range specified in the TransferSpreadsheet action, and these data begin at cell A1 (with the field names row) -- BUT the existing range is moved to cell CQ1 (94 columns to the right), and there is no Range created for the cells that contain the exported data. Any further attempt to export to this worksheet using the same Range argument generates an error because the "move" of the range will extend beyond the column limit of the worksheet.


• If the worksheet name is the same name as the Range name, and the Range begins in cell A5, the exported data are written to the worksheet that contains the Range specified in the TransferSpreadsheet action, and these data begin at cell E5 (with the field names row) -- BUT the existing range is moved to cell CU5 (98 columns to the right), and there is no Range created for the cells that contain the exported data. Any further attempt to export to this worksheet using the same Range argument generates an error because the "move" of the range will extend beyond the column limit of the worksheet.


• If the worksheet name is not the same as the Range name, and the Range begins in cell A1, the exported data are written to a new worksheet that is named the same as the Range argument value, and the existing Range is then moved to cell IV1 (the last column in the sheet) on that new worksheet, and there is no Range created for the cells that contain the exported data.


• If the worksheet name is not the same as the Range name, and the Range begins in cell A30, the exported data are written to the existing worksheet that contains the named Range but are written into the cell block where the left top anchor is cell AD150 (29 columns to the right), the existing Range is then moved to cell DT30 on that worksheet (123 columns to the right), and there is no Range created for the cells that contain the exported data.


• If the worksheet name is not the same as the Range name, and the Range begins in cell A150, the exported data are written to the existing worksheet that contains the named Range but are written into the cell block where the left top anchor is cell ET150 (149 columns to the right), the existing Range is then moved to cell IJ150 on that worksheet (243 columns to the right, and there is no Range created for the cells that contain the exported data.


• If the worksheet name is not the same as the Range name, and the Range begins in any column except column A (e.g., the Range is defined as cell B1), an error occurs because the "move" of the range will extend beyond the column limit of the worksheet, and no data are exported.

Update: This article on Japanese, thanks to Yu Tang!

AccHelp

AccHelp automatically makes HTML Help and Win Help files for Microsoft Access Applications. Actually it creates a basic skeleton, which you then use as a start of your help file. You also have to assign Help Context Id manually before start using addin.