SQL commit statement for Access? 
Author Message
 SQL commit statement for Access?

I am pulling data from tables in Oracle and storing them in one
combined tabled in Access97 for quick refences while my application is
running. It processes about 30,000 Oracle records and condenses them
down to about 10,000 Access records.

To do this, I am constructing a SQL insert statement with all the
information, then when it's ready, I issue a "CurrentDB.Execute
strSQL".

What I would like to do, is every 100 records or so, issue a Commit
statement, but that's not a valid SQL statement in Access.

Since each record is a combination of fields from multiple Oracle
tables, I'm not sure if I could use the BeginTrans construct. That's
why I went with simple SQL "Insert" statements.

Any thoughts on how I should handle this, or if it's even something I
need to worry about?

TIA



Sat, 24 Jul 2004 21:56:42 GMT  
 SQL commit statement for Access?
If you're using dbSQLPassthrough, you can also execute the "Commit"
statement via Access. FWIW, it's probably more efficient to use an ORACLE
view, unless ODBC is too slow for the client app.

--
Calvin Smith
http://www.CalvinSmithSoftware.com
"Real-world Source Code Solutions"
*** http://www.CalvinSmithSoftware.com/AllADO.htm - New! ***


Quote:
> I am pulling data from tables in Oracle and storing them in one
> combined tabled in Access97 for quick refences while my application is
> running. It processes about 30,000 Oracle records and condenses them
> down to about 10,000 Access records.

> To do this, I am constructing a SQL insert statement with all the
> information, then when it's ready, I issue a "CurrentDB.Execute
> strSQL".

> What I would like to do, is every 100 records or so, issue a Commit
> statement, but that's not a valid SQL statement in Access.

> Since each record is a combination of fields from multiple Oracle
> tables, I'm not sure if I could use the BeginTrans construct. That's
> why I went with simple SQL "Insert" statements.

> Any thoughts on how I should handle this, or if it's even something I
> need to worry about?

> TIA



Sun, 25 Jul 2004 16:05:29 GMT  
 SQL commit statement for Access?
If you're using dbSQLPassThrough, then you can also execute a "Commit" to
ORACLE via Access. FWIW, I would think that an ORACLE View would be more
efficient unless ODBC is too slow on the client.

--
Calvin Smith
http://www.CalvinSmithSoftware.com
"Real-world Source Code Solutions"
*** http://www.CalvinSmithSoftware.com/AllADO.htm - New! ***


Quote:
> I am pulling data from tables in Oracle and storing them in one
> combined tabled in Access97 for quick refences while my application is
> running. It processes about 30,000 Oracle records and condenses them
> down to about 10,000 Access records.

> To do this, I am constructing a SQL insert statement with all the
> information, then when it's ready, I issue a "CurrentDB.Execute
> strSQL".

> What I would like to do, is every 100 records or so, issue a Commit
> statement, but that's not a valid SQL statement in Access.

> Since each record is a combination of fields from multiple Oracle
> tables, I'm not sure if I could use the BeginTrans construct. That's
> why I went with simple SQL "Insert" statements.

> Any thoughts on how I should handle this, or if it's even something I
> need to worry about?

> TIA



Sun, 25 Jul 2004 09:28:37 GMT  
 SQL commit statement for Access?

Whoops! Sorry for the double post guys. My first reply had problems and
didn't show on my side until I posted again.

--
Calvin Smith
http://www.CalvinSmithSoftware.com
"Real-world Source Code Solutions"
*** http://www.CalvinSmithSoftware.com/AllADO.htm - New! ***


Quote:
> If you're using dbSQLPassthrough, you can also execute the "Commit"
> statement via Access. FWIW, it's probably more efficient to use an ORACLE
> view, unless ODBC is too slow for the client app.

> --
> Calvin Smith
> http://www.CalvinSmithSoftware.com
> "Real-world Source Code Solutions"
> *** http://www.CalvinSmithSoftware.com/AllADO.htm - New! ***



> > I am pulling data from tables in Oracle and storing them in one
> > combined tabled in Access97 for quick refences while my application is
> > running. It processes about 30,000 Oracle records and condenses them
> > down to about 10,000 Access records.

> > To do this, I am constructing a SQL insert statement with all the
> > information, then when it's ready, I issue a "CurrentDB.Execute
> > strSQL".

> > What I would like to do, is every 100 records or so, issue a Commit
> > statement, but that's not a valid SQL statement in Access.

> > Since each record is a combination of fields from multiple Oracle
> > tables, I'm not sure if I could use the BeginTrans construct. That's
> > why I went with simple SQL "Insert" statements.

> > Any thoughts on how I should handle this, or if it's even something I
> > need to worry about?

> > TIA



Sun, 25 Jul 2004 16:12:48 GMT  
 SQL commit statement for Access?
The table I am storing these records in, is an Access table. I am
taking multiple records from multiple Oracle tables and combining them
into one Access table.

So I am doing an INSERT on an Access table directly. I may not even
need to do a Commit, and since I can't figure out how, I'm not. I just
thought it would be better to do one, if I could.

Quote:

> If you're using dbSQLPassthrough, you can also execute the "Commit"
> statement via Access. FWIW, it's probably more efficient to use an ORACLE
> view, unless ODBC is too slow for the client app.

> --
> Calvin Smith
> http://www.CalvinSmithSoftware.com
> "Real-world Source Code Solutions"
> *** http://www.CalvinSmithSoftware.com/AllADO.htm - New! ***



> > I am pulling data from tables in Oracle and storing them in one
> > combined tabled in Access97 for quick refences while my application is
> > running. It processes about 30,000 Oracle records and condenses them
> > down to about 10,000 Access records.

> > To do this, I am constructing a SQL insert statement with all the
> > information, then when it's ready, I issue a "CurrentDB.Execute
> > strSQL".

> > What I would like to do, is every 100 records or so, issue a Commit
> > statement, but that's not a valid SQL statement in Access.

> > Since each record is a combination of fields from multiple Oracle
> > tables, I'm not sure if I could use the BeginTrans construct. That's
> > why I went with simple SQL "Insert" statements.

> > Any thoughts on how I should handle this, or if it's even something I
> > need to worry about?

> > TIA



Sun, 25 Jul 2004 21:41:16 GMT  
 SQL commit statement for Access?
Yes, I understood what your target table is. Still recommend using an ORACLE
view instead, that you could link to from Access.

FYI, to execute a Commit you would simply do the following:

dbYourORACLEPointer.Execute "Insert blah blah blah", dbSQLPassThrough
dbYourORACLEPointer.Execute "Commit", dbSQLPassThrough

--
Calvin Smith
http://www.CalvinSmithSoftware.com
"Real-world Source Code Solutions"
*** http://www.CalvinSmithSoftware.com/AllADO.htm - New! ***


Quote:
> The table I am storing these records in, is an Access table. I am
> taking multiple records from multiple Oracle tables and combining them
> into one Access table.

> So I am doing an INSERT on an Access table directly. I may not even
> need to do a Commit, and since I can't figure out how, I'm not. I just
> thought it would be better to do one, if I could.




Quote:
> > If you're using dbSQLPassthrough, you can also execute the "Commit"
> > statement via Access. FWIW, it's probably more efficient to use an
ORACLE
> > view, unless ODBC is too slow for the client app.

> > --
> > Calvin Smith
> > http://www.CalvinSmithSoftware.com
> > "Real-world Source Code Solutions"
> > *** http://www.CalvinSmithSoftware.com/AllADO.htm - New! ***



> > > I am pulling data from tables in Oracle and storing them in one
> > > combined tabled in Access97 for quick refences while my application is
> > > running. It processes about 30,000 Oracle records and condenses them
> > > down to about 10,000 Access records.

> > > To do this, I am constructing a SQL insert statement with all the
> > > information, then when it's ready, I issue a "CurrentDB.Execute
> > > strSQL".

> > > What I would like to do, is every 100 records or so, issue a Commit
> > > statement, but that's not a valid SQL statement in Access.

> > > Since each record is a combination of fields from multiple Oracle
> > > tables, I'm not sure if I could use the BeginTrans construct. That's
> > > why I went with simple SQL "Insert" statements.

> > > Any thoughts on how I should handle this, or if it's even something I
> > > need to worry about?

> > > TIA



Mon, 26 Jul 2004 05:26:20 GMT  
 SQL commit statement for Access?
Larry,
Correct me if I am wrong (I am only an interested observer) but I think
Calvin is processing in the wrong direction.

Yes! I do think this may be an issue expecially if this may be executed over
Netware. We have found that large transactions have a tendancy to blow up,
even with a high setting on the locks. You need to process lots on small
chunks. I have taken the MUCH slower route of manual processing (in code)
with COMMITs actioned frequently.

I will watch this conversation with great interest for the grand poobahs to
cast their pearls of wisdom
Terry


Quote:
> I am pulling data from tables in Oracle and storing them in one
> combined tabled in Access97 for quick refences while my application is
> running. It processes about 30,000 Oracle records and condenses them
> down to about 10,000 Access records.

> To do this, I am constructing a SQL insert statement with all the
> information, then when it's ready, I issue a "CurrentDB.Execute
> strSQL".

> What I would like to do, is every 100 records or so, issue a Commit
> statement, but that's not a valid SQL statement in Access.

> Since each record is a combination of fields from multiple Oracle
> tables, I'm not sure if I could use the BeginTrans construct. That's
> why I went with simple SQL "Insert" statements.

> Any thoughts on how I should handle this, or if it's even something I
> need to worry about?

> TIA



Mon, 26 Jul 2004 22:12:04 GMT  
 SQL commit statement for Access?

Quote:
>>> Calvin is processing in the wrong direction <<<

That depends on what his CurrentDB object is set to. If it's an ODBC
database object connection, then "Commit" could still be needed. Regardless,
a "View" is the ideal way to go, if ODBC performance is not an issue on the
client. Otherwise, the Access database will just bloat across the disk space
and need compacting periodically (yet another processing piece to the
flowchart).

Quote:
>>> the grand poobahs <<<

Hehehe! (Yabba dabba doo) :-)

--
Calvin Smith
http://www.CalvinSmithSoftware.com
"Real-world Source Code Solutions"
*** http://www.CalvinSmithSoftware.com/AllADO.htm - New! ***


Quote:
> Larry,
> Correct me if I am wrong (I am only an interested observer) but I think
> Calvin is processing in the wrong direction.

> Yes! I do think this may be an issue expecially if this may be executed
over
> Netware. We have found that large transactions have a tendancy to blow up,
> even with a high setting on the locks. You need to process lots on small
> chunks. I have taken the MUCH slower route of manual processing (in code)
> with COMMITs actioned frequently.

> I will watch this conversation with great interest for the grand poobahs
to
> cast their pearls of wisdom
> Terry



> > I am pulling data from tables in Oracle and storing them in one
> > combined tabled in Access97 for quick refences while my application is
> > running. It processes about 30,000 Oracle records and condenses them
> > down to about 10,000 Access records.

> > To do this, I am constructing a SQL insert statement with all the
> > information, then when it's ready, I issue a "CurrentDB.Execute
> > strSQL".

> > What I would like to do, is every 100 records or so, issue a Commit
> > statement, but that's not a valid SQL statement in Access.

> > Since each record is a combination of fields from multiple Oracle
> > tables, I'm not sure if I could use the BeginTrans construct. That's
> > why I went with simple SQL "Insert" statements.

> > Any thoughts on how I should handle this, or if it's even something I
> > need to worry about?

> > TIA



Tue, 27 Jul 2004 10:46:32 GMT  
 SQL commit statement for Access?
Sorry for the delay in replying, but I just got back into my home
office, after working in Scotland the last couple of weeks. Ok, now I
am so totally confused!  :)

I cannot see how I would create an Oracle view to an Access table,
that I would then link to from Access! That seems impossible, for one
thing, and going in circles for another.

All I am doing is inserting records into an Access table stored in a
back-end Access database. So how would Oracle see this Access table to
be able to create a view? It's bad enough getting to Oracle from
Access though ODBC, but to try to then do it in reverse???

I am linking to the Oracle tables from Access using ODBC (I don't know
any other way) and pulling down (what is now) more than 32,000 records
(5 columns) to store them in an Access table. What I am finding is
that while it works (it takes about an hour to process) the front-end
database gets huges in size (from 1Mb to 14Mb, while the back-end
database only grows to the "required" size to hold the data. Doing a
compact on each, reduces the front-end back to 1Mb while the back-end
is not reduced by much at all.

Once this is in "production" there will be no network traffic, as both
the Oracle database and the Access database will reside on the same
machine.

Any other comments and/or clarifications would be greatly appreciated.

Larry



Sat, 31 Jul 2004 21:22:12 GMT  
 SQL commit statement for Access?
Hi Larry!

Hope your trip to Scotland was pleasant and did not influence you to start
wearing those skirts that I've seen men wearing over there. Only womens'
legs look good at the bottom of skirts [as quiet as it's kept]. :-)

First, let's make sure we're on the same page because it's obvious to me
that somehow my suggestion is being received incorrectly by you and someone
else who replied too.

When I suggested using an ORACLE View, what I meant was, instead of
downloading (or "pulling down" in your words) *any* records into Access,
don't! Use a View instead. If you do that, then the process of calling
"Commit" in ORACLE becomes a non-issue. Compacting would also become a
non-issue, in regards to your efforts with the ORACLE records that you keep
"pulling down."

Hope that clears things up. :-)

--
Calvin Smith
http://www.CalvinSmithSoftware.com
"Real-world Source Code Solutions"
*** http://www.CalvinSmithSoftware.com/AllADO.htm - New! ***


Quote:
> Sorry for the delay in replying, but I just got back into my home
> office, after working in Scotland the last couple of weeks. Ok, now I
> am so totally confused!  :)

> I cannot see how I would create an Oracle view to an Access table,
> that I would then link to from Access! That seems impossible, for one
> thing, and going in circles for another.

> All I am doing is inserting records into an Access table stored in a
> back-end Access database. So how would Oracle see this Access table to
> be able to create a view? It's bad enough getting to Oracle from
> Access though ODBC, but to try to then do it in reverse???

> I am linking to the Oracle tables from Access using ODBC (I don't know
> any other way) and pulling down (what is now) more than 32,000 records
> (5 columns) to store them in an Access table. What I am finding is
> that while it works (it takes about an hour to process) the front-end
> database gets huges in size (from 1Mb to 14Mb, while the back-end
> database only grows to the "required" size to hold the data. Doing a
> compact on each, reduces the front-end back to 1Mb while the back-end
> is not reduced by much at all.

> Once this is in "production" there will be no network traffic, as both
> the Oracle database and the Access database will reside on the same
> machine.

> Any other comments and/or clarifications would be greatly appreciated.

> Larry



Tue, 03 Aug 2004 20:05:16 GMT  
 SQL commit statement for Access?
No need to worry about me wearing a kilt! :)

As far as clearing things up, that did it. I understand what you are
saying now, but using an Oracle view to get the data is not an option.

I am pulling the data from a vendor's highly normalized object
oriented version of an Oracle database. It is not possible to create a
view that would do the job.

When the user is moving through the records in the Access database, I
need to be able to display this related information quickly. To try to
go to Oracle and get the information each time, would take too long. I
also need to be able to show a complete list of the data being
retrieved, all 37,000+ rows. Again, that is not possible with this
database.

So, I am executing this code, which will be executed periodically, to
store the data in a local Access table for use from within Access.

Thanks for the suggestion though.

Quote:

> Hi Larry!

> Hope your trip to Scotland was pleasant and did not influence you to start
> wearing those skirts that I've seen men wearing over there. Only womens'
> legs look good at the bottom of skirts [as quiet as it's kept]. :-)

> First, let's make sure we're on the same page because it's obvious to me
> that somehow my suggestion is being received incorrectly by you and someone
> else who replied too.

> When I suggested using an ORACLE View, what I meant was, instead of
> downloading (or "pulling down" in your words) *any* records into Access,
> don't! Use a View instead. If you do that, then the process of calling
> "Commit" in ORACLE becomes a non-issue. Compacting would also become a
> non-issue, in regards to your efforts with the ORACLE records that you keep
> "pulling down."

> Hope that clears things up. :-)

> --
> Calvin Smith
> http://www.CalvinSmithSoftware.com
> "Real-world Source Code Solutions"
> *** http://www.CalvinSmithSoftware.com/AllADO.htm - New! ***



> > Sorry for the delay in replying, but I just got back into my home
> > office, after working in Scotland the last couple of weeks. Ok, now I
> > am so totally confused!  :)

> > I cannot see how I would create an Oracle view to an Access table,
> > that I would then link to from Access! That seems impossible, for one
> > thing, and going in circles for another.

> > All I am doing is inserting records into an Access table stored in a
> > back-end Access database. So how would Oracle see this Access table to
> > be able to create a view? It's bad enough getting to Oracle from
> > Access though ODBC, but to try to then do it in reverse???

> > I am linking to the Oracle tables from Access using ODBC (I don't know
> > any other way) and pulling down (what is now) more than 32,000 records
> > (5 columns) to store them in an Access table. What I am finding is
> > that while it works (it takes about an hour to process) the front-end
> > database gets huges in size (from 1Mb to 14Mb, while the back-end
> > database only grows to the "required" size to hold the data. Doing a
> > compact on each, reduces the front-end back to 1Mb while the back-end
> > is not reduced by much at all.

> > Once this is in "production" there will be no network traffic, as both
> > the Oracle database and the Access database will reside on the same
> > machine.

> > Any other comments and/or clarifications would be greatly appreciated.

> > Larry



Sat, 07 Aug 2004 02:32:19 GMT  
 
 [ 11 post ] 

 Relevant Pages 

1. Oracle commits vs Sybase/SQL-Server commits

2. Access: Err 2342 in SQL SELECT Statement in VBA

3. Get SQL Statement from Access queries

4. Pairwise Sql statement not working in Access ?

5. SQL Statement edit access db with DAO

6. Access generating incorrect SQL statement when filling subform

7. Access: Err 2342 in SQL SELECT Statement in VBA

8. VBA in Access ... sql statement with querydef

9. SQL Statement - MS Access via IIS with .asp?

10. sql statement in vb using access database

11. Begintrans-Commit with SQL-Server7??

12. VB%, Access and SQL statements

 

 
Powered by phpBB® Forum Software