Hi all,
I've never come across this one before and am hoping someone may be
able to shed some light on a very strange situation.
I'm using a DNS less connection and a ADO recordset to retrieve
records from a networked database. The connection and query all works
fine, but the particular field I'm interested in suddenly becomes NULL
after a couple of seconds or after I've referenced it once. I've got
some test code below which produces the same results. Can anyone see
what's going on?
The two debug statements return this result each time (second one
becomes NULL for no reason) -
Check 1 200403061037112 * L06040400001539 8.hdm
Check 2 200403061037112 *
TIA,
Brad
Dim path As String, i As Integer, dest As String, files As Recordset,
db As Database, rdt As New ADODB.Recordset
Dim sql As String, mycn As New ADODB.Connectio n, stConn As String,
notfound() As String
ReDim Preserve notfound(0)
'set local database
Set db = CurrentDb()
Set files = db.OpenRecordse t("tbl_multex_p df_locations",
dbOpenDynaset)
'set network query
stConn = "Driver={SQ L Server}; Server=ip here; Database=Resear chNet;
Uid=loginhere; Pwd=passwordher e"
mycn.Open stConn
sql = "SELECT document_table. doc_id AS Doc_id,
network_doc_tab le.nd_fileroots FROM document_table INNER JOIN
network_doc_tab le ON document_table. doc_id =
network_doc_tab le.nd_doc_id " _
& "WHERE (((document_tab le.doc_id) In (SELECT document_table. doc_id "
_
& "FROM (document_table INNER JOIN doc_pub_product _table ON
document_table. doc_id = doc_pub_product _table.doc_pub_ product_doc_id)
INNER JOIN pub_product_tab le ON
doc_pub_product _table.doc_pub_ product_pub_pro duct_id =
pub_product_tab le.pub_product_ id " _
& "WHERE (((document_tab le.doc_date) Between '1-Apr-04' And
'1-May-04') AND ((RTrim([pub_product_nam e]))='Asia Ex Japan' Or
(RTrim([pub_product_nam e]))='Japan')))) AND
((document_tabl e.doc_publish_f lag)=1) AND
((network_doc_t able.nd_network _id)='100000000 0000002'))"
'Debug.Print sql
mycn.CommandTim eout = 120
Set rdt = mycn.Execute(sq l)
rdt.MoveFirst
Debug.Print "Check 1 " & rdt(0) & " * " & rdt(1)
Debug.Print "Check 2 " & rdt(0) & " * " & rdt(1)
I've never come across this one before and am hoping someone may be
able to shed some light on a very strange situation.
I'm using a DNS less connection and a ADO recordset to retrieve
records from a networked database. The connection and query all works
fine, but the particular field I'm interested in suddenly becomes NULL
after a couple of seconds or after I've referenced it once. I've got
some test code below which produces the same results. Can anyone see
what's going on?
The two debug statements return this result each time (second one
becomes NULL for no reason) -
Check 1 200403061037112 * L06040400001539 8.hdm
Check 2 200403061037112 *
TIA,
Brad
Dim path As String, i As Integer, dest As String, files As Recordset,
db As Database, rdt As New ADODB.Recordset
Dim sql As String, mycn As New ADODB.Connectio n, stConn As String,
notfound() As String
ReDim Preserve notfound(0)
'set local database
Set db = CurrentDb()
Set files = db.OpenRecordse t("tbl_multex_p df_locations",
dbOpenDynaset)
'set network query
stConn = "Driver={SQ L Server}; Server=ip here; Database=Resear chNet;
Uid=loginhere; Pwd=passwordher e"
mycn.Open stConn
sql = "SELECT document_table. doc_id AS Doc_id,
network_doc_tab le.nd_fileroots FROM document_table INNER JOIN
network_doc_tab le ON document_table. doc_id =
network_doc_tab le.nd_doc_id " _
& "WHERE (((document_tab le.doc_id) In (SELECT document_table. doc_id "
_
& "FROM (document_table INNER JOIN doc_pub_product _table ON
document_table. doc_id = doc_pub_product _table.doc_pub_ product_doc_id)
INNER JOIN pub_product_tab le ON
doc_pub_product _table.doc_pub_ product_pub_pro duct_id =
pub_product_tab le.pub_product_ id " _
& "WHERE (((document_tab le.doc_date) Between '1-Apr-04' And
'1-May-04') AND ((RTrim([pub_product_nam e]))='Asia Ex Japan' Or
(RTrim([pub_product_nam e]))='Japan')))) AND
((document_tabl e.doc_publish_f lag)=1) AND
((network_doc_t able.nd_network _id)='100000000 0000002'))"
'Debug.Print sql
mycn.CommandTim eout = 120
Set rdt = mycn.Execute(sq l)
rdt.MoveFirst
Debug.Print "Check 1 " & rdt(0) & " * " & rdt(1)
Debug.Print "Check 2 " & rdt(0) & " * " & rdt(1)