[Solved] Linking to MS Access 97 database - query/report

[oldgreygary]

Hello,

I have transferred this question to here as I have a number of issues and thought this area might be more relevant for my problems.

I think that I need to rec-cap as there are a few issues that are causing me a few headaches!
My starting point was an Access 97 database. It seems I had two choices to get this into Base.
1/ Link to the Access database
2/ Export the Access database to spreadsheets and copy and paste into base.

I chose option 1, as it seemed to offer the quickest route.

The link to the Access database worked fine and all the tables were accessable.

I created forms which worked in a similar fashion to the Access 97 ones.

My problems began when I started to create queries and reports.

I added the report builder extension as well as I thought this would help.

Here are the problems.

Using the concat function or vertical bars (||) causes either an undefined function error or Invalid use of vertical bars.

If I run the SQL in direct mode with '+' used to joined two fields then it works ok. I presume that is because that works with Access?

If this SQL is then used in the report it will bring an error message saying that 'Field names could not be retrieved'

Also, it seems as though I cannot disable/enable the report builder as it won't switch off or switch on properly.

So, that leaves me with the basic problem that I cannot generate a report using an SQL query. It does not appear that either functions or double bar (||) work when you are linking to an Access database. If I do use an SQL statement that works directly with the Access database then it will not work with the report system. AS the fields are not accessable.

If anybody can throw light on any of the problems then I would be interested to hear from you.

Cheers for now

Gary

[r4zoli]

When you connect to external data source and creates query in SQL direct mode, you must follow the data source syntax.
In your case use acceess97 syntax in OOo in SQL direct mode.
When you use OOo Query UI then you must follow it's syntax, which not work correctly with lot of data sources, it is designed to work with built-in hsqldb database engine.

[oldgreygary]

Hello,

I understand about the different syntax re: Access 97 /hasldb. Below is an sql that works on my database in Access 97:

SELECT DISTINCTROW Client.clientId, Client.title & " " & Client.forename & " " & Client.surname AS clientName,
Client.address1, Client.address2, Client.address3, Client.address4, Client.postCode,
Client.telephone, Client.mobile, Client.email, workSheet.workerId, workSheet.wstid,
workSheetDetails.workTypeId, hours, payRate, workSheet.workSheetId,
hours*payrate AS TotAmt, 0 as FixedRate, worker.*, workSheetDetails.DateOfWork,
InvoiceNo, Invoice.FromDate, Invoice.ToDate, Invoice.Comment
FROM (Client INNER JOIN ((worker INNER JOIN (workstatus
INNER JOIN workSheet ON workstatus.workstatusId = workSheet.wstId)
ON worker.workerId = workSheet.workerId) INNER JOIN Invoice
ON (worker.workerId = Invoice.workerid) AND (workSheet.workSheetId = Invoice.workSheetid))
ON Client.clientId = workSheet.clientId)
INNER JOIN (workType INNER JOIN workSheetDetails
ON workType.workTypeId = workSheetDetails.workTypeId)
ON workSheet.workSheetId = workSheetDetails.workSheetId
WHERE (((payRate)>0))

If I try and run this in the direct sql mode in base it does not work. The distinct row has to be changed to Distinct and the double quotes have to be chnaged to single quotes. It then runs ok. Thats fine. I save the query.

I then try and create a report using that saved query. While it offers up the query as a selection none of the fields are shown as being available for the report?

That is the part that is confusing me. As it seems as though the report system will not accept an sql query that is accessing direct. i.e. using syntax that is valid in Access 97.
That is my problem. Although the data is there and the query works in isolation it will not work within the report function.

Any thoughts?

Cheers for now

Gary

[r4zoli]

How did you created the report?

Create new report in design mode, open report property in report navigator, go to the data tab.
Set SQL commands as report source, click on ... to open query designer, activate SQL direct mode, copy-paste query which was working in standalone query, run query to check it works as report source. The field selector must show fields in background.
Close query, and proceed report design.

[oldgreygary]

Hello,

Thanks for your reply. I followed the instructions and it enabled me to access the fields. So they are now available for the reports!

Unfortunately, that led to my next problem. I tried adding a group and that causes an error. It states that ' An error occurred while creating the report'. I was using the report builder extension. So, I tried to disable that to try the original report creator but disabling the report builder has no effect and I cannot get back to the original!

Any thoughts on why adding a group causes a failure? Also, how can I disable/enable the extension for the report builder so that it does switch off and return to original report writer?

Cheers for now

Gary

[r4zoli]

Any thoughts on why adding a group causes a failure? Also, how can I disable/enable the extension for the report builder so that it does switch off and return to original report writer?

You can in OOo disable in extension manager, restart OOo (quit quickstarter if running) - than you can use old report wizard, but you could not transform report builder report to old one.

[oldgreygary]

Hello,

Firstly, with regards to the report builder extension. The only way I seem to be able to go back to the old report writer is to use the 'remove' option in the 'extension' option under tools.

That takes me back to square one as that only gives the report writer wizard so I cannot use SQL in there. So, back to using the report builder extension. The basic problem with this seems to be that when using native SQL and the grouping option it causes problems. When I tried just creating a report with a table and created groups it was ok. When using native SQl I got the following:

If I tried to execute the report outside i.e. of the edit option (i.e. in the area where the tables, queries etc exist) it displayed 'Syntax error in From clause. This is using the same SQL which has been created and run and which has worked.

If I edit the report and try to execute the report within this environment I get an error message box. When I press the 'more' option it has the following in it.
Error SQL status S1000 - An error occurred while creating the report.
An exception of type com.sun.star.lang.WrappedTargetException was caught.

There is also the Synatax error in From clause message.

If I delete the grouping, these message go away and the report runs. So the issue definitely seems to be a probelm with the grouping. I presume this is also something to do with using the native SQL?

Any thoughts,please.

Cheers for now

Gary

[r4zoli]

I see no solution for you, may be others could help you.
You may try to transfer your data from access file to odb file and try to create report on it.

[oldgreygary]

Hello,

Firstly, thank-you for your patience and replies in response to my questions.

The only thing that I could see was that although the SQL worked in Access and initially in report the error did say the error was after the From statement. So, I thought that instead of using the inner join statements I would change it to using where and and statements and re-organise the sql statements. I don't know why but this has made the difference and I am now able create grouping in the report? I cannot offer any reasonable explanation. As the original SQL works in Access and in the report builder when you choose the options for data.

It does seem that you do need to have a re-think if you intend to use SQL statements from Access as they might not work even though you intend using them to access the Access database direct.


Cheers for now

Gary