Pulling Oracle data into Access

[merlynsdad]

I inherited an Access db which reaches out via VBA to 5 identical Oracle DBs (one from each site) via ODBC. In the code below each of the Oracle DBs is identified as "& txtDatabase &" using tblACDtoLogon for the actual DB names, and it rotates among them. I know Access is reaching Oracle because tblCD_Log is updating with each pass, and the Watch window shows me which Oracle DB I'm reaching, when I run it manually. For testing purposes I'm only pulling down one field from one table in each of the Oracle DBs right now. The code runs without error, but is not pulling down the data I need, in this case the ext_num, and I know the data is on the Oracle DBs. Here's the code; what am I doing wrong? I don't work with Access VBA all that much.

Public Sub cmdLogon_AppendUserInfo()

Dim db As Database
Dim qdfpassThrough As QueryDef
Dim txtDatabase As String
Dim txtUser As String
Dim txtPassword As String
Dim rst1, rst2 As DAO.Recordset
Dim BegDT_CD As Date
Dim EndDT_CD As Date
Dim qryDate As Long
Dim QryLoc As String
Dim QryApp As Integer
Dim Section_CD As String

' Set code to work with this database

Set db = CurrentDb

Section_CD = ""
BegDT_CD = 0
EndDT_CD = 0
qryDate = 0
QryLoc = ""
QryApp = 0

' Clear the data from the tblCD_Log and temp_agent tables

db.Execute "DELETE tblCD_Log.* FROM tblCD_Log;"
db.Execute "DELETE temp_agent.*FROM temp_agent;"

' Start pulling the data from Oracle

Section_CD = "LogOn_PullAgentData"

' Open the password database, go to tblACDtoLogon and move to the first record

Set rst1 = db.OpenRecordset("SELECT tblACDtoLogon.acd, tblACDtoLogon.Name, tblACDtoLogon.psw " _
& "FROM tblACDtoLogon;", dbOpenDynaset)
rst1.MoveFirst

' Logon and do the following with each record until you come to the End Of File
' This loop rotates through the DBs in tblACDtoLogon

Do While Not rst1.EOF
BegDT_CD = Now
With rst1
txtDatabase = rst1![ACD].Value
txtUser = rst1![Name].Value
txtPassword = rst1![psw].Value
End With

Set qdfpassThrough = db.QueryDefs("qryLogon")
' DB changes on next line
qdfpassThrough.Connect = "ODBC;DSN=" & txtDatabase & ";DBQ=" & txtDatabase & ";UID=" & txtUser & ";PWD=" & txtPassword
qdfpassThrough.SQL = "SELECT SYSDATE FROM DUAL"
qdfpassThrough.ReturnsRecords = False
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryLogon"
DoCmd.Close acQuery, "qryLogon"
DoCmd.SetWarnings True

' If there is data, connect to each of the Oracle dbs (there is one for each callsite called txtDatabase)

qdfpassThrough.Connect = "ODBC;DSN=" & txtDatabase & ";DBQ=" & txtDatabase & ""

' This loop rotatess through each row in the selected DB

For Each qdfpassThrough In db.QueryDefs
If Len(qdfpassThrough.Connect) > 0 Then
qdfpassThrough.Connect = "ODBC;DSN=" & txtDatabase & ";DBQ=" & txtDatabase & ""
End If
Next qdfpassThrough

' Pull the data from Oracle into this database

db.Execute "INSERT INTO temp_agent (ext_num) " & _
"SELECT " & txtDatabase & "_dta_users.ext_num " & _
"FROM " & txtDatabase & "_dta_users;"

QryLoc = "& txtDatabase &"
EndDT_CD = Now
Call Append_CDLog(Section_CD, BegDT_CD, EndDT_CD, qryDate, QryLoc, QryApp)
rst1.MoveNext
Loop
Application.SetHiddenAttribute acQuery, "qryLogon", True ' hide the qryLogon query

End Sub
'//////////////////////////////////////////////////////////////////////////////////////////////////////////
'//////////////////////////////////////////////////////////////////////////////////////////////////////////

Sub Append_CDLog(Section_CD As String, BegDT_CD As Date, EndDT_CD As Date, qryDate As Long, QryLoc, QryApp As Integer)

Dim db As Database
Dim CodeDetailrst As DAO.Recordset

Set db = CurrentDb
Set CodeDetailrst = db.OpenRecordset("tblCD_LOG")
With CodeDetailrst
.AddNew

!Section_CD_t = Section_CD
!BegDT_CD_t = BegDT_CD
!EndDT_CD_t = EndDT_CD
!QryDate_t = qryDate
!QryLoc_t = QryLoc
!QryApp_t = QryApp

.Update
End With
CodeDetailrst.Close

End Sub


If the square peg won't fit in the round hole, sand off the corners.

 

[jedraw]

This line has a syntax error

db.Execute "DELETE temp_agent.*FROM temp_agent;"

Needs a space between * and FROM.

As for the code and logic, I suggest you try stepping thru the code; and you could do a few debug.print statements to see some data values at various points in the program.