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

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:

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! *** ---------------------------------------------------------------------------------------------------------->

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

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:

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! *** ---------------------------------------------------------------------------------------------------------->

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

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! *** ---------------------------------------------------------------------------------------------------------->