multiple database 
Author Message
 multiple database

Hi,

I need to transfer some records from one database to
another. Unfortunately not all the databases engine
support the insert into with the IN statement like as
follows:

INSERT INTO destination [IN externalDB] [(field1[, field2
[, ...]])]
SELECT [origin.]field1[, field2[, ...]
FROM tableexpression

at the moment, the first idea was to read the source
recordset row by row and execute a Insert command on a
different connection based on the values retrieved from
the current row.

I think that probably a more efficient approach could be
found. (using the recordset as table espression in insert
into statement?)

Thanks to all

Giuseppe



Tue, 26 Apr 2005 19:37:32 GMT
 multiple database

Hi Giuseppe,

Do you have SQL Server? If yes, then you could use DTS COM library. That
library, which comes with SQL Server, allows transfer data between different
data sources

--
Val Mazur
Microsoft MVP


Quote:
> Hi,

> I need to transfer some records from one database to
> another. Unfortunately not all the databases engine
> support the insert into with the IN statement like as
> follows:

> INSERT INTO destination [IN externalDB] [(field1[, field2
> [, ...]])]
> SELECT [origin.]field1[, field2[, ...]
> FROM tableexpression

> at the moment, the first idea was to read the source
> recordset row by row and execute a Insert command on a
> different connection based on the values retrieved from
> the current row.

> I think that probably a more efficient approach could be
> found. (using the recordset as table espression in insert
> into statement?)

> Thanks to all

> Giuseppe



Tue, 26 Apr 2005 20:59:26 GMT
 multiple database
Thanks Val,

no, unfortunately this application must work with several
database engines, in order to extract some subsets of data
from a large database and put them in a small local
MSAccess on portable PC. The user can go around to it's
customers, make some work, then go back and update the
main database. Due to the environment, no connection is
available by cable (no POP, no telephone), and it is too
expensive by satellite.

The idea is to put some further fields to manage status of
data (i.e. identify the changing).

Giuseppe

Quote:
>-----Original Message-----
>Hi Giuseppe,

>Do you have SQL Server? If yes, then you could use DTS
COM library. That
>library, which comes with SQL Server, allows transfer

data between different
Quote:
>data sources

>--
>Val Mazur
>Microsoft MVP



Tue, 26 Apr 2005 23:59:01 GMT
 multiple database
Giuseppe,

What about disconnected recordsets? You could disconnect recordsets to free
connection resources. Update recordsets locally, re-connect them to database
and call UpdateBatch

--
Val Mazur
Microsoft MVP


Quote:
> Thanks Val,

> no, unfortunately this application must work with several
> database engines, in order to extract some subsets of data
> from a large database and put them in a small local
> MSAccess on portable PC. The user can go around to it's
> customers, make some work, then go back and update the
> main database. Due to the environment, no connection is
> available by cable (no POP, no telephone), and it is too
> expensive by satellite.

> The idea is to put some further fields to manage status of
> data (i.e. identify the changing).

> Giuseppe

> >-----Original Message-----
> >Hi Giuseppe,

> >Do you have SQL Server? If yes, then you could use DTS
> COM library. That
> >library, which comes with SQL Server, allows transfer
> data between different
> >data sources

> >--
> >Val Mazur
> >Microsoft MVP



Wed, 27 Apr 2005 00:28:06 GMT
 multiple database
Hi Val,

I don't know if such approach could work... I think that the problem is
basically that I must retrieve the data from several tables. As example, you
must imagine that I have a system able to manage the real estate of several
companies: you have a group of table with the data for apartments available
for rent, another one group of table with contracts, another one with people
involved in contracts, maintenance and so on.

I have some agent able to go around to propose a door by door contract:
these agents must show the data of the apartments, the maintenance, and so
on. I'm oriented to give to this persons a subset of the main database and I
don't know if the disconnected recordset can accomply this stuff...

Moreover, one agent may spent two or three days before he can come back to
his company... Of course, it is possible to save the disconnected recordset
as a file, but I'm not sure that I can execute a query on the recordsets
(i.e. a join on apartments and assets recordsets).

Thank you for your time

bye

Giuseppe


Quote:
> Giuseppe,

> What about disconnected recordsets? You could disconnect recordsets to
free
> connection resources. Update recordsets locally, re-connect them to
database
> and call UpdateBatch



Wed, 27 Apr 2005 03:20:01 GMT
 multiple database


Thanks Val,

no, unfortunately this application must work with several
database engines, in order to extract some subsets of data
from a large database and put them in a small local
MSAccess on portable PC. The user can go around to it's
customers, make some work, then go back and update the
main database. Due to the environment, no connection is
available by cable (no POP, no telephone), and it is too
expensive by satellite.

The idea is to put some further fields to manage status of
data (i.e. identify the changing).

Giuseppe

Can you dynamically (or statically) link them to your Access database (via a
DSN) and then import the data to native Access tables? This will allow you to
work with Access SQL regardless of the data source.


Microsoft MVP (Visual Basic)



Wed, 27 Apr 2005 06:35:43 GMT
 multiple database

Hi Paul,

this is the current approach: link the table of interest to the "portable"
MSAccess where are the empty tables, then execute several SQL statement to
transfer the subset of data to the empty tables.

The problem is that at the moment the operators must execute all these
operation by hand (i.e. not automatically). I would like to have the
capability to do this automatically (i.e., chose the family of data subset
and activate an export procedure).

in a sort of pseudo-language it must work as follow:

1) in a form of my application, the user can choise the subset of data: i.e.
data about contracts and apartments of company A, B, C, or D

2) execute the export procedure:

2.1) copy the mdb file that works as template changing his name (i.e.
Bdata.mdb) - no problem
2.2a) link the table to Bdata.mdb (?) and execute the queries (I have
already these queries)

or

2.2b) retrieve the data from main database (OK) and send them to Bdata.mdb
without using a row by row insert into (I try this approach but it is very
slow!)

Thanks

Giuseppe



Wed, 27 Apr 2005 20:47:22 GMT
 multiple database
Hi Paul & Val

It seems that the one possible solution is based on the
Paul suggestion: create one MSAccess database with all the
table of the main database linked. Then, for each agent,
at run time:

1. create a MSAccess database in a specified folder (i.e.
C:\mycopies\)
2. execute on the first MSAccess database a query based on
the statement SELECT mytable.* INTO newtable
IN 'C:\mycopies\agent1.mdb' FROM mytable WHERE -
conditions relate to agent n.1-

The path in the IN statement is a string built at runtime.

It seems a little bit slow (3 databases involved!) but it
works...

Bye

Giuseppe

Quote:
>Can you dynamically (or statically) link them to your

Access database (via a
Quote:
>DSN) and then import the data to native Access tables?

This will allow you to
Quote:
>work with Access SQL regardless of the data source.


>Microsoft MVP (Visual Basic)
>.



Fri, 29 Apr 2005 22:02:12 GMT
 
 [ 8 post ] 

 Relevant Pages 

1. Multiple database - multiple query - multiple server

2. Multiple Databases vs. Multiple Tables

3. Multiple Tables v Multiple Databases

4. Multiple Databases/Multiple Servers

5. updating multiple databases on multiple servers with DTC

6. Multiple Databases on Multiple Servers?

7. Multiple Databases on Multiple Servers?

8. HELP! Multiple Databases/Multiple Servers Access

9. Linking to multiple tables in multiple databases

10. Multiple Databases/Multiple Instances

11. Multiple schemas versus multiple databases


 
Powered by phpBB® Forum Software