hidrau
asked on
Export MS SQL Query Result to MDB file
Hello Guys,
I need to export a result from a query to mdb access, I am trying this syntax but it is not working.
Sql.Add('SELECT * INTO [ODBC;Driver=Microsoft Access Driver [*.mdb]; DATABASE=c:\Base.MDB;].Tab le1 from CONTCONTA');
I need to export a result from a query to mdb access, I am trying this syntax but it is not working.
Sql.Add('SELECT * INTO [ODBC;Driver=Microsoft Access Driver [*.mdb]; DATABASE=c:\Base.MDB;].Tab
what error?
i use this for excel, but maybe it will point you somewhere.
SQL_1 = 'SELECT * INTO [Excel 8.0;Database="Z:\TestProje cts\Editor s\book2.xl s"].[MyShe et] FROM Tab1';
SQL_2 = 'insert INTO OPENDATASOURCE'+
'(''Microsoft.Jet.OLEDB.4. 0'',''Data Source="Z:\TestProjects\Ed itors\MyTe st.xls";Ex tended properties=Excel 8.0'')...[MySheet$] '+
'select ID, Col30, Col40, Col90, Col180, Col230 FROM dat0003';
ziolko.
SQL_1 = 'SELECT * INTO [Excel 8.0;Database="Z:\TestProje
SQL_2 = 'insert INTO OPENDATASOURCE'+
'(''Microsoft.Jet.OLEDB.4.
'select ID, Col30, Col40, Col90, Col180, Col230 FROM dat0003';
ziolko.
this works for me:
const
SQL_21 = 'insert INTO OPENDATASOURCE'+
'(''Microsoft.Jet.OLEDB.4. 0'',''Data Source="C:\Documents and Settings\Lukasz\Desktop\nw ind.mdb";' ')...[MySh eet4] '+
'select Col1, Col2 FROM dat0001';
procedure TForm2.Button3Click(Sender : TObject);
var rf: Integer;
begin
ADOConnection1.LoginPrompt :=False;
ADOConnection1.ConnectionS tring:='Pr ovider=Mic rosoft.Jet .OLEDB.4.0 ;Data Source=C:\Documents and Settings\Lukasz\Desktop\nw ind.mdb;';
ADOConnection1.Open;
ADOConnection1.Execute('cr eate table MySheet4 ('+
'Col1 varchar(255),'+ //char(10)
'Col2 varchar(255)'+ //char(10)
')',rf);
ADOConnection1.Close;
ADOConnection1.LoginPrompt :=True;
ADOConnection1.ConnectionS tring:='Pr ovider=SQL OLEDB.1;Pe rsist Security Info=True;Initial Catalog=SDB;Data Source=lukaszz\sql2k;';
ADOConnection1.Execute(SQL _21,rf);
ADOConnection1.Close;
end;
ziolko.
const
SQL_21 = 'insert INTO OPENDATASOURCE'+
'(''Microsoft.Jet.OLEDB.4.
'select Col1, Col2 FROM dat0001';
procedure TForm2.Button3Click(Sender
var rf: Integer;
begin
ADOConnection1.LoginPrompt
ADOConnection1.ConnectionS
ADOConnection1.Open;
ADOConnection1.Execute('cr
'Col1 varchar(255),'+ //char(10)
'Col2 varchar(255)'+ //char(10)
')',rf);
ADOConnection1.Close;
ADOConnection1.LoginPrompt
ADOConnection1.ConnectionS
ADOConnection1.Execute(SQL
ADOConnection1.Close;
end;
ziolko.
ASKER
This line is not working
With ADOCommand1 do
Begin
Str := 'SELECT * INTO [ODBC;Driver=Microsoft Access Driver [*.mdb]; DATABASE=c:\bdcc.MDB;].Tab le1 from CONTCONTA';
CommandText := Str;
Execute
End;
I have this mdb file in my c:\
Maybe something I am doing wrongly
With ADOCommand1 do
Begin
Str := 'SELECT * INTO [ODBC;Driver=Microsoft Access Driver [*.mdb]; DATABASE=c:\bdcc.MDB;].Tab
CommandText := Str;
Execute
End;
I have this mdb file in my c:\
Maybe something I am doing wrongly
ziolko,
I get a syntax error trying that one.
I get a syntax error trying that one.
syntax error? in which point?
for me it works without any problem
ziolko.
for me it works without any problem
ziolko.
Syntax error in INSERT INTO statement is all it says.
const
SQL_21 = 'insert INTO OPENDATASOURCE'+
'(''Microsoft.Jet.OLEDB.4. 0'',''Data Source="C:\Base.mdb";'').. .[Table1] '+
'select * FROM Orders';
procedure TForm1.Button1Click(Sender : TObject);
var
rf: Integer;
begin
ADOConnection1.Open;
ADOConnection1.Execute(SQL _21,rf);
ADOConnection1.Close;
end;
ADOConnection points to Northwind DB on SQL 2K.
C:\Base.mdb is an EMPTY Access DB.
const
SQL_21 = 'insert INTO OPENDATASOURCE'+
'(''Microsoft.Jet.OLEDB.4.
'select * FROM Orders';
procedure TForm1.Button1Click(Sender
var
rf: Integer;
begin
ADOConnection1.Open;
ADOConnection1.Execute(SQL
ADOConnection1.Close;
end;
ADOConnection points to Northwind DB on SQL 2K.
C:\Base.mdb is an EMPTY Access DB.
i'm confused... did you try to execute this SQL command from query analyzer?
ziolko.
ziolko.
NO. I'm not trying to run from QA, I'm trying to run from Delphi like you posted.
ASKER
EddieShipman:
I am testing your code and let me know some things.
const
SQL_21 = 'insert INTO OPENDATASOURCE'+
'(''Microsoft.Jet.OLEDB.4. 0'',''Data Source="C:\Base.mdb";'').. .[Table1] '+
'select * FROM Orders';
In your code you use insert into opendatasource.
In my base.mdb that is in c:\ doesn't have any table inside, it is an empty database. When you use insert, the code will create this table result, is that?
I tried to run your code but I am getting an error from delphi:
[OLE/DB provider returned message: It is not possible to find the file 'c:\base.mdb'.]
the is file is in C:\
what must be wrong?
I am testing your code and let me know some things.
const
SQL_21 = 'insert INTO OPENDATASOURCE'+
'(''Microsoft.Jet.OLEDB.4.
'select * FROM Orders';
In your code you use insert into opendatasource.
In my base.mdb that is in c:\ doesn't have any table inside, it is an empty database. When you use insert, the code will create this table result, is that?
I tried to run your code but I am getting an error from delphi:
[OLE/DB provider returned message: It is not possible to find the file 'c:\base.mdb'.]
the is file is in C:\
what must be wrong?
ASKER
Did you test in your computer your code? did it work?
Eddie confirm if i'm right
you executed insert into sql on empty access (so no table1 defined) without
executing create table sql before?
and second thing, try it in QA because if it's syntax QA will also throw error, hopefully
more detailed one:)
ziolko.
you executed insert into sql on empty access (so no table1 defined) without
executing create table sql before?
and second thing, try it in QA because if it's syntax QA will also throw error, hopefully
more detailed one:)
ziolko.
yes, that is what the OP wanted to do based on his other post:
https://www.experts-exchange.com/questions/22388210/exporting-data-from-mssql-query-to-access-database.html
If he doesn't know the field beforehand, this should just create the table for him.
https://www.experts-exchange.com/questions/22388210/exporting-data-from-mssql-query-to-access-database.html
If he doesn't know the field beforehand, this should just create the table for him.
QA throws this error:
Invalid object name 'Table1'.
Most certainly because I did not create it beforehand. But, like I just said, the OP
wants the SELECT INTO to create it.
Invalid object name 'Table1'.
Most certainly because I did not create it beforehand. But, like I just said, the OP
wants the SELECT INTO to create it.
well if theres no Table1 in access db no wonder that this part is not working :)
thats why i posted also method to create table in access:)
ziolko.
thats why i posted also method to create table in access:)
ziolko.
Ok, now, if you set the ADOConnection to point to the ACCESS database, then this works just like the OP requires. I also had to setup an ODBC DSN called "LocalServer" to point to my local SQL Server.
const
SQL_21 = 'SELECT * INTO Orders_Backup FROM [odbc;DSN=LocalServer;' +
'Database=northwind;].Orde rs';
procedure TForm1.Button1Click(Sender : TObject);
var rf: Integer;
begin
ADOConnection1.Open;
ADOConnection1.Execute(SQL _21,rf);
ADOConnection1.Close;
end;
const
SQL_21 = 'SELECT * INTO Orders_Backup FROM [odbc;DSN=LocalServer;' +
'Database=northwind;].Orde
procedure TForm1.Button1Click(Sender
var rf: Integer;
begin
ADOConnection1.Open;
ADOConnection1.Execute(SQL
ADOConnection1.Close;
end;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.