145cfc
asked on
MS Access tables as objects using ADO
Hi - is it possible to copy an entire table (structure and data) from one MS Access database to another using Delphi?
Details:
I'm having two access databases - one for a sports administration system and one for a statistical records on players, teams etc. The stats-db is frequently uploaded to a webserver to provide stats for the public and on each upload I would like to synchronise eg. the team and the tournament table from the administration database to the stats-database in order to have all new fields and data in the stats database.
I am using ADO to connect to the databases and Delphi 5. Any solution using tadotables or tadoquerys (SQL commands also welcome) are welcome.
Best regards
Claus
Details:
I'm having two access databases - one for a sports administration system and one for a statistical records on players, teams etc. The stats-db is frequently uploaded to a webserver to provide stats for the public and on each upload I would like to synchronise eg. the team and the tournament table from the administration database to the stats-database in order to have all new fields and data in the stats database.
I am using ADO to connect to the databases and Delphi 5. Any solution using tadotables or tadoquerys (SQL commands also welcome) are welcome.
Best regards
Claus
I forgot to mension that the column count and types should be identical. Otherwise, you can do it like this
INSERT INTO table2 (Col1, Col2, Col3)
SELECT Column1, Column2, Column3
FROM table1
This is the case when all column types are idential. But If your Column3 is INTEGER and Col3 is VARCHAR, you can use CASE
INSERT INTO table2 (Col1, Col2, Col3)
SELECT Column1, Column2, CAST(Column3 AS VARCHAR)
FROM table1
INSERT INTO table2 (Col1, Col2, Col3)
SELECT Column1, Column2, Column3
FROM table1
This is the case when all column types are idential. But If your Column3 is INTEGER and Col3 is VARCHAR, you can use CASE
INSERT INTO table2 (Col1, Col2, Col3)
SELECT Column1, Column2, CAST(Column3 AS VARCHAR)
FROM table1
ASKER
Hi Ivanov - thank you for your reply. I could write a routine to check the columns of my dest-table and change/create/delete columns to make it match my source-table and then use the SQL Insert function.
What I'm more specifically looking for is to know wheter ADO grants me any functions to copy a table as an object and inserting it into another database (both ms access).
TY
Claus
What I'm more specifically looking for is to know wheter ADO grants me any functions to copy a table as an object and inserting it into another database (both ms access).
TY
Claus
Probably you can if you loop the table inside datasets... but I don't think this is very useful...
Hi
Use Select ... into .. from clause to create new table with data from another table
like this
Select * into newEmp from Employees
Drop Adoconnection and AdoCommand objects on the form, then use this code to create new table from anthor table
AdoCommand1.CommandText := 'Select * into newEmp from Employees';
AdoCommand1.Execute;
This code valid for MS Access and Sql server, for oracle use "Create table NewEmp as select * from Emp" to do the same job
HTH
Regards,
Mohammed
Use Select ... into .. from clause to create new table with data from another table
like this
Select * into newEmp from Employees
Drop Adoconnection and AdoCommand objects on the form, then use this code to create new table from anthor table
AdoCommand1.CommandText := 'Select * into newEmp from Employees';
AdoCommand1.Execute;
This code valid for MS Access and Sql server, for oracle use "Create table NewEmp as select * from Emp" to do the same job
HTH
Regards,
Mohammed
ASKER
Hi Mohammed - thank you - but I do not think that your solution works in my situation as the table should be copied from one database to another - not stay within the same database.
What I'm more specifically looking for is to know wheter ADO grants me any functions to copy a table as an object and inserting it into another database (both ms access) or another solution without ADO that makes it easy to copy a table object from one Access database to another. As of now I'm manually selecting (from within access) the table in my source database, copying it, opening the destination database, deleting the existing table and inserting the copied one - thus completely replacing the table in my dest. database.
TY
Claus
What I'm more specifically looking for is to know wheter ADO grants me any functions to copy a table as an object and inserting it into another database (both ms access) or another solution without ADO that makes it easy to copy a table object from one Access database to another. As of now I'm manually selecting (from within access) the table in my source database, copying it, opening the destination database, deleting the existing table and inserting the copied one - thus completely replacing the table in my dest. database.
TY
Claus
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi calinutz - does automation work in Delphi? I searched a little on the net and it seems to me that it is a VB function?
INSERT INTO table2
SELECT * FROM tabl1