View Single Post
  #1 (permalink)  
Old January 30th, 2012, 06:19 AM
S300570 S300570 is offline
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