MS Access Engine #msaccess #msexcel #crystalreports #odbc

MarkVS, I tried the same sqlite3 ODBC driver (actually one prior to Jan 12) with OpenOffice Base but ran into the problem with the missing RMNOCASE collation. I’m very interested in seeing how you overcame that with MS Access.

Tom

I no longer have access to SQL Server, but it would be a terrific tool to be able to use due to its capabilities above and beyond those of Access, which are still quite good. The ODBC process described here would likely work with SQL Server and many of the other database packages, as well.

Since moving from RM3 to RM4 I have cleaned up my place list and am slowly working on source citations. One project I have yet to start is that of sharing events, especially census facts. The reason I haven’t started is the lack of a shared facts report. I have a basic knowledge of SQL and have made several unsuccessful attempts to construct a query that will give me the records I want. I am using SQLite Manager, which is an add-on for Firefox.
The query I am looking for is one that will return one record for each shared event. This record would include the fact name, fact date (to distinguish multiple events for the same people), the principal surname, given, the witness (sharee?) surname, given and the role. RIN numbers would be an added bonus. That would be enough to allow me to check my data entry as I progress. I currently have individual census facts for many persons in my database and I am reluctant to replace the individual fact with a shared fact until I can see a report.
I can write two queries that will give me all the info I want, one for the principal and one for the witness but I can’t combine the two to give me the data in one result set.
I have run several of the excellent queries listed in this Wiki. The “People Who Share a Fact with a Principal, But Who Are Not in a Tree in the File” query returns what I want except that I obviously want people who are in my tree. I have not been able to modify this query to get the records I want. The “AllFacts4Persons” query has almost everything I need except when a principal shares multiple census facts with the same witness only one record is returned with a count of the multiple facts, not each individual instance.
This seems like a query that would be useful for many people and one, I hope, that will eventually show up in an update, but there is no guarantee it will include everything I want to show.
If I had the basic query I could create a report in Excel or Access. If anyone can tell me how to modify one of the existing queries I would be most appreciative.
John Weaber
Wayne, PA

I’ve had considerable success playing around with SQLite, both directly using SQLiteSpy and more indirectly using OBDC and Microsoft Access. But one of the most frustrating aspect of using OBDC and Access has simply been creating the required DSN’s. The directions posted both here and at other places on the Internet don’t quite work on my machine running 64 bit Windows. And the biggest problem has simply been to try get the OBDC Administrator of Windows to run.

On my machine, the OBDC Administrator does not appear anywhere in my Control Panel, which apparently is where it “should” appear. The only place I’ve been able to get to it is from the External Data tab of Access 2007, but that approach is sub-optimal in a number of ways.

However, I have now discovered that the OBDC Administrator is called odbcad32.exe. There are four copies of odbcad32.exe on my machine. Two of the copies make no sense to me. Of the other two, one is stored as C:WindowsSystem32odbcad32.exe and the other is stored as C:WindowsSysWOW64odbcad32.exe. On my machine, the one stored as C:WindowsSystem32odbcad32.exe doesn’t work, and the one stored as C:WindowsSysWOW64odbcad32.exe does. That’s probably because I’m running 64 bit Windows rather than 32 bit Windows.

To make the program easier to invoke, I did a “Pin to Start Menu” for C:WindowsSysWOW64odbcad32.exe. Windows/XP does not include the “Pin to” facility, although the same thing can be accomplished in other ways. I went to great pains to skip Windows/Vista, so I don’t know how the same thing is accomplished on Vista. If you are running Windows 7, you probably already know about “Pin to” and you probably use it a lot.

In any case, the difficulty I was having in getting to the Windows OBDC Administrator was not a show stopper, but it sure was frustrating. I am now a much happier camper in my use of OBDC.

Jerry

I have developed a number of “quick and dirty” queries, both in the MS Access environment and in the environment of an SQLite manager. If/when I get one of these queries in good enough shape for public consumption, I have an informal goal of posting both an MS Access version and a version for the SQLite manager environment.

I don’t really want to write each query twice. I hope to write each query once for one environment, and then to make only whatever minimal changes are required to make the query compatible with the other environment.

There are a number of challenges to achieving a such a goal, but I think I have run into a challenge I didn’t really expect – namely the processing of nested OUTER JOIN’s.

INNER JOIN’s are associative with respect to their results, although they may not be associative with respect to their performance. OUTER JOIN’s are not associative with respect to their results, and parentheses must be used to specify the order in which the JOIN’s are to be applied. However, MS Access seems not to honor the parentheses, even though the SQL generated by MS access by its point-and-click interface does include parentheses which seem to be ignored by MS ACCESS itself. So I’m not sure why the parentheses are even there when MS Access generates the SQL for you.

In any case, when I try to use parentheses for multiple OUTER JOIN’s in MS ACCESS, and if MS ACCESS thinks there is anbiguity (even though I have removed the ambiguity with the parentheses), I get an error message and cannot proceed. The error message recommends creating smaller queries with fewer JOIN’s, and then to compose larger queries out of the smaller queries rather than composing larger queries directly from tables.

This technique does work, but it seems to render my queries not portable into the SQLite manager environment. Which is to say, it seems to me that MS Access is blurring the distinction between queries and views, and what Access is calling queries are really being stored as views. So my “larger queries” are really being composed from “smaller views” rather than being composed from “smaller queries”. The only way I know of to really compose “larger queries” from “smaller queries” is to have single “larger queries” that have properly nested JOIN’s, which means parentheses that SQLite seems to honor and which MS Access seems not to do.

I may have this all wrong, so a sanity check would be appreciated. And by the way, I can get more or less the same “large query with multiple nested JOIN’s” to work in both environments. It’s just that the query that works in one environment doesn’t look anything at all like the query that will work in the other environment, and vice versa. So I’m having to write the same query for each environment almost from scratch. Or maybe I’m just doing something wrong.

Jerry