dire help needed

Windows based server farm, SQL 2003, client side is W2k, vb6, Access
97...(i cant upgrade now,..legacy issues), all db access is done thru
daoobject.
Program walks thru the database object on the server farm table by
table. It checks each table for new data, if present it then executes
an insert into the access database from the sql database.
Problem is that it suddenly started only loading certain tables and not
loading the other tables. The tables that are successful use the same
sql statement as the tables that fail. The failure does not give a
return code so its not detectable except by the absence of data for that
table.
Set ws = DBEngine.Workspaces(0)
Set dbData = ws.OpenDatabase(strNull, False, False, strProvider)
strSQL = "Select * From [" & sTableData & "] As " & sTableData & "SQL" &
" Where " & sFieldName1 & " > #" & TheDate & "#;"
Set rs =
dbData.OpenRecordset(strSQL, dbOpenDynaset)
If rs.RecordCount > 0 Then
insSQL = "INSERT INTO [" & sNewTable & "] in '" & strDbPath & "'
Select A.* From " & sTableData & " AS A where A." & sFieldName1 & " = #"
& TheDate & "#;"
DoEvents
dbData.Execute insSQL, dbFailOnError
If Err = 0 Then
blah blah...
else
more blah
endif
endif
Woody
any sugestion or comment made by me should be examined first for
validity and appropriateness before assuming i have any idea at all
what the heck i am talking about. I am not responsible for anything you
may see with my name attached to it, i think.
*** Sent via Developersdex http://www.examnotes.net ***DAO?
;-) ha haa
In the below code, you will need to confirm if ErrorNumber is the default
property of the Err object. You should probably call that property
explicitly.

> If Err = 0 Then
> blah blah...
> else
> more blah
> endif
Try commenting out the "on error ..." portion of your error handler so that
any errors are raised unhandled back to Visual Basic.
In addition to checking for errors raised to Visual Basic, confirm if DAO is
returning any errors in the DBEngine.Errors collection.
http://msdn.microsoft.com/archive/d...rorhandling.asp
"Woody" <lost@.cyberspace.com> wrote in message
news:utAk95aAGHA.3584@.TK2MSFTNGP14.phx.gbl...
> Windows based server farm, SQL 2003, client side is W2k, vb6, Access
> 97...(i cant upgrade now,..legacy issues), all db access is done thru
> daoobject.
> Program walks thru the database object on the server farm table by
> table. It checks each table for new data, if present it then executes
> an insert into the access database from the sql database.
> Problem is that it suddenly started only loading certain tables and not
> loading the other tables. The tables that are successful use the same
> sql statement as the tables that fail. The failure does not give a
> return code so its not detectable except by the absence of data for that
> table.
> Set ws = DBEngine.Workspaces(0)
> Set dbData = ws.OpenDatabase(strNull, False, False, strProvider)
> strSQL = "Select * From [" & sTableData & "] As " & sTableData & "SQL" &
> " Where " & sFieldName1 & " > #" & TheDate & "#;"
> Set rs =
> dbData.OpenRecordset(strSQL, dbOpenDynaset)
> If rs.RecordCount > 0 Then
> insSQL = "INSERT INTO [" & sNewTable & "] in '" & strDbPath & "'
> Select A.* From " & sTableData & " AS A where A." & sFieldName1 & " = #"
> & TheDate & "#;"
> DoEvents
> dbData.Execute insSQL, dbFailOnError
> If Err = 0 Then
> blah blah...
> else
> more blah
> endif
> endif
>
> Woody
> any sugestion or comment made by me should be examined first for
> validity and appropriateness before assuming i have any idea at all
> what the heck i am talking about. I am not responsible for anything you
> may see with my name attached to it, i think.
> *** Sent via Developersdex http://www.examnotes.net ***sql