Click here to Skip to main content
15,904,934 members
Home / Discussions / Database
   

Database

 
GeneralRe: Storing RTF in SQL Pin
MartyK20079-Oct-07 1:05
MartyK20079-Oct-07 1:05 
QuestionCreating Virtual Directories in Reporting Services Pin
Karuna Kumar8-Oct-07 20:44
Karuna Kumar8-Oct-07 20:44 
AnswerRe: Creating Virtual Directories in Reporting Services [modified] Pin
John Gathogo8-Oct-07 23:18
John Gathogo8-Oct-07 23:18 
QuestionJoining 2 Tables Pin
Brendan Vogt8-Oct-07 20:22
Brendan Vogt8-Oct-07 20:22 
AnswerRe: Joining 2 Tables Pin
Krish - KP8-Oct-07 21:09
Krish - KP8-Oct-07 21:09 
QuestionSQL 2005 SQL Profiler? Pin
devvvy8-Oct-07 16:35
devvvy8-Oct-07 16:35 
Answerfound one --- Re: SQL 2005 SQL Profiler? Pin
devvvy8-Oct-07 18:41
devvvy8-Oct-07 18:41 
QuestionIntermittent errors..please help!!! Pin
new_phoenix8-Oct-07 10:51
new_phoenix8-Oct-07 10:51 
I step through the code, and sometimes the code works, but then again, sometimes the code does not work. The error message I get is that "The Microsoft Jet database engine cannot find the input table or query 'qryPassThroughQuery'. Make sure it exists and that its name is spelled correctly."

I cannot understand it. On one occasion it will accept the record and pass through it without a problem. On another occasion, it does not accept it at all, providing this message. Could somebody please provide some advice???

Why can't the code find the query when the line practically in front of it does just that -- creates the query that it needs in that line???

Private Sub ProcessHeadcountRecords()
    Dim Cnxn As ADODB.Connection
    Dim strConn As String
    Dim rstInputFile As ADODB.Recordset
    Dim cmdSQLInputFile As ADODB.Command
    Dim strSQLInputFile As String
    Dim rstHyperionMany As ADODB.Recordset
    Dim cmdSQLHyperionMany As ADODB.Command
    Dim strSQLHyperionMany As String
    Dim rstHyperionOne As ADODB.Recordset
    Dim cmdSQLHyperionOne As ADODB.Command
    Dim strSQLHyperionOne As String
    Dim rstQueryDef As ADODB.Recordset
    Dim strPassThroughQuery As String
    Dim strDBPath As String
    Dim strFileName As String
    Dim strMessage As String
    
    strDBPath = "J:\GELCO DATABASE\Headcount Database\Headcount Database.mdb"
    Set dbsHeadcount = OpenDatabase(strDBPath)
    Set Cnxn = New ADODB.Connection
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=" & strDBPath & ""
    Cnxn.Open strConn
    
    Set rstInputFile = New ADODB.Recordset
    strSQLInputFile = "SELECT [COUNTRY], [TYPE], [BUSINESS_UNIT], " & _
        "[L_R_G], [REGION], [JOB_FUNCTION], [ACTUAL], [NUMINDEX] " & _
        "FROM TBLINPUTFILE"
    rstInputFile.Open strSQLInputFile, Cnxn, adOpenKeyset, adLockOptimistic
    rstInputFile.MoveFirst
    
    Set rstHyperionMany = New ADODB.Recordset
    strSQLHyperionMany = "SELECT [COUNTRY], [TYPE], [BUSINESS_UNIT], " & _
        "[L_R_G], [REGION], [JOB_FUNCTION], [NUMFOREIGNKEY] " & _
        "FROM [TBLHYPERIONMANY2] ORDER BY [NUMFOREIGNKEY]"
    rstHyperionMany.Open strSQLHyperionMany, Cnxn, adOpenKeyset, adLockOptimistic
    

    strFileName = "qryPassThroughQuery"
    txtMessageBoxText.SetFocus
    txtMessageBoxText.Text = "Processing all the records from the upload file " & _
        "to an updated file to be imported into Hyperion."
        
    Do Until rstInputFile.EOF
        With dbsHeadcount
            strSQLHyperionMany = "SELECT [COUNTRY], [TYPE], " & _
                "[BUSINESS_UNIT], [L_R_G], [REGION], [JOB_FUNCTION], [NUMFOREIGNKEY] " & _
                "FROM [TBLHYPERIONMANY2] " & _
                "WHERE [COUNTRY]='" & UCase(rstInputFile![COUNTRY]) & "' " & _
                "AND [TYPE]='" & UCase(rstInputFile![Type]) & "' " & _
                "AND [BUSINESS_UNIT]='" & UCase(rstInputFile![BUSINESS_UNIT]) & "' " & _
                "AND [L_R_G]='" & UCase(rstInputFile![L_R_G]) & "' " & _
                "AND [REGION]='" & UCase(rstInputFile![REGION]) & "' " & _
                "AND [JOB_FUNCTION]='" & UCase(rstInputFile![JOB_FUNCTION]) & "'"
            rstHyperionMany.Requery
            Set qdfNew = dbsHeadcount.CreateQueryDef(strFileName, strSQLHyperionMany)   // CREATES THE QUERY
            DoCmd.SetWarnings False
            
            Set rstQueryDef = New ADODB.Recordset
            strPassThroughQuery = "SELECT * FROM qryPassThroughQuery"
            rstQueryDef.Open strPassThroughQuery, Cnxn, adOpenKeyset, adLockOptimistic     // NEEDS TO USE THE QUERY ***HERE ***
            rstQueryDef.Requery
            
            If rstQueryDef.RecordCount = 1 Then
                rstInputFile![NUMINDEX] = rstQueryDef![NUMFOREIGNKEY]
            Else
                'MsgBox ("Record not found")
            End If
            prgProgressBar.Value = prgProgressBar.Value + 0.4
        End With
        dbsHeadcount.QueryDefs.Delete qdfNew.Name
        rstQueryDef.Close
        rstInputFile.MoveNext
    Loop
End Sub

AnswerRe: Intermittent errors..please help!!! Pin
andyharman9-Oct-07 1:30
professionalandyharman9-Oct-07 1:30 
QuestionVISTA and SQL SERVER 2005 Pin
Yoyosch8-Oct-07 7:23
Yoyosch8-Oct-07 7:23 
AnswerRe: VISTA and SQL SERVER 2005 Pin
Andy_L_J9-Oct-07 22:19
Andy_L_J9-Oct-07 22:19 
QuestionODB Problem ? Pin
hungdl8-Oct-07 6:36
hungdl8-Oct-07 6:36 
AnswerRe: ODB Problem ? Pin
Dave Kreskowiak8-Oct-07 6:48
mveDave Kreskowiak8-Oct-07 6:48 
GeneralRe: ODB Problem ? Pin
hungdl8-Oct-07 7:24
hungdl8-Oct-07 7:24 
GeneralRe: ODB Problem ? Pin
Dave Kreskowiak8-Oct-07 9:17
mveDave Kreskowiak8-Oct-07 9:17 
QuestionConverting Integet to Time: Pin
Shahzad.Aslam7-Oct-07 23:46
Shahzad.Aslam7-Oct-07 23:46 
AnswerRe: Converting Integet to Time: Pin
PIEBALDconsult8-Oct-07 5:16
mvePIEBALDconsult8-Oct-07 5:16 
AnswerRe: Converting Integet to Time: Pin
Virendrak11-Oct-07 0:56
Virendrak11-Oct-07 0:56 
QuestionRemote Connection Pin
Xerox47-Oct-07 21:43
professionalXerox47-Oct-07 21:43 
QuestionWhat sql server 2000 componenets do I have? Pin
Jwalant Natvarlal Soneji7-Oct-07 21:20
Jwalant Natvarlal Soneji7-Oct-07 21:20 
AnswerRe: What sql server 2000 componenets do I have? Pin
Jwalant Natvarlal Soneji8-Oct-07 16:53
Jwalant Natvarlal Soneji8-Oct-07 16:53 
Questioncross tab query problem Pin
veereshIndia7-Oct-07 20:01
veereshIndia7-Oct-07 20:01 
AnswerRe: cross tab query problem Pin
pmarfleet7-Oct-07 21:50
pmarfleet7-Oct-07 21:50 
Questionfunction multi statement table values in stored procedure Pin
Sonia Gupta7-Oct-07 19:30
Sonia Gupta7-Oct-07 19:30 
AnswerRe: function multi statement table values in stored procedure Pin
N a v a n e e t h7-Oct-07 22:12
N a v a n e e t h7-Oct-07 22:12 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.