For the past two days I have been working with MS Access to create an application. One of the features I wanted to create, was a parametric query that would take the value from a combobox on a form and create a report.
To be more precise I had a form with a combobox populate with IDs and names and when the user selected a specific item from the combo and clicked on a button I wanted to have a report that showed some information of related to the selected ID.
One way to do it is to create some vb code and create a select query with a “where” clause. But, that is not too flexible, caz if I want to change the query, I have to change the code.
Today I came across a more flexible solution. Parametric queries. I knew how to use parametric queries, but not in conjunction with forms. So if you want to create a parametric query related to a form, all you have to do in the query is define the parameter at the beginning like:
PARAMETERS [Forms]![formName]![ControlName] ParameterType;
In the where clause you also have to use the same notation. So for example:
where Id = [Forms]![formName]![ControlName]
and that’s it!
I was trying to convert an MS Access database I had developed to MDE. It was crashing every time I was trying to create the MDE file.
After some search on the internet I run across a Microsoft article that solved the problem for me.
The link is: http://support.microsoft.com/Default.aspx?scid=kb;en-us;814858&spid=2509&sid=98
What it says is:
To work around this problem, you can reload the VBA project of the Access database from text and then create the MDE file or the ADE file. To do this, follow these steps:
Note Make a backup copy of the database before you start these steps.
1. On the taskbar, click start and then click Run.
2. Type msaccess.exe /decompile and then click OK.
3. Open the original .mdb file or the original .adp file that you want to save as the new MDE file or the new ADE file.
a. Press ALT+F11 to open the Visual Basic Editor.
b. On the Debug menu, click Compile <databasename>.
c. On the File menu click Save <databasename>, and then close the Visual Basic Editor.
4. On the Tools menu, click Database Utilities and then click Make MDE File or click Make ADE File.
5. In the Save MDE As dialog box or the Save ADE As dialog box, locate the folder where you want to save the MDE file or the ADE file, type the file name in the File name box, and then click Save.<databasename>
I tried it and it and it works like a charm! Apparently I had deleted some forms and there was some VBA code related to those forms that was still there. With the decompile option not only did I manage to create an MDE file, but I also reduced the size of the db by almost 1MB(!!) after I compacted it.
From now on I am going to use the /decompile option every time before I deploy a new version of access.