Guardar datos con ADO desde diferente bases de datos

programador666
25 de Julio del 2005
Espero que el que pueda me ayude con esto.

Tengo un ADODB.Connection que se pega a Oracle y necesito guardar estos datos en BD de Access.

¿Hay algun tipo de query o procedimiento para pasar el recordset generado (Oracle) a Access sin necesidad de tener que hacer un bucle como lo estoy haciendo. A continuacion pego el codigo:

Sub Calcular_Datos()
Dim Facs As String, Facilidades As New ADODB.Recordset, strAreas As String, Areas As New ADODB.Recordset
Dim Inventario As New ADODB.Recordset, Sliic_Conexion As New ADODB.Connection

'Aqui concateno todas las facilidades que voy a filtrar en query de SLICC
With Facilidades
.Open ("select facilidad from facilidades"), Conexion, adOpenForwardOnly, adLockReadOnly
If Not .BOF And Not .EOF Then
While Not .EOF
Facs = Facs & "'" & Trim(!Facilidad) & "',"
.MoveNext
Wend
Facs = Left(Facs, Len(Facs) - 1)
End If
.Close
End With

'Aqui concateno todas las areas que voy a filtrar en query de SLICC
With Areas
.Open ("select area from areas"), Conexion, adOpenForwardOnly, adLockReadOnly
If Not .BOF And Not .EOF Then
While Not .EOF
strAreas = strAreas & "'" & Trim(!area) & "',"
.MoveNext
Wend
strAreas = Left(strAreas, Len(strAreas) - 1)
End If
.Close
End With
With Sliic_Conexion
.ConnectionString = "Provider=MSDAORA.1;Password=iq;User ID=sfciq;Data Source=Oram01.w1hdbp02;Persist Security Info=True"
.Open
End With
' Clipboard.SetText "SELECT SLI.COM_SKU.FK_COM_STYLE_CODE AS ESTILO, SLI.COM_SKU.FK_COM_COLOR_CODE AS COLOR, SLI.COM_SKU.FK_COM_SIZE_CODE AS ZIZE, COUNT(SLI.ICM_INV_ITM.LP_CDE) AS CANT, SLI.ICM_INV_ITM.FK_COM_AREA_CDE AS AREA, SLI.ICM_INV_ITM.FK_COM_FCLTY_CDE as Facilidad, SLI.ICM_INV_ITM.STATUS" _
& " FROM SLI.COM_SKU, SLI.ICM_INV_ITM" _
& " WHERE SLI.COM_SKU.INTERNAL_NUMBER = SLI.ICM_INV_ITM.FK_COM_SKU_INT_NBR AND SLI.ICM_INV_ITM.FK_COM_FCLTY_CDE IN (" & Facs & ") AND AND (SLI.ICM_INV_ITM.FK_COM_AREA_CDE IN (" & strAreas & ")" _
& " GROUP BY SLI.COM_SKU.FK_COM_STYLE_CODE, SLI.COM_SKU.FK_COM_COLOR_CODE, SLI.COM_SKU.FK_COM_SIZE_CODE, SLI.ICM_INV_ITM.FK_COM_AREA_CDE, SLI.ICM_INV_ITM.FK_COM_FCLTY_CDE, SLI.ICM_INV_ITM.STATUS"
Dim i As Long
With Inventario
'.Open ("INSERT INTO Datos_Inventario IN " & Conexion.Properties("Data Source").Value & " SELECT SLI.COM_SKU.FK_COM_STYLE_CODE AS ESTILO, SLI.COM_SKU.FK_COM_COLOR_CODE AS COLOR, SLI.COM_SKU.FK_COM_SIZE_CODE AS ZIZE, COUNT(SLI.ICM_INV_ITM.LP_CDE) AS CANT, SLI.ICM_INV_ITM.FK_COM_AREA_CDE AS AREA, SLI.ICM_INV_ITM.FK_COM_FCLTY_CDE as Facilidad, SLI.ICM_INV_ITM.STATUS,SLI.ICM_INV_ITM.FK_COM_LOC_AISLE AS AISLE,SLI.ICM_INV_ITM.FK_COM_LOC_ROW AS FILA,SLI.ICM_INV_ITM.FK_COM_LOC_COLUMN AS COL,LP_CDE as LP" _
& " FROM SLI.COM_SKU, SLI.ICM_INV_ITM" _
& " WHERE SLI.COM_SKU.INTERNAL_NUMBER = SLI.ICM_INV_ITM.FK_COM_SKU_INT_NBR AND SLI.ICM_INV_ITM.FK_COM_FCLTY_CDE IN (" & Facs & ") AND SLI.ICM_INV_ITM.FK_COM_AREA_CDE IN (" & strAreas & ")" _
& " GROUP BY LP_CDE,SLI.COM_SKU.FK_COM_STYLE_CODE, SLI.COM_SKU.FK_COM_COLOR_CODE, SLI.COM_SKU.FK_COM_SIZE_CODE, SLI.ICM_INV_ITM.FK_COM_AREA_CDE, SLI.ICM_INV_ITM.FK_COM_FCLTY_CDE, SLI.ICM_INV_ITM.STATUS,SLI.ICM_INV_ITM.FK_COM_LOC_AISLE,SLI.ICM_INV_ITM.FK_COM_LOC_ROW,SLI.ICM_INV_ITM.FK_COM_LOC_COLUMN"), Sliic_Conexion, adOpenForwardOnly, adLockReadOnly

.Open ("SELECT SLI.COM_SKU.FK_COM_STYLE_CODE AS ESTILO, SLI.COM_SKU.FK_COM_COLOR_CODE AS COLOR, SLI.COM_SKU.FK_COM_SIZE_CODE AS ZIZE, COUNT(SLI.ICM_INV_ITM.LP_CDE) AS CANT, SLI.ICM_INV_ITM.FK_COM_AREA_CDE AS AREA, SLI.ICM_INV_ITM.FK_COM_FCLTY_CDE as Facilidad, SLI.ICM_INV_ITM.STATUS,SLI.ICM_INV_ITM.FK_COM_LOC_AISLE AS AISLE,SLI.ICM_INV_ITM.FK_COM_LOC_ROW AS FILA,SLI.ICM_INV_ITM.FK_COM_LOC_COLUMN AS COL,LP_CDE as LP" _
& " FROM SLI.COM_SKU, SLI.ICM_INV_ITM" _
& " WHERE SLI.COM_SKU.INTERNAL_NUMBER = SLI.ICM_INV_ITM.FK_COM_SKU_INT_NBR AND SLI.ICM_INV_ITM.FK_COM_FCLTY_CDE IN (" & Facs & ") AND SLI.ICM_INV_ITM.FK_COM_AREA_CDE IN (" & strAreas & ")" _
& " GROUP BY LP_CDE,SLI.COM_SKU.FK_COM_STYLE_CODE, SLI.COM_SKU.FK_COM_COLOR_CODE, SLI.COM_SKU.FK_COM_SIZE_CODE, SLI.ICM_INV_ITM.FK_COM_AREA_CDE, SLI.ICM_INV_ITM.FK_COM_FCLTY_CDE, SLI.ICM_INV_ITM.STATUS,SLI.ICM_INV_ITM.FK_COM_LOC_AISLE,SLI.ICM_INV_ITM.FK_COM_LOC_ROW,SLI.ICM_INV_ITM.FK_COM_LOC_COLUMN"), Sliic_Conexion, adOpenForwardOnly, adLockReadOnly
MsgBox .ActiveCommand

' If Not .BOF And Not .EOF Then
' Conexion.Execute "delete from datos_inventario"
' While Not .EOF
' Conexion.Execute "insert into datos_inventario(estilo,color,sixe,cant,facilidad,area,status,aisle,fila,col,lp)" _
' & " values('" & !Estilo & "','" & !Color & "','" & !zize & "'," & !cant & ",'" & !Facilidad & "','" & !area & "','" & !Status & "','" & !aisle & "','" & !fila & "','" & !col & "','" & !lp & "')"
' i = i + 1
' .MoveNext
' Wend
' End If
End With
Set Facilidades = Nothing
Set Areas = Nothing
MsgBox "Finalizó Proceso.", vbInformation
End Sub

programador666
25 de Julio del 2005
Por favor alguien que me ayude