Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: Access 2007 VBA Programmer's Reference ISBN: 978-0-470-04703-3
This is the forum to discuss the Wrox book Access 2007 VBA Programmer's Reference by Teresa Hennig, Rob Cooper, Geoffrey L. Griffith, Armen Stein; ISBN: 9780470047033
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Access 2007 VBA Programmer's Reference ISBN: 978-0-470-04703-3 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old January 30th, 2012, 07:19 AM
Registered User
Points: 17, Level: 1
Points: 17, Level: 1 Points: 17, Level: 1 Points: 17, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2008
Location: Lisboa, , Portugal.
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Understanding Client-Server Development with VBA

Hi guys,

Let me thank you before anything, and ask you to be patient with my English.

I’vepurchasedthe bookAccess 2007 VBA Programmer's Reference and I’m having a problem regarding the chapter
Understanding Client-Server Development with VBA.

I need to know witch is the best way to get data from ODBC “Data Warehouse”
using Access 2003. My problem is tha I’m using a SQL Query to bring the Data inside a DAO.Recordset, but extracting those records from it, into na access local table, it’s taking to long. We’re talking about 300.000 records.

I need to know please, if there’s a way to bring those records “via ODBC”, using SQL ORACLE, directly INTO a Local access table. I Can’t just do a SELECT * from A Into B, since A is in ODBC environment and B is in a Local Table in my Access.mdb.

I’m using the code below. Please help.

Private sub Get_Data()
Dim sql as string
Dim wrkODBC As DAO.Workspace
Dim conPubs As DAO.Connection
Dim rt As DAO.Recordset
Dim strConnect As String

‘Connection String
strConnect = "ODBC;DSN=dw_producao;UID=s300570;PWD=*****;CONNEC TION TIMEOUT=300;"

sql = "select a.* " & _
"from DW.DWT999_CASO_BKS a , " & _
"(select codcaso, MAX (empresa) empresa from DW.DWT999_CASO_BKS " & _
"where processo = '0027' and estado in ('05','06') group by codcaso) b " & _
"where a.PROCESSO ='0027' " & _
"and a.codcaso = b.codcaso (+) " & _
"and b.empresa is null " & _
"order by a.codcaso, a.ult_alteracao;"


Set wrkODBC = CreateWorkspace("", "admin", "", dbUseODBC)
Set conPubs = wrkODBC.OpenConnection("Connection1", , , strConnect) '


conPubs.QueryTimeout = 180 '3 minutos

‘Extraction takes about 30 seconds.
Set rt = conPubs.OpenRecordset(sql)



DoEvents

OpenRecordsetOutput rt

End Sub

Private Sub OpenRecordsetOutput(rstOutput As DAO.Recordset)
‘This process for dumping the Recordset is too slow…

'Export Data into Recordset:
Dim i As Long
Dim j As Long
Dim iRows As Long ‘Nº Rows from recordset
Dim jCols As Long ‘Nº Cols from recordset
Dim sql as String

‘those 2 lines of code (below) doesn’t work
rstOutput.MoveLast: rstOutput.MoveFirst
iRows = rstOutput.RecordCount
jCols = rstOutput.Fields.Count


sql = ""
With rstOutput
While Not .EOF
sql = "INSERT INTO Table VALUES ("

For j = 0 To jCols - 1 'fields
‘Close Bracket on last field
If j = (jCols - 1) Then
sql = sql & ", '" & Trim$(Replace(rstOutput(j), ",", ".")) & "')"
Else
sql = sql & ", '" & Trim$(Replace(rstOutput(j), ",", ".")) & "'"
End If
Next j

‘Eliminate initial comma after first Bracket
sql = Replace(sql, "(, ", "(")

'Debug.Print sql
DoCmd.SetWarnings False
DoCmd.RunSQL sql, dbFailOnError
DoCmd.SetWarnings True
.MoveNext

Wend

End With

End Sub


Thank you for your kindness.

Leopoldo Fernandes
Lisbon - Portugal
Reply With Quote
  #2 (permalink)  
Old January 30th, 2012, 04:58 PM
gjgriffith's Avatar
Wrox Author
Points: 517, Level: 8
Points: 517, Level: 8 Points: 517, Level: 8 Points: 517, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2009
Location: Boulder, CO
Posts: 110
Thanks: 5
Thanked 14 Times in 14 Posts
Default Why not create a Linked Table in the Access database to Oracle?

Hello Leopoldo,

Thank you so much for posting your question about using Oracle data in the Microsoft Access database application. Also, thank you for reading the Microsoft Access 2007 VBA Programmer's Reference book, we appreciate your readership! To try to answer your question, you asked:

Quote:
I need to know...if there’s a way to bring those records “via ODBC”...directly INTO a Local access table. I Can’t just do a SELECT * from A Into B, since A is in ODBC environment and B is in a Local Table in my Access.mdb.
Ok, so I believe you have at least 2 options to do this:

1. You can create a "Linked Table" to the Oracle tables. Once you have the ODBC drivers for Oracle installed on your computer (http://www.oracle.com/technetwork/de...ads/index.html). You should be able to create linked tables to Oracle (using the "ODBC" button from the "External Data" Ribbon) and then call that linked table from VBA using DAO. Although the data won't technically be local, you should still be able to Access it with DAO.

2. If you really do need the data local, and not just in a linked table, you can create the linked table tot he oracle database, create another table with the same structure as the Oracle linked table, and then create an Append query to append the data from the Oracle linked table to the local Access table.

Anyway, hopefully these answers make sense to you, but please let me know if you have any questions about this. And thank you again for reading the Access 2007 VBA Programmer's Reference, we truly appreciate it!

Thanks,
__________________
Geoffrey L. Griffith
http://www.ImagineThought.com

Wrox Author of:
Microsoft Access 2010 24-Hour Trainer
Access 2010 Programmer's Reference
Access 2007 VBA Programmer's Reference

*** Please click the THANKS button (to the right) if this post helped you! *** ---------------------------------------------------------------------------------------------------------->
Reply With Quote
  #3 (permalink)  
Old January 31st, 2012, 06:24 AM
Registered User
Points: 17, Level: 1
Points: 17, Level: 1 Points: 17, Level: 1 Points: 17, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2008
Location: Lisboa, , Portugal.
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hello gjgriffith,

Let me thank you for your precious time and knowledge. Yes, The Book has been a great help for me.

What I really need is something like the solution you mentioned at the point 2.

My VBA code already creates a Temp Access Database with a "table with the same structure as the Oracle linked table".The really issue is in the 2 procedures I've posted below:

Establish the connection is easy, but creating the Append Query to Run on the Client it's to Difficult. Access gives me an error regarding an extra Bracket ")", due to the LEFT JOIN Implied, as you can see below, at the “Oracle SQL (+)” . For that reason I Have to make it run on the server side.


select a.*
from DW.DWT999_CASO_BKS a ,
(select codcaso, MAX (empresa) empresa from DW.DWT999_CASO_BKS
where processo = '0027' and estado in ('05','06') group by codcaso) b
where a.PROCESSO ='0027'
and a.codcaso = b.codcaso (+)
and b.empresa is null
order by a.codcaso, a.ult_alteracao;


As you mentioned too, It’s possible to run SQL Queries directly from access using the String Connection on the [IN] Clause, Like the example showed below, to bring Data From Oracle Database:

INSERT INTO NewTABLE
select a.ID, a.Costumer, a.Product, b.Price
from COSTUMERS a,
PRODUCTS b
IN '' [ODBC;DSN=dw_producao;UID=s300570;PWD=****;ONNECTIO N TIMEOUT=300;] where a.Product = b.Product


But that kind of approach doesn’t permit the using of subQueries like the example below, to bring the most recent product achieved by one Costumer:

select a.ID, a.Costumer, a.Product, a.Purchase_Date
from COSTUMERS a,
(select Costumer, Max(Purchase_Date) as P_date
from COSTUMERS
Group by Costumer) b
IN '' [ODBC;DSN=dw_producao;UID=s300570;PWD=****;CONNECTI ON TIMEOUT=300;] where a. Costumer = b. Costumer AND
a.Purchase_Date = b. P_date


Can you please show me, using VBA approach, The best way to Get Data from a Oracle “DataWareHouse”, establishing a ODBC Connection In order to run My Append Query? As I mentioned before, the Local Table with the same structure is created via VBA to.

P.s. I already have the Book “Expert Access 2007 Programming”, and didn’t find help there on this matter.


My Best regards to you, and Many Thank’s once more.

Leopoldo Fernandes
Lisbon Portugal
Reply With Quote
  #4 (permalink)  
Old January 31st, 2012, 08:44 AM
gjgriffith's Avatar
Wrox Author
Points: 517, Level: 8
Points: 517, Level: 8 Points: 517, Level: 8 Points: 517, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2009
Location: Boulder, CO
Posts: 110
Thanks: 5
Thanked 14 Times in 14 Posts
Default Your database solution

Hello Leopoldo,

Thank you for the follow up post and your kind words about the Access 2007 VBA Programmer's Reference book, I appreciate your efforts! Ok, so it sounds like the problems that you are having are really problems that are specific to your database application and are not really questions about the Access 2007 VBA Programmer's Reference book...but I will still try to help!

Ok, so I do NOT believe that the "(+)" syntax is required or supported here, and is probably your real problem (I don't think that the Access query engine supports this kind of syntax in a query...I believe this is an Oracle specific piece of SQL Code). But, what you can do is if you already have a local table in an Access database (with the same structure as the Oracle table), and you also have a linked table to the Oracle table in that database, then the Append query should be very simple, something like:

Code:
 
INSERT INTO tblAccessTable ( AccField1, AccField2, AccField3 )
SELECT tblOracleTable.Field1, tblOracleTable.Field2, tblOracleTable.Field3
FROM tblOracleTable;
All you have to do is SELECT all of the fields you want from one table and INSERT them into the other table. But REMEMBER, if the Oracle table has lookups to other tables (and your table DOES from looking at your code above), you'll need to append the data to the lookup tables FIRST, before appending the main Oracle table! Does this make sense? I understand that there may be some language difficulties, but hopefully you can understand my comments.

Anyway, hopefully this helps, but if there is anything else I can do, please just let me know!

Thanks,
__________________
Geoffrey L. Griffith
http://www.ImagineThought.com

Wrox Author of:
Microsoft Access 2010 24-Hour Trainer
Access 2010 Programmer's Reference
Access 2007 VBA Programmer's Reference

*** Please click the THANKS button (to the right) if this post helped you! *** ---------------------------------------------------------------------------------------------------------->
Reply With Quote
  #5 (permalink)  
Old January 31st, 2012, 11:15 AM
Registered User
Points: 17, Level: 1
Points: 17, Level: 1 Points: 17, Level: 1 Points: 17, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2008
Location: Lisboa, , Portugal.
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Many thank's once more.

I don't need to update data in the Oracle Database, just bring the data to local access database to build ststistics.

I think I'll follow your sugestion, and remake my SQL avoiding the "(+)", since my main problem is Dumping the Recordset with the 300.000 records selected from ORACLE Database.
Altering the SQL in order to run the query localy using the direct "INSERT INTO", probably is faster than Dumping all records from the Recordset.


Thank you for All your time and help Gjgriffith

Leopoldo Fernandes
Reply With Quote
  #6 (permalink)  
Old January 31st, 2012, 11:54 AM
gjgriffith's Avatar
Wrox Author
Points: 517, Level: 8
Points: 517, Level: 8 Points: 517, Level: 8 Points: 517, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2009
Location: Boulder, CO
Posts: 110
Thanks: 5
Thanked 14 Times in 14 Posts
Smile No problem!

Hello Leopoldo,

No problem, I am glad to try to help! Hopefully you can get the SQL code worked out, but if you are still having problems, please feel free to contact me at my website (below) and maybe I can try to take a look at the specific files if necessary!

Thanks again,
__________________
Geoffrey L. Griffith
http://www.ImagineThought.com

Wrox Author of:
Microsoft Access 2010 24-Hour Trainer
Access 2010 Programmer's Reference
Access 2007 VBA Programmer's Reference

*** Please click the THANKS button (to the right) if this post helped you! *** ---------------------------------------------------------------------------------------------------------->
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Test-Driven Development (TDD) within Access VBA robzyc Access VBA 1 July 24th, 2007 07:24 AM
JSP on WDSC (WebSphere Development Studio Client) mshaik JSP Basics 0 January 31st, 2006 07:56 PM
DLLs on server pulling server time or client? mussitsch Classic ASP Professional 1 March 1st, 2005 05:38 AM
Smart client in .Net Enterprise Development in C# bookworm_zju Wrox Book Feedback 0 February 17th, 2004 06:12 AM
VBA winsock POP email client hooi Access VBA 0 December 23rd, 2003 04:03 AM



All times are GMT -4. The time now is 10:58 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.