VBA ADO SQL Query Sporadic Results

[Mav3000]

Hi All,

I query an SQL Database via VBA and wish to return results from the recordset into variables.

The problem is that some of the fields, although populated on the Database, don't show values in VBA, or, they show values, but then mysteriously change to 'Null' when I try to do anything with them.

Specifying specific fields to query, and changing the field order in the SQL SELECT code seems to change the amount of populated fields too.

Why is this and what's going on?!

My Query is as follows:

sSQL = "SELECT * " & _
"FROM " & _
"dtblPEN0001Jobs, " & _
"dtblPEN0001Options " & _
"WHERE " & _
"dtblPEN0001Options.Reference='" & sPropertyRef & "' " & _
"AND dtblPEN0001Jobs.Reference='" & sLeaseRef & "' " & _
"AND dtblPEN0001Jobs.OptionID = dtblPEN0001Options.OptionID"

oDatabase.Open sSQL, _
oConnection, _
adOpenForwardOnly, _
adLockReadOnly, _
adCmdText

' Then I populate my variables with the returned results:
If IsNull(oDatabase("S_Site_Status")) = False Then sPropertyStatus = oDatabase("S_Site_Status")
If IsNull(oDatabase("N_Property_Area")) = False Then sPropertyArea = oDatabase("N_Property_Area")

' However some field types, although 'LEN' shows as having a length, and tooltip help showing the value, change to 'NULL' when I try to add them to a variable:

If Len(oDatabase("M_Repair_Obligations")) > 0 Then sRepairObligations = oDatabase("M_Repair_Obligations")
If Len(oDatabase("M_Insurance_Obligations")) > 0 Then sInsuranceObligations = oDatabase("M_Insurance_Obligations")

' --------------

On the lower two rows, Len(oDatabase("M_Repair_Obligations")) returns '84', but then the field shows as NULL, and produces an error when I try to add it to a field. All other fields below this (beginning with M_) then show NULL too - even though their LEN values are greater than zero.

Any help would be greatly appreciated!

Mav3000

 

[SkipVought]

Hi,

You need to look carefully at the ADO recordset OPEN statement. Althought this query does not access an SQL Server db, it demonstrates the open statement...

Function GetINDN(sPN As String, sTRAV As String) As String
'SkipVought/2007 Mar 19/
'--------------------------------------------------
' Access: A010PROD.FPRPTSAR.READ
'--------------------------------------------------
':gets Indenture for a Part for a given TRAVELER
'--------------------------------------------------
    Dim sConn As String, sSQL As String, sServer As String
    Dim rst As ADODB.Recordset, cnn As ADODB.Connection
    
    Set cnn = New ADODB.Connection
    
    sServer = "A010PROD"
    cnn.Open "Driver={Microsoft ODBC for Oracle};" & _
               "Server=" & sServer & ";" & _
               "Uid=/;" & _
               "Pwd="
    
    Set rst = New ADODB.Recordset
    
    sSQL = sSQL & "SELECT BOM_IND"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "FROM FPRPTSAR.SHIP_SHORTAGES"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "WHERE SHORT_PARENT ='" & sTRAV & "'"
    sSQL = sSQL & "  AND PART_ID      ='" & sPN & "'"

    Debug.Print sSQL

    rst.Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
                          
    On Error Resume Next
                          
    rst.MoveFirst

    If Err.Number = 0 Then
        GetINDN = rst(0)
    End If
    
    rst.Close
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
End Function

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Mav3000]

Thanks for the reply Skip, My OPEN Statement (including the initial bit left off from my post above, goes like this...

Function OpenDatabase()

sDatabasePath = "\backdoor.mdb"
sConnection = "Provider=MSDASQL.1;" & _
"Password=password; " & _
"Persist Security Info=True; " & _
"User ID=username; " & _
"Data Source=databasename; " & _
"Initial Catalog=databasename"

Set oConnection = New ADODB.Connection

' Open Database connection
oConnection.ConnectionTimeout = 0
oConnection.ConnectionString = sConnection
oConnection.Open

End Function

'---------------------

Function QueryDatabase

Dim oDatabase As ADODB.Recordset
Dim sSQL As String

' Database SQL Query
sSQL = 'SQL Statement

Set oDatabase = New ADODB.Recordset

' Query Database
If bConnectionOpen = False Then OpenDatabase

oDatabase.Open sSQL, _
oConnection, _
adOpenForwardOnly, _
adLockReadOnly, _
adCmdText

End Function

'-----------

I would have copied this connection method from somewhere and have used it without complete knowledge of if it is 100% suitable for my database or not.

Does it look ok? How could it be causing the problem?

Thanks for the help,

 

[SkipVought]

I could not determine what knid of object oDatabase was.

Try using Debug.Print to return your SQL string.

Then, open the db and run your query manually using that SQL code, to be absolutely sure that it functions as expected.

If it does, then we have something else to look for.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Mav3000]

Thanks Skip,

I'll investigate further and report back if needed. I've also changed a couple of bits based on your connection function, so will see how that goes too.

Cheers,

Mav3000