How to get around 2 Gig limit

[jer99 via AccessMonster.com]

I have code that is using linked tables from Oracle via ODBC.
These tables are quite large but since they are linked, it has been no big
deal.

However, in one module, I create a table on the fly, fill it and go through
updating it based on certain criteria. Then I export it to a delimited file.
The table fields created are not constant so I can not create a permanent
table. This table is created on the Access side.

I've been hitting up against the 2 gig limit and obviously it's ugly.
I can't place it in another database and link because it will eventually get
to 2 gigs.
I cant create a linked fixed table on the oracle side since the fields vary.
I was trying to think of a way to potentially count the records before hand
and some how break it up, but that thought seems too intense.

Anyone have any ideas?

 

[Granny Spitz via AccessMonster.com]

However, in one module, I create a table on the fly, fill it and go through
updating it based on certain criteria. Then I export it to a delimited file.
The table fields created are not constant so I can not create a permanent
table. This table is created on the Access side.

The preferred method is to use a stored procedure in Oracle and execute it.
This requires PL/SQL and Oracle SQL skills. If you don't have these skills
and you're creating a large temp table *in Access,* what's stopping you from
creating that temp table in Oracle and creating a link to it programmatically?
The SQL syntax for table creation isn't that different from Jet's. Doesn't
the DBA give you permissions to create tables in your schema or the temp
schema for your application?

 

[jer99 via AccessMonster.com]

We (I) am our own DBA (now that's dangerous).
I have fairly good SQL skills and can do a mean PL/SQL at times, but I don't
quite understand what you mean by placing it in a stored procedure. All my
manipulations are being done using forms in access with VBA code behind the
scenes. For example, we have a local Oracle environment and a server Oracle
environment and I have some VBA code that switches the links.

I would love to get this over on the oracle side but dont understand how to
leverage the forms, VBA and stored procedures.

My gut is telling me that I can't have a stored procedure be as dynamic as my
VBA code with the creation of tables and views (assuming that a "view" =
Access' "query")

 

[Granny Spitz via AccessMonster.com]

We (I) am our own DBA (now that's dangerous).

It may be dangerous but it's really good news because it means you can do
whatever you need to do without begging or bribing with a whole box of Krispy
Kremes.

I would love to get this over on the oracle side but dont understand how to
leverage the forms, VBA and stored procedures.

The VBA or query that is creating the new table now can be replaced with
either:

1) ADO to connect to the Oracle database to execute the equivalent SQL to
create the temp table in the Oracle schema and fill it with data, or
2) ADO to execute a passthrough query that runs the PL/SQL stored procedure
that creates the temp table and fills it with data in the Oracle schema.

(Ok you can use DAO if you want to, but ADO would be more appropriate.) Once
the table is created and filled with the 2 GB+ of data you can create a link
programmatically to the view/table and play with it from Access or *stay* in
Oracle and do whatever needs to be done with that big table with either
Oracle SQL or PL/SQL with passthrough queries. Afterwards you can set a
form's recordsource to the linked Oracle view/table so you can see the
records.

My gut is telling me that I can't have a stored procedure be as dynamic as my
VBA code with the creation of tables and views (assuming that a "view" =
Access' "query")

Yes, a view is equivalent to an Access query. And Oracle *can* be just as
dynamic as VBA code but it depends on what you're doing. Creating tables and
views should be just about as easy in Oracle if you're using SQL in Jet now.
I'll leave you to think about how you can use your PL/SQL and Oracle SQL
skills to get the data into a big temp table in the Oracle schema from the
other big tables because I don't deal much with Oracle any more myself. I
mainly work with SQL Server and MySQL so I don't *think Oracle* much any more.

 

[jer99 via AccessMonster.com]

Thanks for the ideas. It opens up a whole new world to me.
I can't let you dissappear so easily however.

I use ADO exclusively now so that isn't an issue at all. I do, however, like
to create things that are very dynamic (and modifiable - not to be redundant
again).
The item you mentioned that intrigued me is:
-----------------
2) ADO to execute a passthrough query that runs the PL/SQL stored procedure
that creates the temp table and fills it with data in the Oracle schema.
----------------
if the temp table fields vary, the code can not be static. I use TOAD as a
front end (thank heaven for TOAD) so I'm removed a bit from the actual real
world. Are you suggesting that I can have VBA somehow create a stored
procedure on the fly and execute it ?

I'm used to doing ODBC connections and can always do exports of a blank table
and then fill it, but if I can create the query in VBA and run it in Oracle,
that is a good thing. It would open a lot of doors if I had the ability to
manuipulate Oracle stored procedures from VBA somehow. ...