Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Export MS SQL Query Result to MDB file

Posted on 2007-03-28
17
Medium Priority
?
436 Views
Last Modified: 2010-05-18
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');
0
Comment
Question by:hidrau
[X]
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
  • 8
  • 6
  • 3
17 Comments
 
LVL 26

Expert Comment

by:EddieShipman
ID: 18812169
what error?
0
 
LVL 21

Expert Comment

by:ziolko
ID: 18814284
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
 
LVL 21

Expert Comment

by:ziolko
ID: 18814377
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 1

Author Comment

by:hidrau
ID: 18814994
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
 
LVL 26

Expert Comment

by:EddieShipman
ID: 18816194
ziolko,
 I get a syntax error trying that one.
0
 
LVL 21

Expert Comment

by:ziolko
ID: 18818562
syntax error? in which point?
for me it works without any problem

ziolko.
0
 
LVL 26

Expert Comment

by:EddieShipman
ID: 18818838
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
 
LVL 21

Expert Comment

by:ziolko
ID: 18821867
i'm confused... did you try to execute this SQL command from query analyzer?

ziolko.
0
 
LVL 26

Expert Comment

by:EddieShipman
ID: 18822568
NO. I'm not trying to run from QA, I'm trying to run from Delphi like you posted.
0
 
LVL 1

Author Comment

by:hidrau
ID: 18822592
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
 
LVL 1

Author Comment

by:hidrau
ID: 18822629
Did you test in your computer your code? did it work?
0
 
LVL 21

Expert Comment

by:ziolko
ID: 18822736
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
 
LVL 26

Expert Comment

by:EddieShipman
ID: 18823309
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
 
LVL 26

Expert Comment

by:EddieShipman
ID: 18823551
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
 
LVL 21

Expert Comment

by:ziolko
ID: 18823821
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
 
LVL 26

Expert Comment

by:EddieShipman
ID: 18823945
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
 
LVL 26

Accepted Solution

by:
EddieShipman earned 2000 total points
ID: 18824015
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

670 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