dire help needed

15 Dec 2005 7:33 PM

Woody

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.developersdex.com ***

15 Dec 2005 8:09 PM

JT

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/default.asp?url=/archive/en-us/office97/html/errorserrorhandling.asp

Show quote

"Woody" <l***@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.developersdex.com ***