Multiple databases

[rwestein]

Hi,

I want to create a database with data from various sources.

1. Source ERP, that would be a Base db connected by ODBC to SQL Server (on SQL Server no foreign tables can be created).

2. User has to add some data to tables. I would like to offer the user a nice data-entry form in Base (Base db HSQLDB).

3. Create a Calc file which will contain all data from the previous 2 Base files.

4. A Base file which has the Calc file as datasource and will connect the datasources together and create a report out of it.

So with this solution, I need 3 Base files and 1 Calc file. That is quite a lot of files to create a report. Does someone have a better idea to solve this and use fewer Base files?

I hope my question is clear. Thanks.

Rob

[Ratslinger]

Hello,

Seems the biggest problem here is the server where it must be some policy not allowing additional tables to be created. This could be replaced by retrieving necessary data via SQL in a macro. Create a view or temp table based upon this data.

So now in a single Base file, the user creates their tables, using SQL in a macro (probably executed from a push button or some other trigger) you can retrieve the data from server, Calc is not needed at all & now you can generate wanted report(s).

One Base file & a macro.

[jimk]

Agreed, using a macro to read from SQL Server is the solution. I understand about the temp table, but… “Create a view… based upon this data.” Does that mean there is a way to create a view instead of copying the data into an HSQLDB table? That sounds ideal for large databases, but I’m not sure it works that way. Perhaps you could describe or point to a link on how to create a view from data retrieved by a macro, which could then be joined in a query to an HSQLDB table.

[Ratslinger]

No basis for view but have done other things which had no basis. Can’t say it’s not possible. Also isn’t a View just selective information from tables? Yes, there is no immediate changes from the tables but you can selectively choose what is retrieved in the macro. Maybe no cigar but close.

[rwestein]

I’ve seen some code to connect to an ODBC source, so that is solved. I’m not sure on how to create a view though. Have to look into it. I think the best way is do copy a selection of the ERP data to a HSQLDB table.