Passthrough Query, Appends, and the madness within (1 Viewer)

[warpathmechanic]

I have scoured the internet in search for what I am attempting to do. It is complicated and confusing but I am going to do my best to explain here as I cannot give too much information regarding my company.

I have two different database sources that CANNOT talk to each other (For reference we will call them DB1 and DB2).

DB1 is the main source of data I need to pull two different columns of data for:
Account #
Contract #

I need to take each column of data individually and insert them into queries containing tables from DB2 (an Oracle database). For example:

SELECT * FROM DB2 WHERE Account # = (SELECT Account # from DB1)

Connections:

DB1 Server: P007.ORACLEDB (Microsoft ODBC for Oracle)
DB2 Server: DWHODP1.us (Oracle BI Server)

Normally this would work but since they cannot talk to each other I am at a loss.

Someone sent me this:

 Function Main()

Dim dbsRIDoublecheck As Database
Dim rstList As Recordset
Dim qdfPassThrough As QueryDef
Dim rstQueryResults As Recordset


Set dbsRIDoublecheck = CurrentDb
Set qdfPassThrough = dbsRIDoublecheck.QueryDefs("qryRIDoubleCheck")
Set qdfClearResults = dbsRIDoublecheck.QueryDefs("qryClearRIDoubleCheck")
qdfClearResults.Execute
Set qdfAppendResults = dbsRIDoublecheck.QueryDefs("qryAppendRIDoubleCheck")

Set rstList = dbsRIDoublecheck.OpenRecordset("Base_TCDDESC_List", dbOpenSnapshot)
rstList.MoveFirst
Do
  strSQL = "SELECT '" & rstList!shm_na & "." & rstList!Tbl_Na & "' as Table_Name, b.col_na as Column_Name, " & rstList!col_na & " as Value, count(*) as Occurrences, to_char(localtimestamp, 'yyyy-mm-dd hh24:mi:ss') as Run_Time from " & rstList!shm_na & ".V" & Right(rstList!Tbl_Na, Len(rstList!Tbl_Na) - 1) & " a left outer join  O311IA.VCDDESC b on a." & rstList!col_na & " = b.TBL_CLU_VLU_CD where b.shm_na = '" & rstList!shm_na & "' and b.tbl_na = '" & rstList!Tbl_Na & "' and b.col_na = '" & rstList!col_na & "' and b.TBL_CLU_VLU_CD Is Null Group by '" & rstList!shm_na & "." & rstList!Tbl_Na & "', b.col_na, " & rstList!col_na & ", to_char(localtimestamp, 'yyyy-mm-dd hh24:mi:ss') UNION select '" & rstList!shm_na & "." & rstList!Tbl_Na & "', '" & rstList!col_na & "', 'MARKER',0, to_char(localtimestamp, 'yyyy-mm-dd hh24:mi:ss') from dual"
  Debug.Print strSQL
  qdfPassThrough.SQL = strSQL
  qdfAppendResults.Execute
  rstList.MoveNext
Loop While Not rstList.EOF

End Function

But I cannot make heads or tails of what I would need to edit. The code is supposed to use a passthrough query to obtain the lists from DB1 then use it as a parameter in a query to DB2 then append the records into a local table.

The reason why there is no connection string is because the database will prompt with a UN and PW no matter if I store it or not. I have found language that will allow me to store the connection above but that is not what I can't figure out.

Ultimately the goal is to use the columns from DB1 as criteria to queries using tables from DB2 then appending the results to a local table in MS Access all while in VBA.

Keep in mind that while I am intermediate in MS ACCESS I am a complete novice in VBA.

ANY ASSISTANCE WILL RESULT IN GODLIKE PRAISE.

Thank you,

warpathmechanic

 

[Pat Hartman]

If the tables are in different databases, you can't use a pass-through query since that will go to one but not both. You need to link the tables to your Access FE. Then using querydefs (Access queries), select both tables and draw the appropriate join lines. Choose the columns you need. This query can be used in an append query to append the data to a Jet/ACE table. No code is nessary.