OLE DB error

L

LeAnn

I have an VB Script file that runs an Access 97 procedure. Basically the
script passes several variables to the procedure to extract data from a
Visual Fox Pro database (5 different tables)to text files. The script is
making 5 calls to the same procedure. This has run fine for many months.
All of a sudden, I'm getting the following error on the 3rd call(only 15 of
198 records are written to the text file):

'Multiple-step OLE DB operation generated errors. Check each OLE DB status
value, if available. No work was done.'

All of the drivers being referenced are ODBC. I've read several articles
trying to track the cause and I'm thinking the article "INFO: ADO Spawns
Additional Connections to SQL Server #194979" or "FIX: Uninitialized String
Variables, Empty String Values, Jet Provider, and Errors Occurred #228935"
may be relevent.

Why would this occur all of a sudden? I'm not sure if it is related to
initialization of the variables or additional connctions being spawned. I
have run this on 2 different computers (one definitely has the latest Jet 3.5
SP3).

Any help?
 
L

LeAnn

I have some follow up info on this issue. The script/Access procedure ran
fine last night. When I try to re-run the script for Sunday's data, I
receive the same error. This leads me to believe that there is something
about the data so I looked at it through Crystal. The error is thrown on the
same record each time. The only unusual thing about that record that I can
see is that it has a date with the year of 1950. There is no code that
checks date ranges so I wouldn't think this was the
problem. I have attached the Access procedure which creates a text file
from the source data (Visual Fox Pro). As I said, the procedure is called 5
times in the script file and runs fine 2 times before it fails. One of the
articles mentioned initializing null string variables properly (if I remember
correctly). I did try initializing varString = "", but it still threw the
error. I'm not sure where to go with this one.


Access VBA code:

Public Function ImportDB(ByVal strDB As String, ByVal strODBC As String,
ByVal strSProc As String, _
ByVal strRecSet As String, ByVal strDir As String, ByVal strADir As String,
ByVal strPFile As String, _
ByVal strAFile As String, ByVal strTable As String)
'Description
'This code is utilized to extract data from various systems.

'1. Delete all files from process directory
'2. Create the new file
'3. Get the recordset for the new file
'4. Write the data to the new process file
'5. Copy the file to the archive directory
'6. Run stored procedure to update log information
'7. Close any connections and recordsets
'8. Close and reset

'strDB = The SQL Server database name
'strODBC = The driver for the source data
'strSProc = The stored procedure to update the log table
'strRecSet = The recordset of data to import
'strDir = The main directory for the process file
'strPFile = The name of the process file
'strADir = The name of the archive directory
'strAFile = The name of the archive file
'strTable = The name of the table

'Set the environment and variables
Dim strFile As String
Dim lngFile As Long
Dim conn As Object, res As Object, fld As Object
Dim conn1 As Object, comm1 As Object
Dim varString As Variant
Dim lngRecCnt As Long

strAFile = strAFile & Format(Now, "mmddyyhhmmss") & ".txt"

'1. Delete all files from process directory
Close
Reset

strFile = Dir(strDir)

Do Until strFile = ""
If strFile = strAFile Then
Kill strDir & strFile
End If
strFile = Dir
Loop


'2. Create the new file
lngFile = FreeFile

Open strDir & strPFile For Output As lngFile

'3. Get the recordset for the new file
Set conn = CreateObject("ADODB.Connection")
Set res = CreateObject("ADODB.Recordset")

conn.Open "FILEDSN=" & strODBC
res.LockType = 1
res.Open strRecSet, conn
If res.EOF <> True And res.BOF <> True Then
With res

'4. Write the data to the new process file
.MoveFirst
Do Until .EOF = True
For Each fld In .Fields
If varString = "" Then
varString = fld.Value
Else
varString = varString & "|" & fld.Value
End If
Next

'Add the file name to the string
varString = varString & "|" & strAFile
Print #lngFile, varString
varString = ""
.MoveNext
lngRecCnt = lngRecCnt + 1
Loop
End With
End If
'Close all files
Close
Reset

'5. Copy the file to the archive directory
FileCopy strDir & strPFile, strADir & strAFile

'Close the connection
res.Close
conn.Close

'6. Run stored procedure to update log information
Set conn1 = CreateObject("ADODB.Connection")
conn1.ConnectionString = "FILEDSN=" & UDDEST
conn1.Open

Set comm1 = CreateObject("ADODB.Command")
Set comm1.ActiveConnection = conn1
comm1.CommandText = "USE " & strDB & " EXECUTE " & strSProc & " '" &
strTable & "', " & lngRecCnt & ", '" & strAFile & "'"
comm1.Execute

'7. Close any connections and recordsets
conn1.Close

Set fld = Nothing
Set res = Nothing
Set conn = Nothing
Set comm1 = Nothing
Set conn1 = Nothing

'8. Close and reset
Close
Reset

End Function
 
L

LeAnn

Please disregard. When the (1950) date was modified to the correct date, the
script ran fine. Still don't understand why a date range would initiate that
particular error but as long as it works.


============================
 
Top