Just had a weird moment in Access land.
I have two forms that have been in use for a couple of years, suddenly they will not display records as I expected them to.
I was setting the RecordSource property using an SQL string which was working a couple of hours ago. But now they are not showing any of the records. However, if I put the RecordType to Snapshot... tada... all the records show up as expected.
After reading some forums, I found the solution here
http://bytes.com/topic/access/answers/763599-form-does-not-show-records-dynaset-mode-only-snapshot
The thing that is bothering me the most, is how did this come to be a bug I am only just finding? Have the properties been reset somehow? Has a patch fixed something? I swear it used to work as expected... now...weird.
Anyway, I have updated my codebase to explicitly set the DataEntry property to false when I am "reviewing" records and it seems to work fine. I just have to figure out if my users have different ideas about "fine".
Showing posts with label Access. Show all posts
Showing posts with label Access. Show all posts
Friday, February 15, 2013
Tuesday, February 5, 2013
Access 2010 corrupt database "File not found"
I had the unplesant experience of a corrupt database yesterday. After doing some work, suddenly everthing started generating a "File not Found" error. Clicking buttons, using the ribbon bar, trying to run any macros. My database was fucked.
I tracked it back to a module that I had added... put a little bit of code in and then deleted earlier. When I closed and opened the database the module still showed up in the tree in the VBA editor, but seemed to be "Empty" as every time I tried to open it... it flicked up the "File not found" error. All the other modules worked as normal.
Anyway, after trying to delete it, over-write it, modify its name etc... I gave up and created a new database, imported everything from the old one and got moving. The steps are:
NOTE. Make a list of the "References" in the VBA editor for your database at this point. (See below for more details to avoid getting screwed when you close the corrupt database. Just write them on a piece of paper or something low tech. They cannot be exported.)
1) Create a new database
2) In the new database go to "External Data" > Access
3) Select "Import tables, Queries, forms, reports, macros, and modules into the current database. Select the old database file using the browse button and then click the OK button.
4) You will get a mega select dialog where you can select all the objects you want to import. Generally you will want to use the "Select All" button on each of the tabs to make sure you get "Everything" from your old database. Make sure you do not have the "Corrupt" item selected... if you are not sure what is corrupt... take it in a couple of bites and test the new database between bites.
5) Once the import has finished and you save the new database, you may still need to "Wire up" a couple of things in the new database. (Go to File > Options > Current Database) The things I had to setup were:
Application Options Section
* Application Title
* Application Icon
* Display Form
Ribbon and Toolbar Options
* Ribbon Name
These are just the options for my "Development" database. I turn off the navigation pane and the Default ribbon before I deploy the database to the users.
Hope this helps someone.
In the Visual Basic Editor, I has to recreate all the References. I had to open the old database and make a list of everthing and then manually add the references to the new database.
Unfortunatly, when I came to try to re-open the old database it was now throwing a much larger error
"The database cannot be opened because the VBA project contained in it cannot be read. The database can be opened only if the VBA project is first deleted. Deleting the VBA project removes all code from modules, forms and reports. You should back up your database before attempting to open the database and delete the VBA project. To create a backup copy, click Cancel and then make a backup copy of your database. To open the database and delete the VBA project without creating a backup copy, click OK." mmmm shit!
Well after making a backup... I opened the Database and deleted the VBA project and found that the references had gone with it..... fuckcicle!
I tried going back to one of the production copies but they have been compiled and the VBA project will not show the references.... fucked again.
Don't bother trying to decompile a previous version either:
http://stackoverflow.com/questions/3266542/ms-access-how-to-decompile-and-recompile
When I try this it gives me the error message "The VBA project in the datbase is corrupt". Basically when it was compiled, it was stripped of all the symbols etc and they cannot be re-created.
Fucked again.
So my options are to "discover" the references that I need by trial and error. I remember I had about 8-10 references, so it should not be tooooo hard. I think the calendar control was the worst to find.
Where you have used early binding in your VBA you can discover missing references simply by running Debug > Compile Database. This will highlight any types that you are using that have not been defined. You can then search the net for which object library contains that type and references it correctly.
Bugger... just have to test everything in the damn database again.
I tracked it back to a module that I had added... put a little bit of code in and then deleted earlier. When I closed and opened the database the module still showed up in the tree in the VBA editor, but seemed to be "Empty" as every time I tried to open it... it flicked up the "File not found" error. All the other modules worked as normal.
Anyway, after trying to delete it, over-write it, modify its name etc... I gave up and created a new database, imported everything from the old one and got moving. The steps are:
NOTE. Make a list of the "References" in the VBA editor for your database at this point. (See below for more details to avoid getting screwed when you close the corrupt database. Just write them on a piece of paper or something low tech. They cannot be exported.)
1) Create a new database
2) In the new database go to "External Data" > Access
3) Select "Import tables, Queries, forms, reports, macros, and modules into the current database. Select the old database file using the browse button and then click the OK button.
4) You will get a mega select dialog where you can select all the objects you want to import. Generally you will want to use the "Select All" button on each of the tabs to make sure you get "Everything" from your old database. Make sure you do not have the "Corrupt" item selected... if you are not sure what is corrupt... take it in a couple of bites and test the new database between bites.
5) Once the import has finished and you save the new database, you may still need to "Wire up" a couple of things in the new database. (Go to File > Options > Current Database) The things I had to setup were:
Application Options Section
* Application Title
* Application Icon
* Display Form
Ribbon and Toolbar Options
* Ribbon Name
These are just the options for my "Development" database. I turn off the navigation pane and the Default ribbon before I deploy the database to the users.
Hope this helps someone.
In the Visual Basic Editor, I has to recreate all the References. I had to open the old database and make a list of everthing and then manually add the references to the new database.
Unfortunatly, when I came to try to re-open the old database it was now throwing a much larger error
"The database cannot be opened because the VBA project contained in it cannot be read. The database can be opened only if the VBA project is first deleted. Deleting the VBA project removes all code from modules, forms and reports. You should back up your database before attempting to open the database and delete the VBA project. To create a backup copy, click Cancel and then make a backup copy of your database. To open the database and delete the VBA project without creating a backup copy, click OK." mmmm shit!
Well after making a backup... I opened the Database and deleted the VBA project and found that the references had gone with it..... fuckcicle!
I tried going back to one of the production copies but they have been compiled and the VBA project will not show the references.... fucked again.
Don't bother trying to decompile a previous version either:
http://stackoverflow.com/questions/3266542/ms-access-how-to-decompile-and-recompile
When I try this it gives me the error message "The VBA project in the datbase is corrupt". Basically when it was compiled, it was stripped of all the symbols etc and they cannot be re-created.
Fucked again.
So my options are to "discover" the references that I need by trial and error. I remember I had about 8-10 references, so it should not be tooooo hard. I think the calendar control was the worst to find.
Where you have used early binding in your VBA you can discover missing references simply by running Debug > Compile Database. This will highlight any types that you are using that have not been defined. You can then search the net for which object library contains that type and references it correctly.
Bugger... just have to test everything in the damn database again.
Tuesday, November 22, 2011
How to add line breaks to ControlTip Text in Access
Whats the problem?
If you've ever tried to add line breaks or formatting to the ControlTip Text in a control in an Access project you will have noticed that you can add anything to the ControlTip Text property field and it will get put on a single line. I.e...
You can add any sort of tricky string to the property dialog box and get no useful result.
If you have spent any time thinking about property dialog programming you should already see whats going on... input validation. The code behind the property dialog is "sanitizing" your input to prevent you injecting control characters, illegal characters and other types of idiot/malicious code. This is a good thing... except when you want to do something like this intentionally.
Whats the solution?
The above description helped me identify the solution... avoid the input validation behind the property dialog rather than try to bend it to my will. The hack is to add the formatted string for the ControlTip Text via code. I use the Form_Load sub to set tricky control tip text for all the controls on the form that I want to have multi-line ControlTip Text.
I.e
Private Sub Form_Load()
showTaskManager.ControlTipText = "Display the Batch Task Manager" & vbCrLf & "Some other text here"
End Sub
This will show up as a nicely formatted multi-line ControlTip.
Simple once you see it....
If you've ever tried to add line breaks or formatting to the ControlTip Text in a control in an Access project you will have noticed that you can add anything to the ControlTip Text property field and it will get put on a single line. I.e...
You can add any sort of tricky string to the property dialog box and get no useful result.
If you have spent any time thinking about property dialog programming you should already see whats going on... input validation. The code behind the property dialog is "sanitizing" your input to prevent you injecting control characters, illegal characters and other types of idiot/malicious code. This is a good thing... except when you want to do something like this intentionally.
Whats the solution?
The above description helped me identify the solution... avoid the input validation behind the property dialog rather than try to bend it to my will. The hack is to add the formatted string for the ControlTip Text via code. I use the Form_Load sub to set tricky control tip text for all the controls on the form that I want to have multi-line ControlTip Text.
I.e
Private Sub Form_Load()
showTaskManager.ControlTipText = "Display the Batch Task Manager" & vbCrLf & "Some other text here"
End Sub
This will show up as a nicely formatted multi-line ControlTip.
Thursday, February 10, 2011
Using querydefs and parameter querys in VBA
Today I will be bitching about using parametrized query's from VBA code in a robust way. I will also be presenting a roundup of the solutions as far as I know them.
The Problem
You want to modify a query on the fly using some dynamic option or parameter in VBA and then use that as the RecordSource for a form. Bloody obvious need if one is building a db driven app.
Possible Solutions
The first is to user a parameter query with a field somewhere on a form/global variable that the query can just pick up and run with. This sucks because its inflexible. I need to essentially plant a global somewhere that always exists and always contains the right value... can anyone say spagetti code? Hello 1980.
The second ( variation) is to try to plug the parameter in on-the-fly by massaging the Querydef object and then pulling the Recordset by hand and somehow pushing it into the form or whatever you are trying to use it as a source. This is just fuggly.
The third is to compose the SQL as a string and put the parameter into the string using string functions. Nice, easy and completely unmaintainable using all the nice GUI tools provided in Access. Yes I can do it, but it means I end up with my code littered with SQL fragments that I have to then hand maintain every time something changes in the db schema. what a PITA.
The fourth possible solution I have seen floating around is to build a whole dialog that can compose the SQL on the fly using some combo boxes etc. This is just a variation on solutions 1 and 3. With the worst parts of both. Now you have even more trash to maintain and find when you change anything. The whole point is to reduce complexity not add to it. Blahhhhh.
Fifth possible solution is to roll my own object that somehow can be instantiated, wrap a Querydef object and gracefully update it as required. Since there are no useful hooks to build this around, it gets back to having a global object somewhere and messing with it on demand... back to option 1 but with the overhead of a whole class of code to now maintain.
Main Bitch
What I would much prefer is a nice wrapper object around a Querydef that could be used to create a temporary Querydef with parameter slots, I could then plug in the params, test that the Querydef is stable, and pass it to the form as its Recordsource. This way I can maintain the Querydef using the Querybuilder GUI and all the other test tools, use the QueryDef gracefully from code and not littler my code with all sorts of fragile text string and maintenance headaches. Oh and I want MS to supply this wrapper so I don't need to roll my own. Bastards.
Usually when I find myself banging my head against what seems to be an obvious problem, I usually find that its just that I don't know the "Right way" to do things in that language or framework and that with a little bit of reading I can find it and get my head right (usually refactor a truck load of code) but get back on the true path and start making progress again. However in this case it seems like its a common problem that doesn't really have any particular "right solution" its just lots of ugly hacks. (There are some truly UGLY solutions floating around the forums to this problem. But I don't feel like I have a more pleasing solution so its much of a muchness. Still they offend my eye in ways that indicate they are fragile, hard to maintain and error prone)
Ok head is a bit clearer... bitching complete. I need to go read up on the QueryDef object I think.
Edit:
The best solution I have come up with ( tip of the hat to a forum post I read but can't remember... its probably common knowledge anyway) is to maintain the query in as a normal QueryDef which gives me the ability to use the GUI and compile the SQL for testing; the Query contains a parameter which is essentially a unique key phrase that I then use the "Replace" function to replace with my desired parameter info.
For instance
Dim qdef As QueryDef
'contains a param "[ContID]"
Set qdef = CurrentDb.QueryDefs("ContinuityRelationshipFormSpecificQuery")
Dim theSql As String
theSql = Replace(qdef.sql, "[ContID]", str(relationshipID))
Me.RecordSource = theSql
This is a fairly simple and maintainable system. It takes about four lines to do what I would like to do in less, but its still only a line per parameter so not too horrible a trade-off.
The Problem
You want to modify a query on the fly using some dynamic option or parameter in VBA and then use that as the RecordSource for a form. Bloody obvious need if one is building a db driven app.
Possible Solutions
The first is to user a parameter query with a field somewhere on a form/global variable that the query can just pick up and run with. This sucks because its inflexible. I need to essentially plant a global somewhere that always exists and always contains the right value... can anyone say spagetti code? Hello 1980.
The second ( variation) is to try to plug the parameter in on-the-fly by massaging the Querydef object and then pulling the Recordset by hand and somehow pushing it into the form or whatever you are trying to use it as a source. This is just fuggly.
The third is to compose the SQL as a string and put the parameter into the string using string functions. Nice, easy and completely unmaintainable using all the nice GUI tools provided in Access. Yes I can do it, but it means I end up with my code littered with SQL fragments that I have to then hand maintain every time something changes in the db schema. what a PITA.
The fourth possible solution I have seen floating around is to build a whole dialog that can compose the SQL on the fly using some combo boxes etc. This is just a variation on solutions 1 and 3. With the worst parts of both. Now you have even more trash to maintain and find when you change anything. The whole point is to reduce complexity not add to it. Blahhhhh.
Fifth possible solution is to roll my own object that somehow can be instantiated, wrap a Querydef object and gracefully update it as required. Since there are no useful hooks to build this around, it gets back to having a global object somewhere and messing with it on demand... back to option 1 but with the overhead of a whole class of code to now maintain.
Main Bitch
What I would much prefer is a nice wrapper object around a Querydef that could be used to create a temporary Querydef with parameter slots, I could then plug in the params, test that the Querydef is stable, and pass it to the form as its Recordsource. This way I can maintain the Querydef using the Querybuilder GUI and all the other test tools, use the QueryDef gracefully from code and not littler my code with all sorts of fragile text string and maintenance headaches. Oh and I want MS to supply this wrapper so I don't need to roll my own. Bastards.
Usually when I find myself banging my head against what seems to be an obvious problem, I usually find that its just that I don't know the "Right way" to do things in that language or framework and that with a little bit of reading I can find it and get my head right (usually refactor a truck load of code) but get back on the true path and start making progress again. However in this case it seems like its a common problem that doesn't really have any particular "right solution" its just lots of ugly hacks. (There are some truly UGLY solutions floating around the forums to this problem. But I don't feel like I have a more pleasing solution so its much of a muchness. Still they offend my eye in ways that indicate they are fragile, hard to maintain and error prone)
Ok head is a bit clearer... bitching complete. I need to go read up on the QueryDef object I think.
Edit:
The best solution I have come up with ( tip of the hat to a forum post I read but can't remember... its probably common knowledge anyway) is to maintain the query in as a normal QueryDef which gives me the ability to use the GUI and compile the SQL for testing; the Query contains a parameter which is essentially a unique key phrase that I then use the "Replace" function to replace with my desired parameter info.
For instance
Dim qdef As QueryDef
'contains a param "[ContID]"
Set qdef = CurrentDb.QueryDefs("ContinuityRelationshipFormSpecificQuery")
Dim theSql As String
theSql = Replace(qdef.sql, "[ContID]", str(relationshipID))
Me.RecordSource = theSql
This is a fairly simple and maintainable system. It takes about four lines to do what I would like to do in less, but its still only a line per parameter so not too horrible a trade-off.
Tuesday, November 9, 2010
Improving the performance of a Shared Access Database
http://www.fmsinc.com/microsoftaccess/performance.html
http://www.fmsinc.com/microsoftaccess/Performance/LinkedDatabase.html
http://office.microsoft.com/en-us/access-help/about-sharing-an-access-database-on-a-network-mdb-HP005240860.aspx
I've implemented the trick of holding the lock file open and it seems to be helping. Counter intuitive to all the energy I have spent releasing resources in other languages and systems but anyway.... it works. I am also doing a lot better with caching results and doing my joins in code rather than via SQL. Anyway, back to the bug hunting...
http://www.fmsinc.com/microsoftaccess/Performance/LinkedDatabase.html
http://office.microsoft.com/en-us/access-help/about-sharing-an-access-database-on-a-network-mdb-HP005240860.aspx
I've implemented the trick of holding the lock file open and it seems to be helping. Counter intuitive to all the energy I have spent releasing resources in other languages and systems but anyway.... it works. I am also doing a lot better with caching results and doing my joins in code rather than via SQL. Anyway, back to the bug hunting...
Friday, October 29, 2010
Profiler for VBA code in Access
I found a nice little stack style Profiler for VBA from a book called Access Cookbook. Its been very handy for identifying and tracking down a couple of memory leaks (My fault as usual) The Profiler is in a module called basProfiler and is discussed in an article called "Create an Execution Time Profiler" which has been reprinted all over the web. I had to fix a small bug in the code that formatted the output to the log file. Other than that it was good to go out of the box.
My usage of basProfiler
1. Import the module.
2. Set the path to the log file to somewhere conveinent. (I put mine into my working directory for subversion to manage)
2. Instrument the code blocks with the opening and closing lines.
I open the log file in Notepad++ on the other monitor and every time I run the code, Notepad++ detects that the file has changed on disk and allows me to reload it. This gives me the power of a real text editor rather than a log window to play with the output.
Simple and elegant. Just the way I like my tools.
I considered hacking it to output a comma delimited file for import into excel for analysis but I ended up not needing that sort of power on this job.
And we are done.
My usage of basProfiler
1. Import the module.
2. Set the path to the log file to somewhere conveinent. (I put mine into my working directory for subversion to manage)
2. Instrument the code blocks with the opening and closing lines.
Public Sub Whatever()
acbProPushStack "Whatever" 'profiler
'code to be profiled here
'.....
acbProPopStack 'profiler
End Sub I open the log file in Notepad++ on the other monitor and every time I run the code, Notepad++ detects that the file has changed on disk and allows me to reload it. This gives me the power of a real text editor rather than a log window to play with the output.
Simple and elegant. Just the way I like my tools.
I considered hacking it to output a comma delimited file for import into excel for analysis but I ended up not needing that sort of power on this job.
And we are done.
Quicksort for VBA array of custom objects
Can you believe that there is no sort function for the collection object in Access VBA? Anyway, I have dumped the collection for a typed array to avoid some of the overhead of type casting variants everywhere. (Long story involving a profiler and a lot of time which finally resulted in finding a different source for the bug that was causing the slow down.)
Back to the main thread. I went looking for a good implementation of a sorting algorithm in VBA. After a couple of absolutly spectacular crappy implementations I found this one.
http://www.java2s.com/Code/VBA-Excel-Access-Word/Data-Type/QuickSort2.htm
Which is short, simple and elegant. In comparison to some of the other multi-page examples I found its really ... beautiful.
Anyway, the first thing I did was refactor out the comparison operation and the swap operation, so I could apply the operation to an array of custom objects without adding clutter to the quicksort sub. By extracting the comparison operation, I can now sort the objects in the array on a variety of properties. If I get bored I will implement both ascending and decending sort but I don't need it right now.
The most painful part of this is not being able to write a template for the code and make type independent. (Anyone mentions VBA variants as being equivalent and they are outa here ...)
Just thought I would share...
Back to the main thread. I went looking for a good implementation of a sorting algorithm in VBA. After a couple of absolutly spectacular crappy implementations I found this one.
http://www.java2s.com/Code/VBA-Excel-Access-Word/Data-Type/QuickSort2.htm
Which is short, simple and elegant. In comparison to some of the other multi-page examples I found its really ... beautiful.
Anyway, the first thing I did was refactor out the comparison operation and the swap operation, so I could apply the operation to an array of custom objects without adding clutter to the quicksort sub. By extracting the comparison operation, I can now sort the objects in the array on a variety of properties. If I get bored I will implement both ascending and decending sort but I don't need it right now.
The most painful part of this is not being able to write a template for the code and make type independent. (Anyone mentions VBA variants as being equivalent and they are outa here ...)
Just thought I would share...
Subscribe to:
Posts (Atom)