Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

MS Access tables as objects using ADO

Posted on 2005-03-12
10
Medium Priority
?
601 Views
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?

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
0
Comment
Question by:145cfc
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 12

Expert Comment

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

INSERT INTO table2
SELECT * FROM tabl1
0
 
LVL 12

Expert Comment

by:Ivanov_G
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
0
 

Author Comment

by:145cfc
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).

TY

Claus
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 12

Expert Comment

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

Expert Comment

by:Mohammed Nasman
ID: 13528007
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
0
 

Author Comment

by:145cfc
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.

TY

Claus
0
 
LVL 11

Accepted Solution

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

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
0
 

Author Comment

by:145cfc
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?
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses
Course of the Month11 days, 18 hours left to enroll

564 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