将Oracle导入到Access,
ADOConnection1.Execute('insert into gds_order select * from gds_order in [ODBC][ODBC;dsn=jbcmis;User Name=informix;password=informix;] ');
程序第一次运行的运行是弹出Oracle8 ODBC Driver Connnect的对话框,不知道有没有办法将其屏蔽掉?
ADOConnection1.Execute('insert into gds_order select * from gds_order in [ODBC][ODBC;dsn=jbcmis;User Name=informix;password=informix;] ');
程序第一次运行的运行是弹出Oracle8 ODBC Driver Connnect的对话框,不知道有没有办法将其屏蔽掉?
希望对你有点用处
procedure TForm1.FormCreate(Sender: TObject);
begin
ADOTable1.ConnectionString := 'Provider=MSDAORA.1;Password=yaowei;User ID=CPSJ;Data Source=YAOWEI;Persist Security Info=True';
ADOTable1.TableName :='产品';
ADOTable1.Open;
ADOTable2.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=产品数据.mdb;Persist Security Info=False';
ADOTable2.TableName :='产品';
ADOTable2.Open;
end;procedure TForm1.Button1Click(Sender: TObject);
begin
While not ADOTable1.Recordset.EOF do
begin
ADOTable2.Insert ;
ADOTable2.FieldByName('产品名称').AsString :=ADOTable1.FieldByName('产品名称').AsString;
ADOTable2.FieldByName('类别编号').AsString :=ADOTable1.FieldByName('类别编号').AsString;
ADOTable2.FieldByName('单位数量').AsString :=ADOTable1.FieldByName('单位数量').AsString;
ADOTable2.FieldByName('单价').AsFloat :=ADOTable1.FieldByName('单价').AsFloat;
ADOTable2.FieldByName('库存量').AsInteger :=ADOTable1.FieldByName('库存量').AsInteger;
ADOTable2.FieldByName('订购量').AsInteger :=ADOTable1.FieldByName('订购量').AsInteger;
ADOTable2.Post;
ADOTable1.Next ;
end;
Close;
end;end.
Install an Oracle ODBC driver
Create a datasource based on a machine with an Oracle database
Select Get External data from the File menu
Choose Import rather than Link
Choose ODBC as the file type and select the datasource
The biggest problems are (going from Oracle to Access): Names
names- these go over to upper case names in Access which may not look very friendly. One can rename them at this stage since names are case insensitive.
Fails to export
If your machine falls over because the data is to big to import, you could use the following script to create a comma delimitered file of the data in Oracle and then ftp it over
document
I hope you gave the tablename on the start command
otherwise you get an 'unknown command' error message
#
undefine quote
REM This procedure writes a comma delimited file
REM where tablename is the name of the table you want to output.
REM
accept delimiter char prompt 'Delimiter?'
accept nullvalue char prompt 'Null value?'
accept quote char prompt 'Type " for text to be surrounded by quotes '
set pause off
set echo off
set termout off
set newpage 1
set verify off
set feedback off
set pagesize 0
set linesize 75
set tab off
set scan
spool /tmp/stack.tmp
select decode (rownum,1,'select ','||''&delimiter''||')
|| decode (data_type,'NUMBER','decode(','''&"e''||')
|| column_name
||
decode(data_type,'NUMBER',',NULL,''&nullvalue'','||column_name
||')','||''&"e''')
from user_tab_columns
where table_name=upper('&1');
select 'templabel from &1' from dict where rownum=1;
prompt /
spool off
set linesize 132
set tab on
set trimout on
set termout on
set scan off
spool temp.txt
start /tmp/stack.tmp
spool OFF
set heading on
set linesize 80
set scan on
set pause on
set pagesize 23
set feedback on
host mv temp.dat "&1".txtdata types
text fields in Access (max length 255) but varchar2 (max length 2000) in Oracle so may want to use memo fields
may want to use booleans rather than text fields
You may want to set up automatic counters
ORACLE longraw datatype only holds 65Kb so the picture will come out very fuzzy, convert to OLE
Relationships and Constraints
set up the relationships, add data constraints and indexes
data content
case of text ignored in Access queries but significant in Oracle queries
Queries
Taking queries from Oracle to Access may or may not be straightforward depending on how standard the SQL is - again I have documented differences between Access SQL and Oracle SQL in Comparison of Oracle and Microsoft Access SQL.
Copy the SQL from Oracle into the clipboard
Create a new query in Access
View the design in SQL
paste the Oracle query into the Access query and see if it runs
if it does you can then view the QBE grid and see what it looks like