Board index » delphi » How to copy records from a SQL Server Table to a Access Table

2004-05-11 11:23:37 AM
delphi236
can i use SQL?
or anthing else?
 

I would use the DTS from the MS SQL.
George Christoforakis
"luo" <XXXX@XXXXX.COM>writes

but how to get this with delphi?
 

Are you trying to do this automatically, or manually ? If manual, then all
you need is an ODBC connection to SQL Server and then link the SQL Server
tables within access itself. Open the SQL Server table, copy and paste to
access. If you want to do it automatically, then you could actually do the
same with Access macros - but I don't know the commands. If you are trying
to do it in Delphi, then you just need 2 BDE aliases, one to Access and the
other to SQL Server, then have 2 TDatabase components in your Delphi project
to open each one and then use TTables / TQueries to get data from one and
post to the other.
"luo" <XXXX@XXXXX.COM>writes

luo writes:
Google for examples (I've posted a couple).
--
jc
Remove the -not from email
 

This procedure does the trick:
procedure MoveTableFromODBC2Access(MDBConnection : TADOConnection;
const AConnectionString, ATableName : string);
var
ACatalog : _Catalog;
ATable : _Table;
begin
ACatalog := CoCatalog.Create;
ACatalog.Set_ActiveConnection(MDBConnection.ConnectionObject);
ATable := CoTable.Create;
ATable.ParentCatalog := ACatalog;
ATable.Name := 'SourceTable';
ATable.Properties['Jet OLEDB:Create Link'].Value := True;
ATable.Properties['Jet OLEDB:Link Provider String'].Value :=
AConnectionString;
ATable.Properties['Jet OLEDB:Remote Table Name'].Value := ATableName;
ACatalog.Tables.Append(ATable);
MDBConnection.Execute('SELECT * INTO ' + ATableName + ' FROM
SourceTable');
MDBConnection.Execute('DROP TABLE SourceTable');
end;
Assumptions: MDBConnection is connected to mdb file and active. Target mdb
file doesn't have tables with name [SourceTable] and value passed in
ATableName.
Example of usage:
MoveTableFromODBC2Access(MDBConnection, 'ODBC;DRIVER=SQL
Server;SERVER=(local);DATABASE=Northwind', 'Products');//Windows integrated
security
MoveTableFromODBC2Access(MDBConnection, 'ODBC;DRIVER=SQL
Server;SERVER=(local);UID=sa;PWD=simsim;DATABASE=Northwind',
'Products');//MS SQL Server security
Regards,
Vitali
"luo" <XXXX@XXXXX.COM>ÓÏÏÂÝÉ?ÓÏÏÂÝÉÌÁ ?ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅ?

Forgot to mention you will need to import ADOX type library and append it to
uses clause.
"Vitali Kalinin" <XXXX@XXXXX.COM>ÓÏÏÂÝÉ?ÓÏÏÂÝÉÌÁ ?ÎÏ×ÏÓÔÑÈ
ÓÌÅÄÕÀÝÅ? news:XXXX@XXXXX.COM...

Even easer:
cmd := 'SELECT * INTO [authors] FROM [ODBC;DRIVER=SQL
Server;SERVER=(local);DATABASE=pubs;].[authors]';
ADOConnection1.Execute(cmd, RecAffected);
Generally it should be better to enclose table name into brackets:
'SELECT * INTO [' + ATableName + '] FROM '....
//------------------------------------------
Regards,
Vassiliev V. V.
www.managed-vcl.com - using .Net objects in Delphi for Win32 +
ADO.Net
www.oledbdirect.com - The fastest way to access MS SQL Server,
MS Jet (Access) and Interbase (through OLEDB)
"Vitali Kalinin" <XXXX@XXXXX.COM>ÓÏÏÂÝÉ?ÓÏÏÂÝÉÌÁ ?ÎÏ×ÏÓÔÑÈ
ÓÌÅÄÕÀÝÅ? news:XXXX@XXXXX.COM...

Very neat!
"Viatcheslav V. Vassiliev" <XXXX@XXXXX.COM>ÓÏÏÂÝÉ?ÓÏÏÂÝÉÌÁ ?
ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅ? news:XXXX@XXXXX.COM...

thanks a lot!
 

Dataset for data transfer.
HTH,
Asim Khan
--- posted by geoForum on delphi.newswhat.com