Import from one database to another 3

[petermeachem]

I've a problem with a VB6 ADO programme. I have two connections, one to databaseA and one to databaseB. DatabaseB happens to be Access, DatabaseA can be anything at all. I am building a query on tables in DatabaseA by selecting tables and fields, so I end up with a string representing a select query on DatabaseA. What I want to do is to import the data into DatabaseB. I've ended up doing a creating a table in DatabaseB with the right field names and made them all big text fields. I am looping on the select query and doing an addnew into DatabaseB.
This seems a bit crummy.
What I would like to do is
SELECT Fielda,Fieldb FROM TableA in DatabaseA INTO DatabaseB.

How can I do that?

I'm going to cross post this in a sql forum.
Peter Meachem
peter@accuflight.com

 

[bjrollet]

What is so crummy about it? It's a pretty easy and effective way to do it, as you can check for record duplicity as you go.

 

[petermeachem]

Crummy Thing 1) The fields are all text 255. For all I know they are supposed to be integer.

Crummy Thing 2) There could be a lot of records here. The data is names and addresses. Although UK is quite small, we have a lot of addresses. Running a query should be much faster.

Mind you, you should see the rest of the stuff I produce. Peter Meachem
peter@accuflight.com

 

[bjrollet]

That being the case, cant you perform a "connection.execute "SQL Statement"?

 

[petermeachem]

There are two connections. One to databasea and another to b. Neither knows anything about the database on the other connection. Peter Meachem
peter@accuflight.com

 

[bjrollet]

Then I would say that you couldn't do it, as a SQL statement acts only on data in a single database. I really don't see how it can be done in a single statement.

 

[CCLINT]

In order to select records from one db into records in another db use the IN identifier in the FROM or INTO part of the SELECT statment (DAO - add a reference to the DAO 3.52 - but also should work for ADO - may need to change the connection string):


[SELECT | INSERT] INTO Table IN
{Path| ["Path" "Type"] | ["" [Type; DATABASE = Path]]}

FROM Table IN
{Path| ["Path" "Type"] | ["" [Type; DATABASE = Path]]}

For Instance, to insert fields into an MDB table from a text file you would use the following:

SELECT INTO myTable (myField1, myField2,...)
FROM [myTextdb.txt] IN '' [TEXT;DATABASE= myPath;];"

Or when working with 2 MDBs:

SELECT [CustomerID]
FROM Customer
IN OtherDB.mdb
WHERE [CustomerID] Like "A*";

(OtherDB.mdb would be the path and name)

Or an MDB and Paradox db:

SELECT [CustomerID]
FROM Customer
IN "C:\Path" "Paradox X.xx"
WHERE [CustomerID] Like "A*";

(X.xx being the version)

The above could be written like this:
SELECT [CustomerID]
FROM Customer
IN "" [Paradox X.xx;Datenbank=C:\PARADOX\DATA\SALES;]
WHERE [CustomerID] Like "A*";

 

[CCLINT]

In the example:
SELECT INTO myTable (myField1, myField2,...)
FROM [myTextdb.txt] IN '' [TEXT;DATABASE= myPath;];"

It should read:
SELECT myTable.myField1, myTable.myField2
INTO myTable
FROM [myTextdb.txt] IN '' [TEXT;DATABASE= myPath;];"

OR
INSERT INTO myTable (myField1, myField2,...)
INTO myTable
SELECT myField1, myField2,...
FROM [myTextdb.txt] IN '' [TEXT;DATABASE= myPath;];"

 

[petermeachem]

Thanks, I'll try that and see if it is quicker. Peter Meachem
peter@accuflight.com

 

[CCLINT]

Typo above in my first response: reference DAO 3.51 (not 3.52 - never existed)

 

[Ek1mu5]

I'm doing the same thing on Access and Excel.

I use ADO but it can't seem to maintain the field
formats/data types of the converted database.

Data comes out in General format on Excel, not the way it should be (i.e. Date, Double, Integer).

Since my app requires sums of the currency fields in the excel spreadsheet.

Thanks in advance.