How do I import/convert MSAccess queries to OOBase?

[Burgman73]

HI.
I have an MSAccess DB, with tables, few, queries, a lot, and "masks" (the presentation windows, I do not know the correct name in English) , and have "imported", that is connected to it from OOBase. I have read in this forum that the "masks" are not importable, but the queries have been imported into the tables list. Is there a way to convert them back to queries? When I open the ex-queries by double clicking on them in the talbes list they open up as a table, or the result of the query itself maybe (with no input parameters, which some of them have),
Any suggestions (besides opening the queries on the Old pc and copy the query text and then make new queries on the new pc with OO)?
Thanks

[Villeroy]

You need to export queries from MS Access which has some excellent self-reporting tools.
Input forms and reports are not convertible.

[Burgman73]

Thanks, I'll try...

[Burgman73]

Hi. I am trying but I need some more info...which format should I export to (in order to be able to read them in OOBase)?Do you know if I can export queries that require an input parameter? Sorry, but is it clear that I am not interested in the results as much as in the query text?

[Villeroy]

Having some old version of MS Access and a Windows operating system but without in-depth knowlege about databases you must not convert your working Access stuff to anything else.
If you want to use Writer/Calc [instead of]/[in addition to] Word/Excel you can simply connect a Base document to the Access file and copy the query definitions (SELECT...FROM...WHERE...) into the Base document. Turn on direct SQL mode before you save the queries and the query definitions should work exactly as in Access. This will be sufficient to feed your serial letters and calculation models with table data or sub-sets of table data (query results).

[Burgman73]

I am sorry but cannot undestand what you are saying. Maybe I have not make myself clear either...on my old pc, I have MS Office, and there I created the DB, with tables,queries (and input forms, which cannot be "exported" to OOBase). On a new pc I only have Open Office, and am trying to use Base to open my Access DB. My queries are not simple queries from single table, nor do I use excel nor word for anything to do with my db, and some of them require an input parameter from the user to be processed and give the results. So what do you mean by "connect a Base document to the Access file and copy the query definitions (SELECT...FROM...WHERE...) into the Base document"? It is the very first time I use OOBase, so I am not familiar with its terminology, what do you mean by "Base Document" and "Access file"?
When you talk of turning the sql mode on before saving the queries, what do you mean? I know how to view a query in sql mode, or the other modes (I only know the names in Italian), but I thought this can be done for one query at a time, am I wrong?
When I "connected OO to an existing DB", and chose MS Access, then OO just asked me what name I want to save it with. If I choose to "Open it for modify after connecting" then it shows all the tables, and the queries also in the tables list. Which is my original problem.
On the MS office pc I am trying to export the queries, but so far it only exoported the results, just as I saw in OO at the beginning...
I am trying several different formats for the export and they all do save the result "table", in excel, rtf or any other format...If I choose to export in .mdb format then an error message appears saying that the required file does not exist (the one I am trying to create with the export procedure)...I am puzzled.
I think I'll just copy the queries text and make them over again in OO...

[Villeroy]

Install MS Office on the other computer as well. You can not use Base as easily as MS Access.
If you do not even know what a query definition is (or a SELECT statement) then MS Access is even more sophisticated in fooling its "users" than I imagined.

[pmcleanj]

I think this is the same problem that I am having, and as I do know the words in English, perhaps I can make it clear:

Once per quarter, the local state electrical authority publishes a database of up-to-date grid information as an MS Access file only -- call that file "Grid.mdb". Many individuals and small companies who need to use this data don't have MS Access, or even Windows systems, and so don't have the option to "just install MS Office". I am trying to use Open Office to export "Grid.mdb" into a more open format: a native Base document, or even an SQL backup script, in aid of those smaller stakeholders.

Like Burgman73, I used the "connect to an existing database" option, chose "MS Access", and saved the resulting file as "Grid.odb". When I open "Grid.odb", I see all the tables from Grid.mdb as tables, and I see all the queries from Grid.mdb in the Table pane as views -- similar to what I get when I use Base to connect to a postgres database. If I understand the functionality correctly, the new "Grid.odb" is NOT a conversion from the old "Grid.mdb", but just establishes a link to the original Access database as a Datasource. Since I can run all those "views", and can open all the tables, Grid.odb must have visibility of the table definitions and of the SQL code in the query definitions in the original database. So, how to get those definitions in a form I can use? There are 96 tables, and over a hundred queries, so manually handling them all is not feasible. I want to automate this using Basic.

If it is a native Base database, I can iterate through the Tables collection, and iterate through the Columns collection of each table, and strip out the table definition. In this case, however, I can only get the names of the individual tables. When I then try to iterate over the columns, the Count is returned as "0". I cannot get the additional information I need to describe the table. I cannot access the "views" at all -- the DataSource.QueryDefs collection has a count of 0. But, we know that Base *does* have access to the columns and querydefs -- it displays them! So, how do I access them from Basic?

[DACM]

Welcome pmcleanj,

Grid.odb must have visibility of the table definitions and of the SQL code in the query definitions in the original database...So, how do I access them from Basic?

The native MS Access SDBC driver is simply not that advanced, so i don't think a coded solution is possible from the OOo side. The driver apparently converts all Queries into Views which effectively hides their '.content'.

So, how to get those definitions in a form I can use? There are 96 tables, and over a hundred queries, so manually handling them all is not feasible. I want to automate this using Basic.

A programmatic solution would start with .Net or MS Access installed on your development computer.
How To Retrieve Schema Information by Using GetOleDbSchemaTable and Visual Basic .NET
How To Export and Import Access Tables Using DAO or ODBC

But then, you may be re-inventing the wheel. MS Access is well supported by various database tools. I would look into tools that export from MDB to SQL including the table definitions and data. That way, you can import the database into virtually any SQL backend. Some of these may have sufficient freeware or trial functionality to accomplish the export task:
OmBelt Database Tools
MDB View 1.0
MDB Viewer Plus
Some will require the free MDAC (Microsoft Data Access Components) from Microsoft.

[pmcleanj]

Thank-you, DACM,

It is always reassuring when one hits a brick wall, to know that whatone was trying to do is just not possible. Disappointing as that is, it would be more disappointing to find out that the method was simple and obvious and I was just blind to it . I do know how to write the export routines in Visual Basic if I had MS Access. And, I can bite the bullet and buy it if necessary, especially since if I shell out for one Access installation, I may be able to help dozens of small stakeholders avoid incurring that cost. Thanks for the suggested alternative viewers. Here is what I found so far:

• Windows 7 has the MDAC (version 6.1 in my case) installed as protected system software components, so it is neither necessary nor possible to install them from the free download site.
• MDB View 1.0 does not have the export functionality yet, opens the queries as views, and takes a fault on some of the FROM clauses in the views in the source database -- so, it's less useful than Base.
• MDB Viewer Plus can export the tables to text or html, but not (that I could find) the table definitions, and it too opens the queries as views, so it's about equally useful as Base for this purpose
• OmBelt Database Tools looks like it would do the job fine if I were starting from SQL Server, but has only an "Export Access Table to SQL" wizard which does half the job (getting the tables and views out) but still needs to be called once per table -- meaning I'd have to manually select the table and run the wizard 96 times each quarter to rely on this solution.

So, I'm still looking, but will resort to MS Access as a last resort.

[Villeroy]

In the world of proprietary software it is intended to be like that.
Please, pay the little fee for MS Access. Base will disappoint you anyway.

[DACM]

Very good analysis of the highlighted tools! If you ever become an OOo user, we could use your eloquence and experience in these forums for sure. I tried the database tools myself this afternoon and concluded the same -- they were particularly lame with respect to MS Access export support. One thing to notice before you resolve to start from scratch, the OmBelt product has a "Pro" version ($48) that works from the command-line. So it may be that you can make up for the lack of MDB database-export depth with a simple batch file. Or maybe not, because it's beginning to be clear that the available Microsoft drivers for external access to MDB structures are quite limited, perhaps by design as Villeroy contends. But then ADO.NET is just begging someone to write a cross-platform (.NET Framework dependent), MDB viewer and SQL export tool. I'd really be surprised if it hasn't been done before. It's a perfectly natural migration tool for MDB to SQL Server or otherwise. Very few database-savvy consumers would be happy dumbing down their data with simple .csv or .dbf export (although .xml export has the potential to maintain all data definitions and data). So like Villeroy...I think you'll be happier with a .Net or VBA solution. I'm just assuming it's readily available somehow, somewhere, so you won't have to (re?)invent that wheel.
Some useful code examples:
Microsoft instructions
C# code
VB.Net code
ADO.NET code

[eremmel]

Have a look to this discussion. There are suggestions to use DAO and plain SQL (Scroll to the end of page). I use a simple SQL command tool written in perl to connect to any ODBC and try to query the system tables. MSys*. But in my MSAccess database I do not have permission to read those.

Edit: By default a MSAccess database has a (admin) user ''admin" with empty password. When this is inplace I not could lookup the queries (access the content of the system tables). I used MSAccess program to set the admin password (open *.mdb exclusive mode) and now I can access those tables.

In Base you have also the possibility to connect to an ODBC connection. You might try it via that route and see if you can access those system tables.

Edit: To see the queries via Base, I had to use an ODBC connection and not the native 'MSAccess' driver. Next to that I had to make a view (in native SQL) to add an extra indirection to access the MSysQueries table, else Base complained. I named the view MySysQueries and used as text: 'SELECT * FROM MSysQueries'. Building a form/report will now be easy to get all the queries out of MSAccess. I'll attach a list of all the system tables and their column names. One need to perform some reverse engineering to figure out details.

[Gabrielwer]

Full transfer not possible, you can only, reach your files tables and views, no forms and report transfer.
You can connect to .accdb file, create new database file, select connection to an existing database, open drop down list, select MSA 2007, save. You created Base frontend to MSA file. Tables not inside, if you want to change tables create new empty file with built inHSQL databse engine. Open both file, grab tables from MSA connected file and drag and drop into empty new file, and you can forget MSA. This file will be fully editable.
http://wiki.services.openoffice.org/wiki/MSA-Base_Faq

[DACM]

...if you want to change tables...

This thread discusses various ways to retrieve the Query definitions from an MS Access database file. Full knowledge of the MSA-Base_Faq and readily-available import options are assumed.

The conclusions include:
(1) Don't migrate from a superior product to and inferior one; MS Access will prove well worth the price compared to Base.
(2) With MS Access available, copy&paste the individual Query definitions from within MS Access to Base Queries. Then select direct-SQL in Base when simply connecting to the native-Access file with Base.
(3) Programmatic export of Query definitions is not possible through the dedicated MS Access drivers. Using ODBC and some SQL to retrieve the Query definitions is promising...?
(4) It shouldn't be difficult to create an executable ADO.NET export to SQL script including all Query definitions, but oddly we've been unable to find a ready-made solution.