Just to report a problem that I run into and hopefully this post can help someone that run into a similar problem.
The problem: Receive 'object library not registered' error when try to check referenced code in ACCESS 97 that references MS VBScript engine.
Suspected scope of the problem: This problem could also appear in code that was developed for other version of ACCESS on Windows Vista, XP or older operation system.
The sequel: I got some ACCESS 97 code that uses the regular expression object (RegExp) that was made available by Microsoft through the installation of Microsoft's VBScript engine (The latest version is the Windows Scripting Host version 5.7 and can be download from Windows Script 5.7 for Windows Server 2003). Recently, when I try to check the 'reference' (open a code module and -tools -reference) in ACCESS 97, I received the 'object library not registered' error and was not able to see the referenced object for the database.
Kernel of the problem: A reference to the 'VBScript Global' type library in Windows 7. Basically, in my old XP machine, I have to install the VBScript Engine so that the 'VBScript Global' COM (Component Object Model) object exist. But I did not do that on my Windows 7 machine.
The solution: After googling the web and found the Wikipedia article: Windows Script Host, it is clear that there is no VBScript Engine for Windows 7 to download. It should come with the Windows 7 machine. At that point, I was thinking maybe the RegExp is contained in other COM that I should reference. But I was failed to find one. In the end, I simply test the code: CreateObject("VBScript.RegExp") and it worked without me adding any reference in ACCESS.
The annoyance: Since the database is hosted on a network drive, I will have to add and remove the reference based on which machine I am using.
Showing posts with label MS ACCESS. Show all posts
Showing posts with label MS ACCESS. Show all posts
Friday, November 8, 2013
Monday, August 26, 2013
MS ACCESS Navigation Pane Add Item Greyed Out 2002,2007 and 2010?
This is a bug resolved by guest posted at:
office-forums.com
The solution apply to my situation, where I upgraded my secured (user level security) database from MS ACCESS 97 to ACCESS XP (2002) and opened with MS ACCESS 2007.
I couldn't tell you that this always happen. Because I believe I was able to open MS ACCESS XP database in MS ACCESS 2007 and see the Navigation Pane worked the way it suppose to and this is why I was at lost when this happen to me.
Through my (Google) search, it seems it affect MS ACCESS 2003, 2010 too.
Here is a copy of the solution posted at office-forums.com:
===========
You'll need to right click the Navigation Pain bar- select Category - select
Table and Related Views - click Ok when a message appear and wait until it
completes.
Now, right click on the Navigation Pane bar again, select Navigation Options.
Now you should see the left listbox "Categories" filled with one category.
Click add under categories to add one sample name it "test" and now on the
right listbox "Groups for "test" appears and the button at the botton "Add
Group" is enabled.
The so called bug only confuse users if there are nothing in the listboxes
and disabling the buttons is really uncalled for. Something MS Access Team
need to fix.
===========
The solution isn't very neat in that the created 'Table and Related Views' Category can never be deleted. But other than that, things seem to work just fine.
From time to time we all benefited from other people's finding and we should credit them whenever we can.
== Added on Aug. 29, 2013 ======
A problem is discovered on Aug. 29, 2013.
If the .mdb file is in MS ACCESS 2000 format and the category is setup in MS ACCESS 2007, login with MS ACCESS 2010 will remove that (could be all) category. However, if the category was created in MS ACCESS 2010, category will stay if login with older version of the ACCESS.
office-forums.com
The solution apply to my situation, where I upgraded my secured (user level security) database from MS ACCESS 97 to ACCESS XP (2002) and opened with MS ACCESS 2007.
I couldn't tell you that this always happen. Because I believe I was able to open MS ACCESS XP database in MS ACCESS 2007 and see the Navigation Pane worked the way it suppose to and this is why I was at lost when this happen to me.
Through my (Google) search, it seems it affect MS ACCESS 2003, 2010 too.
Here is a copy of the solution posted at office-forums.com:
===========
You'll need to right click the Navigation Pain bar- select Category - select
Table and Related Views - click Ok when a message appear and wait until it
completes.
Now, right click on the Navigation Pane bar again, select Navigation Options.
Now you should see the left listbox "Categories" filled with one category.
Click add under categories to add one sample name it "test" and now on the
right listbox "Groups for "test" appears and the button at the botton "Add
Group" is enabled.
The so called bug only confuse users if there are nothing in the listboxes
and disabling the buttons is really uncalled for. Something MS Access Team
need to fix.
===========
The solution isn't very neat in that the created 'Table and Related Views' Category can never be deleted. But other than that, things seem to work just fine.
From time to time we all benefited from other people's finding and we should credit them whenever we can.
== Added on Aug. 29, 2013 ======
A problem is discovered on Aug. 29, 2013.
If the .mdb file is in MS ACCESS 2000 format and the category is setup in MS ACCESS 2007, login with MS ACCESS 2010 will remove that (could be all) category. However, if the category was created in MS ACCESS 2010, category will stay if login with older version of the ACCESS.
Monday, August 1, 2011
MS Word mail merge using MS Access LIKE operator
Mail merge refer to the process of using/linking data from an external source to fill out an otherwise static document. An example can be a formal letter that send to all board members. The content of the letter is the same (static), but the title/address-block and greeting can be pulled from a database so that letters are with proper greeting and title of the addressed board member.
For most part, Microsoft's (MS) word processing (Word) and databbase (Access) works well with each other. However, under one situation, the normal way of linking MS Word and MS Access failed to work correctly. This article is to report a work around for that situation.
In general, database can hold a lot of information and it is not uncommon that a mail merge incident only need a small part of those information stored in the database. For example, the database may hold the contact information for all employee, while the letter is addressed only to board members. In database, queries are used to filtered out unwanted information and MS Word can use queries to obtained the needed information. For most of queries, MS Word worked well with MS Access.
The Problem:
When a query uses the LIKE operator/keyword, the normal way of linking MS Word and Access could fail. The Word will return results that would be produced with the '=' operator instead of the 'LIKE' operator. This problem had been reported dated back to 2005 and still exist today (Aug. 2011) in Word 2007.
The work around:
To work around the problem, we can have Word linked to Access through an ODBC (Open Database Connectivity), which defines a standard protocol to be implemented by middle-layer software to allow applications (e.g. Word) to connect to various database.
To proceed, in Windows environment, you proceed by defining a DSN (Data Source Name) through the ODBC manager ( -Start -Control Panel -Administrative Tools -Data Sources) with 'Driver do Microsoft Access' as the middle-layer software and the Access database file (.mdb) as the database file.
Once the DSN is in place, you can now use it as the mail merge data source. In Word, when prompt to select the data source, click the 'New Source...' button, Select ODBC DSN and click Next. Select the DSN you created, click Next, select the query using the LIKE keyword, click Next and you are prompt to save these settings in a data connection file to be used later. Click 'Finish'.
Now you can highlight the connection file and click open. The query should work correctly.
For most part, Microsoft's (MS) word processing (Word) and databbase (Access) works well with each other. However, under one situation, the normal way of linking MS Word and MS Access failed to work correctly. This article is to report a work around for that situation.
In general, database can hold a lot of information and it is not uncommon that a mail merge incident only need a small part of those information stored in the database. For example, the database may hold the contact information for all employee, while the letter is addressed only to board members. In database, queries are used to filtered out unwanted information and MS Word can use queries to obtained the needed information. For most of queries, MS Word worked well with MS Access.
The Problem:
When a query uses the LIKE operator/keyword, the normal way of linking MS Word and Access could fail. The Word will return results that would be produced with the '=' operator instead of the 'LIKE' operator. This problem had been reported dated back to 2005 and still exist today (Aug. 2011) in Word 2007.
The work around:
To work around the problem, we can have Word linked to Access through an ODBC (Open Database Connectivity), which defines a standard protocol to be implemented by middle-layer software to allow applications (e.g. Word) to connect to various database.
To proceed, in Windows environment, you proceed by defining a DSN (Data Source Name) through the ODBC manager ( -Start -Control Panel -Administrative Tools -Data Sources) with 'Driver do Microsoft Access' as the middle-layer software and the Access database file (.mdb) as the database file.
Once the DSN is in place, you can now use it as the mail merge data source. In Word, when prompt to select the data source, click the 'New Source...' button, Select ODBC DSN and click Next. Select the DSN you created, click Next, select the query using the LIKE keyword, click Next and you are prompt to save these settings in a data connection file to be used later. Click 'Finish'.
Now you can highlight the connection file and click open. The query should work correctly.
Friday, September 18, 2009
Database usability
Before we migrated to MS ACCESS 2007, we noticed that we could not sort queries by property/description in MS ACCESS 2007. This caused a big problem for us since a lot of our tasks are based on several queries and we try to keep them together by coding the description. Why we chose to do this in the first place? Well, as we all know that ACCESS isn't build to be a power house and there are times you have to combine several queries to achieve the task on hand. We did not chose to code the query name since changing the query name may break other queries.
Because of this, we began a project that stored all queries in a table and on top of that we build other facilities to link queries into tasks. In the process, we constructed a framework that we think can be a better user front end for databases.
The framework create a layer on top of the query so that we can assign a path to classify a query. This is like a link in the *nix file system. We can then build a tree structure that allows us to locate a query. We also build a task layer on top of the query so that we can combine couple of queries to create a task. On top the task, we also build a tree structure that allows us to organize tasks.
As mentioned earlier, we stored all our queries in a table, this gives us the ability to search the table and build dependency information between queries. We can also build the dependency info between queries and tasks. All of these helped us in maintaining our projects.
For example, one of our tasks is to import data from external sources. The data format and definition can change from year to year. The process/task to import the data can also change from year to year. Every year we did our best to import that data. However, from time to time, we found that we process the data incorrectly later. It become obvious that we need a good way to document the task we performed every year so that in case of error, we can trace back our steps.
With the system we have, we can create task alias/paths like:
Organization/DataType/DefintionCheck/2007
Organization/DataType/DataImport/2007
etc...
In the case that the data come in in the same format like last year, we can simply create a new link that pointed to the same task of last year. Otherwise we will create a new task and alias.
With the path/alias, we can also build a tree structure that make locating queries and tasks much easier.
At this point, the system is not finished yet and we are trying to make our system more universe so that we can adopt this system to, say, the OpenOffice database.
Because of this, we began a project that stored all queries in a table and on top of that we build other facilities to link queries into tasks. In the process, we constructed a framework that we think can be a better user front end for databases.
The framework create a layer on top of the query so that we can assign a path to classify a query. This is like a link in the *nix file system. We can then build a tree structure that allows us to locate a query. We also build a task layer on top of the query so that we can combine couple of queries to create a task. On top the task, we also build a tree structure that allows us to organize tasks.
As mentioned earlier, we stored all our queries in a table, this gives us the ability to search the table and build dependency information between queries. We can also build the dependency info between queries and tasks. All of these helped us in maintaining our projects.
For example, one of our tasks is to import data from external sources. The data format and definition can change from year to year. The process/task to import the data can also change from year to year. Every year we did our best to import that data. However, from time to time, we found that we process the data incorrectly later. It become obvious that we need a good way to document the task we performed every year so that in case of error, we can trace back our steps.
With the system we have, we can create task alias/paths like:
Organization/DataType/DefintionCheck/2007
Organization/DataType/DataImport/2007
etc...
In the case that the data come in in the same format like last year, we can simply create a new link that pointed to the same task of last year. Otherwise we will create a new task and alias.
With the path/alias, we can also build a tree structure that make locating queries and tasks much easier.
At this point, the system is not finished yet and we are trying to make our system more universe so that we can adopt this system to, say, the OpenOffice database.
Subscribe to:
Posts (Atom)