Understanding Client-Server Development with VBA
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
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)
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
iRows = rstOutput.RecordCount
jCols = rstOutput.Fields.Count
sql = ""
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), ",", ".")) & "')"
sql = sql & ", '" & Trim$(Replace(rstOutput(j), ",", ".")) & "'"
‘Eliminate initial comma after first Bracket
sql = Replace(sql, "(, ", "(")
DoCmd.RunSQL sql, dbFailOnError
Thank you for your kindness.
Lisbon - Portugal