Ever needed to determine if an Excel Workbook was already open, perhaps a Word document?
The 2 functions below can help you determine the state of a file.
The following returns a plain English description of the state:
I used to use table field captions extensively until I started to run into a weird problem in which I couldn’t reassign a fieldname in a query with a new name. No matter what I did, it always reverted to the field caption. After a lot of testing, I eventually found the link. Delete the link and the SQL Alias worked, put the caption back and again the Alias wouldn’t work?!
It has now become common practice for me, as well as many other professional developers, to delete all the field captions from any database I work on to ensure the flexibility of ALIASes in my queries. As such, I developed the procedure below to quickly delete the captions from all the tables within a database, instead of trying to do this manually.
'---------------------------------------------------------------------------------------
' Procedure : ClearAllCaptions
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Remove all captions from all the fields in all the non-system tables
' because of issues that caption cause, mainly:
' Captions prevent you from being able to assign a new name in a query
' unless you do a calculation with it
' Copyright : The following may be altered and reused as you wish so long as the
' copyright notice is left unchanged (including Author, Website and
' Copyright). It may not be sold/resold or reposted on other sites (links
' back to this site are allowed).
'
' Usage:
' ~~~~~~
' Call ClearAllCaptions
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2012-Jun-19 Initial Release
'---------------------------------------------------------------------------------------
Public Sub ClearAllCaptions()
On Error GoTo Error_Handler
Dim Db As DAO.Database
Dim sPropName As String
Dim fld As Field
Dim iTbls As Integer
Dim iNonSysTbls As Integer
Dim ifldCount As Integer
Set Db = CurrentDb
sPropName = "Caption"
iNonSysTbls = 0
ifldCount = 0
For iTbls = 0 To Db.TableDefs.Count - 1 'Loop through the table collection
If (Db.TableDefs(iTbls).Attributes And dbSystemObject) = 0 Then 'Ensure the table isn't a system table
'we don't want to mess around with them
For Each fld In Db.TableDefs(iTbls).Fields 'Loop through the table fields
fld.Properties.Delete (sPropName) 'Delete any captions
ifldCount = ifldCount + 1
Next fld
iNonSysTbls = iNonSysTbls + 1
End If
Next iTbls
If iTbls > 0 Then
MsgBox "Out of a total of " & iTbls & " tables in the current database, " & _
iNonSysTbls & " non-system tables " & _
" were processed, in which a total of " & ifldCount & " fields " & _
"had their '" & sPropName & "' property deleted."
End If
Error_Handler_Exit:
On Error Resume Next
Set Db = Nothing
Exit Sub
Error_Handler:
If Err.Number = 3265 Then
Resume Next
Else
MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: ClearAllCaptions" & vbCrLf & _
"Error Description: " & Err.Description, _
vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End If
End Sub
After a little while to cool off from my last visit to Mr Lube, I thought I should perhaps give them a second shot. Maybe my last visit was a typical…
So I went this morning to get my oil changed.
Pros of my most recent visit:
Cons:
At the end of the day, I went there for an oil change, so the fact that my tire pressure wasn't actually checked isn't the end of the world. I just don't like it being indicated that it was performed when it wasn't and then it make me wonder about everything else they supposedly are doing (including the oil change itself).
Also, the price for a simple oil change was 51.24$+taxes which came out to 58.91$. When you compare that to going to say Canadian Tire, where it would cost you 36.14$, there is a 22.77$ (63%) difference in price for the same service. Actually at Canadian Tire you get a free inspection of your car at the same time.
You make up your own mind. For an oil change it is one thing. They are fast, courteous and you don't have to wait around. Beyond that, I personally would go elsewhere for absolutely anything else and do not ever buy any of there upsell services or recommendations. Instead, go to your mechanic!
I was looking for a simple way to reproduce a web style expandable sidebar (accordian subform, expandable subform, sliding subform, shutter subform, or whatever name you’d like to use to describe it in MS Access), instead of merely making a subform visible/invisible. The attached file, does exactly that and with a single change to a constant variable you can control the speed of the shutter/slider.
This is a very crude example to illustrate the principle. You can use any subform you like, within any form you’d like. There are no requirements to make this work and all the code is native MS Access code, no external references, or ActiveX Controls. The button used to expand and retract the subform can also be changed as you see fit, it is just a question of placing the brief code behind whatever button you select to use in your design.
The following VBA function enables you to read files, for instance a text file, into memory (pass the read file’s content to a variable for instance) to further use within your application.
Instead of reading each line, one by one, the following approach reads the entire file in one go. So this should be faster than other methods.
'---------------------------------------------------------------------------------------
' Procedure : ReadFile
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Read (text) file all into memory in a single shot rather than line by line
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFile : Full path and filename of the file that is to be read
'
' Usage:
' ~~~~~~
' MyTxt = ReadFile("c:\tmp\test.txt")
' MyTxt = ReadFile("c:\tmp\test.sql")
' MyTxt = ReadFile("c:\tmp\test.csv")
'
' Revision History:
' Rev Date(yyyy-mm-dd) Description
' **************************************************************************************
' 1 2012-05-14 Initial Public Release
' 2 2021-09-25 Updated Header
' Updated Error Handler
' Code Cleanup
'---------------------------------------------------------------------------------------
Function ReadFile(ByVal sFile As String) As String
On Error GoTo Error_Handler
Dim iFileNumber As Integer
Dim sFileContent As String
iFileNumber = FreeFile
Open sFile For Binary Access Read As iFileNumber
sFileContent = Space(LOF(iFileNumber))
Get #iFileNumber, , sFileContent
Close iFileNumber
ReadFile = sFileContent
Error_Handler_Exit:
On Error Resume Next
Exit Function
Error_Handler:
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: ReadFile" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Function
The above can be used with any text file type, so: txt, dat, csv, ini, … as illustrated in the Usage section of the function header.
I have done further improvements to this function and further testing, so be sure to read all about it by referring to:
I was asked in a support forum how to convert an Excel *.xls, *.xlsx file to *.csv format. I didn’t readily have an answer so I created a custom function to help the user out. So here are the fruits on my labors should it help someone else out. What is also nice about the way it is written, is that it will run in any MS Office application (MS Access, MS Word, MS PowerPoint, MS Outlook, …) without requiring any modifications (copy & paste, that’s it)!
'---------------------------------------------------------------------------------------
' Procedure : ConvertXls2CSV
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Converts a standard Excel file to csv format
' Requirements: Requires MS Excel be installed
' Uses late binding, so no libraries need be declared
' Copyright : The following may be altered and reused as you wish so long as the
' copyright notice is left unchanged (including Author, Website and
' Copyright). It may not be sold/resold or reposted on other sites (links
' back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sXlsFile : Fully qualified path and filename with extension of the Excel workbook
'
' Usage:
' ~~~~~~
' ConvertXls2CSV "C:\Users\Daniel\Desktop\Contact_E-mail listing.xls"
' Will output a file C:\Users\Daniel\Desktop\Contact_E-mail listing.csv
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2012-May-11 Initial Release - Answer to forum question
'---------------------------------------------------------------------------------------
Function ConvertXls2CSV(sXlsFile As String)
On Error Resume Next
Dim oExcel As Object
Dim oExcelWrkBk As Object
Dim bExcelOpened As Boolean 'Was Excel already open or not
'Review 'XlFileFormat Enumeration' for more formats
Const xlCSVWindows = 23 'Windows CSV Format
Const xlCSV = 6 'CSV
Const xlCSVMac = 22 'Macintosh CSV
Const xlCSVMSDOS = 24 'MSDOS CSV
Set oExcel = GetObject(, "Excel.Application") 'Bind to existing instance of Excel
If Err.Number <> 0 Then 'Could not get instance of Excel, so create a new one
Err.Clear
On Error GoTo Error_Handler
Set oExcel = CreateObject("excel.application")
bExcelOpened = False
Else 'Excel was already running
bExcelOpened = True
End If
On Error GoTo Error_Handler
oExcel.ScreenUpdating = False
oExcel.Visible = False 'Keep Excel hidden from the user
oExcel.Application.DisplayAlerts = False
Set oExcelWrkBk = oExcel.Workbooks.Open(sXlsFile)
'Note: you may wish to change the file format constant for another type declared
' above based on your usage/needs in the following line.
oExcelWrkBk.SaveAs Left(sXlsFile, InStrRev(sXlsFile, ".")) & "csv", xlCSVWindows
oExcelWrkBk.Close False
If bExcelOpened = False Then
oExcel.Quit
End If
Error_Handler_Exit:
On Error Resume Next
Set oExcelWrkBk = Nothing
Set oExcel = Nothing
Exit Function
Error_Handler:
MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: ConvertXls2CSV" & vbCrLf & _
"Error Description: " & Err.Description, _
vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Function
Now this could be further improved by extending the error handling further to trap specific errors such as 1004 – file not found, etc… but it definitely illustrates the basic principle in using late binding to utilize Excel to open the file and convert it to *.csv format.
I hope this helps.
BNI, in the simplest terms, is an organization to help business owners network and promote their business to other local business owners. The premise is that by networking with your fellow BNI business owners, they will then be able to promote your business to their family, friends, business clients, …
They are similar to numerous other groups, amongst others, your local Chamber of Commerce. The key differences from other networking groups are:
On its face, this is a great concept. By having only one business per business category in each chapter, you ensure that you have no competition and thus all the members should privilege you for all their referrals within your category.
This is great in theory. In my experience, this turns out not to be the reality. This sacred BNI rule, is overlooked as long as business is being made!
As a member, at the weekly meetings, you are expected to give qualified referrals to your fellow members. Expected may be a little weak a term, because if you do not meet a minimal (monthly) level of production, you will be kicked out of the group (and you are not reimbursed your membership fees).
This mandatory production is solely based on quantity and not quality. Thus, the individual generating a $15.00 referral every week for the florist in the group will be in good standing within the group. However, the individual who only gives 2 referral totaling $12,000.00 would be kicked out. So it is all a numbers game at the end of the day. So what you end up seeing is either bogus referrals given just to generate numbers for the individual or partnership created between 2 or 3 member who pass referrals back and forth week after week.
Having risen to the highest post within a BNI chapter, as the vice-president (the president hold no real power as is merely a figure head) I will say, in my humble opinion, BNI is a business networking group like any other. Do not go into this group thinking people there are your friends. They may be very friendly, may say nice things to your face, but if they can make a quick buck by going behind your back, lying to you, they will. These are business people, and typically, all they care about is $$$. Just don’t think any otherwise. As long as you go into this group without any premade expectation, it may be beneficial to you.
They always promote the line “Giver Gain”, but the reality that I experienced seems to disagree with the supposed fundamental philosophy. Members care only about you to the extent that you give them referrals. Most chapters have clicks within them, and you irk the wrong member, you may be out before you were ever in. But then again, I guess this is sadly true of almost any group.
I was personally very disappointed with regional management here in Montreal, in the sense that they didn’t uphold their own rules at the end of the day. I found the rule don’t apply, or are greatly overlooked, if money is being made.
I know several people that have prospered within BNI. I also know countless people that didn’t get a single referral, or didn’t get enough to recoup their membership fees little alone actually prosper from their membership. Some of it has to do with the individual, how outgoing and social they are, and some of it has to do with product. Most members are small business people, with little to no technical knowledge. They are incapable (typically – there can be a few exceptions however) of providing referrals to large business, or niche markets. They can however help promote businesses whose services and products target the average person! This is why people such as, insurance brokers, accountants, massage therapist, … can do very well in these groups because they can easily be referred to almost anyone. However, a software developer is a very hard referral and would probably do better focusing their marketing energies elsewhere. In my opinion and experience, a company primarily focused on B2B is better looking elsewhere.
Note that BNI requires a substantial investment of time. You are only allowed to miss 2 meetings a year. Attendance in mandatory, as is production. Meetings last a good 2 hours and then you are required to have weekly meetings with your fellow members, special events, and if you accept to take a role within the chapter you can easily take up 1-2 days a week. Some people say that BNI will have a return of 55000$/yr. I do not know where they get such numbers! I can state that in 2.5 years, I netted under 3000$. Deduct the annual fees, meal costs, cost of the commute, … and I was making less than a laborer in some third world nation.
I will also state that a colleague of mine, instead of joining a specific BNI chapter, decided to jump from chapter to chapter, as a visiting guest, week after week. Not only did it not cost them a penny to join a chapter, this approach landed them a lot more business then I ever did as a member for 2.5+ years. They got a lot more exposure because instead of being isolated to just those businesses within a single chapter, every week they met new business people in a new chapter. So think hard before joining a Chapter!
Similarily to my post regarding exporting records to MS Excel, below is some sample code that illustrates how one can export data into a new Word document (in a table structure). The code determines the necessary rows and columns based on the table or query passed to it and then does the rest. You can easily from this simple example get into formatting fonts, etc…
'---------------------------------------------------------------------------------------
' Procedure : Export2DOC
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Export a recordset to a MS Word table in a new document
' Note : Overwrites file if it already exists without any warning, so you may wish
' to add a check prior to calling this function
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Uses Late Binding, none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sQuery : Name of the Table or Query to Export the data from
' sFileName : Path and Filename to save the Word Document as
' bOpenDocument : Leave the document open to the users or not (close after generating)
'
' Usage:
' ~~~~~~
' Export2DOC "Contacts", "C:\Temp\testing01.docx"
' Export2DOC "IncomingOrders", "C:\Temp\orders.docx", True
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2012-Apr-23 Initial Release
' 2 2020-04-08 Updated proc header
' Added sFileName and bOpenDocument arguments
' Added PageSetup.Orientation to proc
' Fixed a bug in the table row count
' Updated and expanded the error handler
'---------------------------------------------------------------------------------------
Function Export2DOC(sQuery As String, _
sFileName As String, _
Optional bOpenDocument As Boolean = False)
Dim oWord As Object
Dim oWordDoc As Object
Dim oWordTbl As Object
Dim bWordOpened As Boolean
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim iCols As Integer
Dim iRecCount As Integer
Dim iFldCount As Integer
Dim i As Integer
Dim j As Integer
Const wdPrintView = 3
Const wdWord9TableBehavior = 1
Const wdAutoFitFixed = 0
' Const wdOrientPortrait = 0
Const wdOrientLandscape = 1
'Start Word
On Error Resume Next
Set oWord = GetObject("Word.Application") 'Bind to existing instance of Word
If Err.Number <> 0 Then 'Could not get instance of Word, so create a new one
Err.Clear
On Error GoTo Error_Handler
Set oWord = CreateObject("Word.application")
bWordOpened = False
Else 'Word was already running
bWordOpened = True
End If
On Error GoTo Error_Handler
oWord.Visible = False 'Keep Word hidden until we are done with our manipulation
Set oWordDoc = oWord.Documents.Add 'Start a new document
'Not strictly necessary, but for larger tables switching to Landscape can be very beneficial!
' so the next line demonstrates how that can easily be done
oWordDoc.PageSetup.Orientation = wdOrientLandscape
'Open our SQL Statement, Table, Query
Set db = CurrentDb
Set rs = db.OpenRecordset(sQuery, dbOpenSnapshot)
With rs
If .RecordCount <> 0 Then
.MoveLast 'Ensure proper count
iRecCount = .RecordCount 'Number of records returned by the table/query
.MoveFirst
iFldCount = .Fields.Count 'Number of fields/columns returned by the table/query
'Switch to print preview mode (not req'd just a personal preference)
oWord.ActiveWindow.View.Type = wdPrintView
'Create the basic table
oWord.ActiveDocument.Tables.Add oWord.selection.Range, _
iRecCount + 1, _
iFldCount, _
wdWord9TableBehavior, _
wdAutoFitFixed
Set oWordTbl = oWordDoc.Tables(1)
'Build our Header Row
For i = 0 To iFldCount - 1
oWordTbl.Cell(1, i + 1) = rs.Fields(i).Name
Next i
'Build our data rows
For i = 1 To iRecCount
For j = 0 To iFldCount - 1
oWordTbl.Cell(i + 1, j + 1) = Nz(rs.Fields(j).Value, "")
Next j
.MoveNext
Next i
Else
MsgBox "There are no records returned by the specified queries/SQL statement.", _
vbCritical + vbOKOnly, "No data to generate an Word spreadsheet with"
GoTo Error_Handler_Exit
End If
End With
oWordDoc.SaveAs (sFileName) 'Save and close
If bOpenDocument = False Then
oWordDoc.Close
' Close Word if is wasn't originally running
If bWordOpened = False Then
oWord.Quit
End If
End If
Error_Handler_Exit:
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing
oWord.Visible = True 'Make Word visible to the user
Set oWordTbl = Nothing
Set oWordDoc = Nothing
Set oWord = Nothing
Exit Function
Error_Handler:
If Err.Number = 5148 Then
MsgBox "Your Table/Query contains a total of " & iFldCount & " fields/columns, but Word tables can only support a maximum of 63. " & _
"Please change your Table/Query to only supply a maximum of 63 fields/columns and try again.", _
vbCritical Or vbOKOnly, "Operation Aborted"
Else
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: Export2DOC" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
End If
Resume Error_Handler_Exit
End Function
You may also wish to review my MS Access Sample- Export Data to Excel and/or Word.
Although there are a couple buit-in technique for exporting table & query records to Excel, mainly , if one wants to control the process a little more (edit fonts, print areas, etc) you need to create your own custom procedure to do so. Below is a simple example to get you started in the right direction. This example uses late binding techniques and thus does not require any libraries to work!
'---------------------------------------------------------------------------------------
' Procedure : Export2XLS
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Export recordset to Excel
' Copyright : The following may be altered and reused as you wish so long as the
' copyright notice is left unchanged (including Author, Website and
' Copyright). It may not be sold/resold or reposted on other sites (links
' back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sQuery : Name of the table, or SQL Statement to be used to export the records
' to Excel
'
' Usage:
' ~~~~~~
' Export2XLS "qryCustomers"
' Call Export2XLS("qryCustomers")
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2012-Apr-18 Initial Release
' 2 2015-May-01 Header Clarifications
'---------------------------------------------------------------------------------------
Function Export2XLS(ByVal sQuery As String)
Dim oExcel As Object
Dim oExcelWrkBk As Object
Dim oExcelWrSht As Object
Dim bExcelOpened As Boolean
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim iCols As Integer
Const xlCenter = -4108
'Start Excel
On Error Resume Next
Set oExcel = GetObject(, "Excel.Application") 'Bind to existing instance of Excel
If Err.Number <> 0 Then 'Could not get instance of Excel, so create a new one
Err.Clear
On Error GoTo Error_Handler
Set oExcel = CreateObject("Excel.Application")
bExcelOpened = False
Else 'Excel was already running
bExcelOpened = True
End If
On Error GoTo Error_Handler
oExcel.ScreenUpdating = False
oExcel.Visible = False 'Keep Excel hidden until we are done with our manipulation
Set oExcelWrkBk = oExcel.Workbooks.Add() 'Start a new workbook
Set oExcelWrSht = oExcelWrkBk.Sheets(1)
'Open our SQL Statement, Table, Query
Set db = CurrentDb
Set rs = db.OpenRecordset(sQuery, dbOpenSnapshot)
With rs
If .RecordCount <> 0 Then
'Build our Header
For iCols = 0 To rs.Fields.Count - 1
oExcelWrSht.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
Next
With oExcelWrSht.Range(oExcelWrSht.Cells(1, 1), _
oExcelWrSht.Cells(1, rs.Fields.Count))
.Font.Bold = True
.Font.ColorIndex = 2
.Interior.ColorIndex = 1
.HorizontalAlignment = xlCenter
End With
'Copy the data from our query into Excel
oExcelWrSht.Range("A2").CopyFromRecordset rs
oExcelWrSht.Range(oExcelWrSht.Cells(1, 1), _
oExcelWrSht.Cells(1, rs.Fields.Count)).Columns.AutoFit 'Resize our Columns based on the headings
oExcelWrSht.Range("A1").Select 'Return to the top of the page
Else
MsgBox "There are no records returned by the specified queries/SQL statement.", vbCritical + vbOKOnly, "No data to generate an Excel spreadsheet with"
GoTo Error_Handler_Exit
End If
End With
' oExcelWrkBk.Close True, sFileName 'Save and close the generated workbook
' 'Close excel if is wasn't originally running
' If bExcelOpened = False Then
' oExcel.Quit
' End If
Error_Handler_Exit:
On Error Resume Next
oExcel.Visible = True 'Make excel visible to the user
rs.Close
Set rs = Nothing
Set db = Nothing
Set oExcelWrSht = Nothing
Set oExcelWrkBk = Nothing
oExcel.ScreenUpdating = True
Set oExcel = Nothing
Exit Function
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: Export2XLS" & vbCrLf & _
"Error Description: " & Err.Description _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Function
You may also wish to review my MS Access Sample- Export Data to Excel and/or Word
As a developer, it becomes necessary to decompile your database from time to time. The frequency depends on a multitude of factors, but at the very least, one should decompile one’s application prior to release. Decompilation is yet one more step a develpper has to take to ensure their database/code is clean and optimal for their end-users. Also, keep in mind that the best approach is to decompile the database on the development machine and then recompile on the end-user machine. This ensures that the database is compiled using the end-user’s libraries minimizing any surprises when put into production.
Before going any further on this subject, let me emphasize once more the importance of making a backup of your database prior to performing a decompile of your database!
The MSACCESS.EXE command line accepts several command line switches, one of which is to allow decompiling your database. There is no other means to decompile a database. So one merely needs to create a shortcut including the appropriate command line switch in order to decompile any given database. The basic syntax would be:
"FullPath\MSACCESS.EXE" "FullPathAndDbNameWithExtension" /decompile
Examples:
"C:\Program Files\Microsoft Office\Office\MSACESS.EXE" "C:\Databases\Test\Database1.mdb" /decompile
Or
"C:\Program Files (x86)\Microsoft Office\Office12\MSACESS.EXE" "C:\Databases\Test\Database1.mdb" /decompile
On the other hand, it become tedious to create a shortcut for each database you create/manage and as such a more automated method may be a good idea to implement.
"C:\Program Files (x86)\Microsoft Office\Office12\MSACCESS.EXE" /decompile
Or
"C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE" /decompile
Now whenever you wish to decompile a database you need only right-click on the Access database in Windows Explorer and select “Send To –> MSACCESS Decompile.
Since after decompiling we always perform a compact of the database, you can perform both in one step if you’d like by appending a /compact to your /decompile command. This is not obligatory by any means. Personally, I prefer the granularity of performing each step myself, but should you wish to automate things a little more you’d do something along the lines of:
"C:\Program Files\Microsoft Office\Office\MSACESS.EXE" "C:\Databases\Test\Database1.mdb" /decompile /compact
Or
"C:\Program Files (x86)\Microsoft Office\Office12\MSACESS.EXE" "C:\Databases\Test\Database1.mdb" /decompile /compact
"C:\Program Files (x86)\Microsoft Office\Office12\MSACCESS.EXE" /decompile /compact
Or
"C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE" /decompile /compact
Do note that if you do implement this variation for the reusable decompilation method, you’d probably be best to also change the name attributed to the command in step 4. to MS Access Decompile and Compact
I found the following instructions from David Fenton, and thought they fit right into this subject.
Why are all these steps necessary?
Because you want to not just decompile the VBA, you want to make sure that all the data pages where the compiled p-code was stored are completely discarded before you recompile.
I also recommend:
Another good resource on the subject is Michael Kaplan’s article on the subject.
Lastly, instead of creating various shortcut to perform these actions, you may prefer to use my utility that will add such functionalities directly into the Windows Context Menu. To learn more check out Windows Explorer MS Access Database Right-Click Context Menu.