Results 1 to 4 of 4
  1. #1
    Join Date
    May 2013
    Posts
    2

    Unanswered: Passthrough Query, Appends, and the madness within

    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:

    Code:
    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.

    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

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What surprises me is that you do not specify any connection string for the querydefs when you create them (Connect property). How do you create Pass-Through queries that way?
    Have a nice day!

  3. #3
    Join Date
    May 2013
    Posts
    2
    I found a bunch of code on that and I can add that in like this:

    Code:
    strcon = "ODBC;DSN=SB2;Server=AB2001;Uid=admin;Pwd=admin;"
    What I wanted to do was figure out the nitty gritty stuff first and then store in the connection information. The query will prompt me for that no matter if I store it or not.

    Also, I did not create the code above. It was handed to me to edit. AKA "Here is what we did before (with little to no explination), just modify it to work with what you are doing. I'm pretty in the dark here.
    Last edited by warpathmechanic; 05-03-13 at 17:07. Reason: additional info needed

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by warpathmechanic View Post
    I have two different database sources that CANNOT talk to each other (For reference we will call them DB1 and DB2).

    Quote Originally Posted by warpathmechanic View Post
    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:
    As far as I can understand, the information you provide are contradictory. You wrote that DB1 is the main source of data, while DB2 provides the condition. However, later on, we can read this which tells just the contrary (DB1 provides the condition and DB2 provides the data):
    Quote Originally Posted by warpathmechanic View Post
    SELECT * FROM DB2 WHERE Account # = (SELECT Account # from DB1)
    ...
    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.
    I'll go with the second hypothesis in this example.

    1. Create a Pass-Through query to extract the criteria data set from DB1:
    Code:
    Const c_Connection As String = "ODBC;DSN=..." 'Connection string to DB1.
    Const c_SQL As String = "SELECT [Account #] from TableInDB1"
    
    Dim qfd As DAO.QueryDef
    
    If DCount("*", "MSysObjects", "Name = 'qry_DB1'") = 0 Then
        Set qdf = CurrentDb.CreateQueryDef("qry_DB1")
    Else
        Set qdf = CurrentDb.QueryDefs("qry_DB1")
    End If
    With qdf
        .Connection = strConnection
        .SQL = strSQL
        .Close
    End With
    Set qdf = Nothing
    2. Create a Select query to extract the data set from DB2:
    Code:
    Const c_Connection As String = "ODBC;DSN=..." 'Connection string to DB2.
    Const c_SQL As String = "SELECT [Account #], [Contract #] from TableInDB2"
    
    Dim qfd As DAO.QueryDef
    
    If DCount("*", "MSysObjects", "Name = 'qry_DB2'") = 0 Then
        Set qdf = CurrentDb.CreateQueryDef("qry_DB2")
    Else
        Set qdf = CurrentDb.QueryDefs("qry_DB2")
    End If
    With qdf
        .Connection = strConnection
        .SQL = strSQL
        .Close
    End With
    Set qdf = Nothing
    3. You can now create an Append query to insert data into a local table:
    Code:
    Const c_SQL As String = "INSERT INTO LocalTable ( [Account #], [Contract #] ) " & _
                                "SELECT [Account #], [Contract #] FROM qry_DB2 INNER JOIN qry_DB1 " & _
                                "ON qry_DB2.[Account #] = qry_DB1.[Account #];"
    CurrentDb.Execute c_SQL, dbFailOnError
    Change TableInDB1 and TableInDB2 to the actual name of the tables in both databases, LocalTable to the actual name of the table in the local Access database and complete the connection strings for both Oracle databases.
    Have a nice day!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •