Page 1 of 1

querry from two separate databases

PostPosted: Wed Oct 08, 2014 1:02 pm
by Rodrigo
I want to build a query from two separate databases.

I have tried it in various ways.
For example:

Code: Select all
dbpOpenAccessDatabase "BD6Mov" "[PubDir]Bases\dbtest1.mdb" "Password=xxxxx"

dbpExecSQL "BD6Mov" "SELECT * |FROM Table1 A|INNER JOIN|[#91];database=[PubDir]dbtest2.mdb;PWD=xxxxx[#93].Table2 B|ON A.id = B.id;" "resultConsulta"


The result is an error "cannot perfom this operation on a closed dataset"

Is it possible to build this query using DBPro, and what is the correct way?

Re: querry from two separate databases

PostPosted: Thu Oct 09, 2014 8:19 am
by dpayer
Rodrigo wrote:I want to build a query from two separate databases.

I have tried it in various ways.
For example:

Code: Select all
dbpOpenAccessDatabase "BD6Mov" "[PubDir]Bases\dbtest1.mdb" "Password=xxxxx"

dbpExecSQL "BD6Mov" "SELECT * |FROM Table1 A|INNER JOIN|[#91];database=[PubDir]dbtest2.mdb;PWD=xxxxx[#93].Table2 B|ON A.id = B.id;" "resultConsulta"


The result is an error "cannot perfom this operation on a closed dataset"

Is it possible to build this query using DBPro, and what is the correct way?


You would need to open both databases, do your select in each and have the resultant table defined with a variable name. Then you can do your inner join from the two tables using the variable named tables.

David P.

Re: querry from two separate databases

PostPosted: Thu Oct 09, 2014 11:14 am
by Rodrigo
Thank you David.

:shock: I have no idea about how I can define the result of each query in a variable using DBPro.

Please could you post an example of the query?

Re: querry from two separate databases

PostPosted: Fri Oct 10, 2014 3:15 pm
by Neosoft Support
Maybe, try something like this:

dbpOpenAccessDatabase "BD6Mov" "[PubDir]Bases\dbtest1.mdb" "Password=xxxxx"
dbpOpenTable "BD6Mov" "Table1" ""
dbpOpenTable "BD6Mov" "Table2" ""
dbpExecSQL "BD6Mov" "SELECT * |FROM Table1 A|INNER JOIN|[#91];database=[PubDir]dbtest2.mdb;PWD=xxxxx[#93].Table2 B|ON A.id = B.id;" "resultConsulta"

Re: querry from two separate databases

PostPosted: Fri Oct 10, 2014 10:21 pm
by Rodrigo
Hi, thanks. The problem is that Table 2 is not in BD6Mov DB, then, when I run the query, dbpro returns error.

Re: querry from two separate databases

PostPosted: Sat Oct 11, 2014 9:55 am
by Neosoft Support
The tables must be in the same database.

Re: querry from two separate databases

PostPosted: Sun Oct 12, 2014 7:34 am
by Rodrigo
The FROM clause of the SQL statement accepts a fully qualified table name, which allows placement of the chain connecting square brackets in front of the table name. The connection string is separated from table name by a period. This method allows you to connect to several external databases simultaneously.

I had used this method before (eg from VB)

Code: Select all
SELECT
   A.field1, A.field2, B.Field
FROM
   Table1 A
INNER JOIN
[MS Access;database=C:\database2.mdb;PWD=abcd1234].Table2 B ON A.id = B.id;


I assumed that I could build this simple query (using dbpExecSQL).

Anyway thanks for your answers.

Re: querry from two separate databases

PostPosted: Sun Oct 12, 2014 4:50 pm
by Gaev
Rodrigo:

I did a Google search for "ms access join tables in separate databases" ... and from reading the results, this MIGHT work ...
Code: Select all
    SELECT
       A.field1, A.field2, B.Field
    FROM
       Table1 A
    INNER JOIN
       (SELECT * FROM Table2 IN '' [MS Access;database=C:\database2.mdb;PWD=abcd1234]) B ON A.id = B.id;


According to one of the responses ... the 2 single quotes after IN must be there.

Re: querry from two separate databases

PostPosted: Wed Oct 15, 2014 7:22 pm
by Rodrigo
Gaev wrote:Rodrigo:

I did a Google search for "ms access join tables in separate databases" ... and from reading the results, this MIGHT work ...
Code: Select all
    SELECT
       A.field1, A.field2, B.Field
    FROM
       Table1 A
    INNER JOIN
       (SELECT * FROM Table2 IN '' [MS Access;database=C:\database2.mdb;PWD=abcd1234]) B ON A.id = B.id;


According to one of the responses ... the 2 single quotes after IN must be there.


Thanks Gaev.

The solution you proposed, it should work, just as I posted. I've really tried two and more. But apparently it is not possible to build a query from two different databases using DBPro (I hope I'm wrong :| )

I came up with a tangled solution: Make separate queries and then populate a temporary table with the result of both.
A real mess !.

Hopefully appears a better idea.

Re: querry from two separate databases

PostPosted: Thu Oct 16, 2014 10:18 am
by Neosoft Support
DBPro just passes the SQL code through to the database engine (Access, mySQL, etc.) via an ADO/ODBC interface. DBPro doesn't really have anything to do with the actual processing of the SQL commands. Also, each database engine handles SQL slightly differently, and the extra ADO/ODBC layer plays a role too.

What you should do is try executing your SQL from within MS Access to get it to work there (if possible) before trying it from DBPro. If it works in Access but not DBPro, you could try creating a stored procedure in Access and executing it from DBPro. Information about stored procedures in Access can be found here: http://stackoverflow.com/questions/3287545/how-do-i-make-a-stored-procedure-in-ms-access

Re: querry from two separate databases

PostPosted: Thu Oct 16, 2014 10:44 am
by Rodrigo
Neosoft Support wrote:DBPro just passes the SQL code through to the database engine (Access, mySQL, etc.) via an ADO/ODBC interface. DBPro doesn't really have anything to do with the actual processing of the SQL commands. Also, each database engine handles SQL slightly differently, and the extra ADO/ODBC layer plays a role too.

What you should do is try executing your SQL from within MS Access to get it to work there (if possible) before trying it from DBPro. If it works in Access but not DBPro, you could try creating a stored procedure in Access and executing it from DBPro. Information about stored procedures in Access can be found here: http://stackoverflow.com/questions/3287545/how-do-i-make-a-stored-procedure-in-ms-access


Thank you very much!

Both databases are not distributed along with the application.

For various reasons, both databases are built by the NeoBook application when run for the first time (dbpCreateAccessDatabase). So I discarded a link to another database from access directly, or stored procedure.

I keep trying other techniques. I will report if I find a way to do it.