My company has a large portion of their data (about 60%) in a Sybase database and the rest of the data resides in a SQL Server database (about 40%).
In order to report on all of the data (total company numbers), we use Microsoft Access to tie the two databases together. The final step is that we use a reporting software much like Access that
we use to build/display our reports (query design and sql set up much like access, but it does not have the capability to report on two databases, as expected). I am not a database expert.
I just handle reporting. My opinion is that reporting on two databases through Access is a terrible plan. Am I correct in saying that Sybase uses one SQL form of the language, SQL Server uses another
form of the language and Access uses a third. When we try to tie them through Access and then send the data through a reporting software, there are tons of issues. Am I correct in this and what is a solution? Can anyone help?
There are many ways to consolidate data for reporting issues. Your company has chosen one way - which you say has many issues but you provide no detail. Is there a better way? Probably - but "better" generally requires an investment
of time, money, and resources. It also requires someone with experience to evaluate your existing infrastructure and your organization's plans to determine what path is best. That is something that really cannot be done in a forum or in a question-and-answer