RE: Populate Access table with Oracle data - how?
From: mautobot (mautobot_at_discussions.microsoft.com)
Date: 11/24/04
- Next message: Alex Ivanov: "Re: query mdb on network into Word. Access.exe not on workstation"
- Previous message: SK: "Stored Procedure Syntax"
- In reply to: dj: "Populate Access table with Oracle data - how?"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 23 Nov 2004 19:49:02 -0800
I did a project just like this not too long ago. We use it as an export
utility for front end users to play with.
Can you ensure the client machine will have Access?
If so, you can hook into the Access.Application COM Object and tell it to
create a link table for you. Once you have the link table there you can
execute the SQL statement inside Access vs. Oracle. Access does a great job
at dynamically building the table based on the link table.
I ended up creating an XML parameter file that defined the export. So,
anyone in my company can create their own export file and use my utility to
export data from any ODBC source.
if you want some sample code let me know and I will get it to you tomorrow.
Regards,
Matt
"dj" wrote:
> It seems simple enough. I need to get one or more rows, based on a WHERE
> clause (parameter), from a single linked Oracle table via ADO and put the
> results in a local Access 2000 table for later use. This takes place in the
> same Access 2000 db.
>
> Getting the data is not problem, but it seems like I should be able to do
> the whole thing in a single INSERT statement: INSERT INTO [Access table
> columns] SELECT [Oracle columns] WHERE [Oracle column] = [parameter value].
>
> But I get an error (ORA-00942) from Oracle because Oracle doesn't recognize
> the local Access table. I could open an ADO recordset and loop through
> that, putting the rows in the local table, but that seems inefficient.
>
> Anyone have a better way?
>
> TIA,
> dj
>
- Next message: Alex Ivanov: "Re: query mdb on network into Word. Access.exe not on workstation"
- Previous message: SK: "Stored Procedure Syntax"
- In reply to: dj: "Populate Access table with Oracle data - how?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
