MS Access tables as objects using ADO

Posted on 2005-03-12
Medium Priority
Last Modified: 2010-04-05
Hi - is it possible to copy an entire table (structure and data) from one MS Access database to another using Delphi?

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

Question by:145cfc
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
LVL 12

Expert Comment

ID: 13524617
try with INSERT INTO ... SELECT query, but I am not quite sure if Access support it.

LVL 12

Expert Comment

ID: 13524623
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

Author Comment

ID: 13525789
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).


Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 12

Expert Comment

ID: 13525847
Probably you can if you loop the table inside datasets... but I don't think this is very useful...
LVL 22

Expert Comment

by:Mohammed Nasman
ID: 13528007

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';

This code valid for MS Access and Sql server, for oracle use "Create table NewEmp as select * from Emp" to do the same job



Author Comment

ID: 13528215
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.


LVL 11

Accepted Solution

calinutz earned 1600 total points
ID: 13528354
Did you try automation?
After opening the source database like:
  AccessApplication1.Visible := True;
  AccessApplication1.OpenCurrentDatabase('C:\SourceFolder\YourDB.mdb', True);

Try it... I do not know much about the parameters but i'll look into it maybe tomorow.
Firt param is the destination DB, the second is the new name for the object... and the rest you could figure out by yourself.
Good luck

Author Comment

ID: 13659636
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?

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question