Source language: Translate to:

querry from two separate databases

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

querry from two separate databases

Postby Rodrigo » Wed Oct 08, 2014 1:02 pm

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?
After the game, the nerd and the ignorant will return to the same box
User avatar
Rodrigo
 
Posts: 29
Joined: Wed May 30, 2012 6:23 am
Location: Chile

Re: querry from two separate databases

Postby dpayer » Thu Oct 09, 2014 8:19 am

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.
David Payer
Des Moines, Iowa
USA
User avatar
dpayer
 
Posts: 1382
Joined: Mon Apr 11, 2005 5:55 am
Location: Iowa - USA

Re: querry from two separate databases

Postby Rodrigo » Thu Oct 09, 2014 11:14 am

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?
After the game, the nerd and the ignorant will return to the same box
User avatar
Rodrigo
 
Posts: 29
Joined: Wed May 30, 2012 6:23 am
Location: Chile

Re: querry from two separate databases

Postby Neosoft Support » Fri Oct 10, 2014 3:15 pm

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"
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5602
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Re: querry from two separate databases

Postby Rodrigo » Fri Oct 10, 2014 10:21 pm

Hi, thanks. The problem is that Table 2 is not in BD6Mov DB, then, when I run the query, dbpro returns error.
After the game, the nerd and the ignorant will return to the same box
User avatar
Rodrigo
 
Posts: 29
Joined: Wed May 30, 2012 6:23 am
Location: Chile

Re: querry from two separate databases

Postby Neosoft Support » Sat Oct 11, 2014 9:55 am

The tables must be in the same database.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5602
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Re: querry from two separate databases

Postby Rodrigo » Sun Oct 12, 2014 7:34 am

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.
After the game, the nerd and the ignorant will return to the same box
User avatar
Rodrigo
 
Posts: 29
Joined: Wed May 30, 2012 6:23 am
Location: Chile

Re: querry from two separate databases

Postby Gaev » Sun Oct 12, 2014 4:50 pm

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.
User avatar
Gaev
 
Posts: 3728
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: querry from two separate databases

Postby Rodrigo » Wed Oct 15, 2014 7:22 pm

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.
After the game, the nerd and the ignorant will return to the same box
User avatar
Rodrigo
 
Posts: 29
Joined: Wed May 30, 2012 6:23 am
Location: Chile

Re: querry from two separate databases

Postby Neosoft Support » Thu Oct 16, 2014 10:18 am

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
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5602
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Re: querry from two separate databases

Postby Rodrigo » Thu Oct 16, 2014 10:44 am

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.
After the game, the nerd and the ignorant will return to the same box
User avatar
Rodrigo
 
Posts: 29
Joined: Wed May 30, 2012 6:23 am
Location: Chile


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 3 guests

cron