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;].Table1 from CONTCONTA');
LVL 1
hidrauAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Eddie ShipmanAll-around developerCommented:
what error?
0
ziolkoCommented:
i use this for excel, but maybe it will point you somewhere.

  SQL_1 = 'SELECT * INTO [Excel 8.0;Database="Z:\TestProjects\Editors\book2.xls"].[MySheet] FROM Tab1';
  SQL_2 = 'insert INTO OPENDATASOURCE'+
          '(''Microsoft.Jet.OLEDB.4.0'',''Data Source="Z:\TestProjects\Editors\MyTest.xls";Extended properties=Excel 8.0'')...[MySheet$] '+
          'select ID, Col30, Col40, Col90, Col180, Col230 FROM dat0003';

ziolko.
0
ziolkoCommented:
this works for me:
const
  SQL_21 = 'insert INTO OPENDATASOURCE'+
          '(''Microsoft.Jet.OLEDB.4.0'',''Data Source="C:\Documents and Settings\Lukasz\Desktop\nwind.mdb";'')...[MySheet4] '+
          'select Col1, Col2 FROM dat0001';

procedure TForm2.Button3Click(Sender: TObject);
var rf: Integer;
begin
  ADOConnection1.LoginPrompt:=False;
  ADOConnection1.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Lukasz\Desktop\nwind.mdb;';
  ADOConnection1.Open;
  ADOConnection1.Execute('create table MySheet4 ('+
      'Col1 varchar(255),'+ //char(10)
          'Col2 varchar(255)'+ //char(10)
      ')',rf);

  ADOConnection1.Close;
  ADOConnection1.LoginPrompt:=True;
  ADOConnection1.ConnectionString:='Provider=SQLOLEDB.1;Persist Security Info=True;Initial Catalog=SDB;Data Source=lukaszz\sql2k;';
  ADOConnection1.Execute(SQL_21,rf);
  ADOConnection1.Close;
end;


ziolko.
0
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

hidrauAuthor Commented:
This line is not working

  With ADOCommand1 do
  Begin
    Str := 'SELECT * INTO [ODBC;Driver=Microsoft Access Driver [*.mdb]; DATABASE=c:\bdcc.MDB;].Table1 from CONTCONTA';
    CommandText := Str;
    Execute
 End;

I have this mdb file in my c:\
Maybe something I am doing wrongly
0
Eddie ShipmanAll-around developerCommented:
ziolko,
 I get a syntax error trying that one.
0
ziolkoCommented:
syntax error? in which point?
for me it works without any problem

ziolko.
0
Eddie ShipmanAll-around developerCommented:
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.
0
ziolkoCommented:
i'm confused... did you try to execute this SQL command from query analyzer?

ziolko.
0
Eddie ShipmanAll-around developerCommented:
NO. I'm not trying to run from QA, I'm trying to run from Delphi like you posted.
0
hidrauAuthor Commented:
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?

0
hidrauAuthor Commented:
Did you test in your computer your code? did it work?
0
ziolkoCommented:
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.
0
Eddie ShipmanAll-around developerCommented:
yes, that is what the OP wanted to do based on his other post:
http://www.experts-exchange.com/Programming/Languages/Pascal/Delphi/Q_22388210.html

If he doesn't know the field beforehand, this should just create the table for him.
0
Eddie ShipmanAll-around developerCommented:
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.
0
ziolkoCommented:
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.
0
Eddie ShipmanAll-around developerCommented:
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;].Orders';

procedure TForm1.Button1Click(Sender: TObject);
var rf: Integer;
begin
  ADOConnection1.Open;
  ADOConnection1.Execute(SQL_21,rf);
  ADOConnection1.Close;
end;

0
Eddie ShipmanAll-around developerCommented:
This code also creates the LocalServer DSN on the fly and then removes it when it is done.

const
  SQL_21 = 'SELECT * INTO Orders_Backup FROM [odbc;DSN=LocalServer;' +
           'Database=northwind;].Orders';

procedure TForm1.Button1Click(Sender: TObject);
var
  rf: Integer;
  Reg: TRegistry;
begin
  Reg := TRegistry.Create;
  try
    with Reg do
    begin
      RootKey := HKEY_LOCAL_MACHINE;
      OpenKey('Software\ODBC\ODBC.INI\Localserver', True); // Will create it if it doesn't exist
      WriteString('Database', 'Northwind');
      WriteString('Description', 'LocalServer');
      WriteString('Driver', 'C:\WINNT\system32\SQLSRV32.dll');
      WriteString('Server', '(local)');
      WriteString('Trusted_connection', 'Yes');
      CloseKey;
      RootKey := HKEY_LOCAL_MACHINE;
      OpenKey('SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources', false); // do not allow create in this
                                                                  // case since it should already exist?
      WriteString('LocalServer', 'SQL Server');
      CloseKey;
    end;

    ADOConnection1.Open;
    ADOConnection1.Execute(SQL_21,rf);
    ADOConnection1.Close;
  finally
    with Reg do
    begin
      DeleteKey('Software\ODBC\ODBC.INI\Localserver');
      OpenKey('SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources', false);
      DeleteValue('LocalServer');
      CloseKey;
      Free;
    end;
  end;
end;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.