Access Stops Writing To My Database

scripter73 · Jun 27, 2001

Hi,

I have an ASP script that does some text file manipulation and then opens an Access database to write the data to a table, and then later it opens the same database to write to another table.

Here's the code for the first table write:

'********************************************************
'OPEN DATABASE CONNECTION - write data to database
'********************************************************
'create connection to database using dsn

dim adopenforwardonly, adlockreadonly, adcmdtable
adopenforwardonly = 0
adlockreadonly = 1
adcmdtable = 2

dim objconn, objrs
set objconn = server.createobject("ADODB.Connection&quot

set objrs = server.createobject("ADODB.Recordset&quot

'line 461
dim strdatabasetype
strdatabasetype = "Access"

'6/27/01 adding commands
MdbFilePath = Server.MapPath("TargetURL.mdb&quot

objconn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & MdbFilePath & ";" (line 468)

objrs.open "Alog", objconn, adopenforwardonly, adlockreadonly, adcmdtable

'add new record
'check to make sure file data to upload is from CONNECT or DOMINO log file

if (strcomp(filechars, "NC&quot

= 0) OR (strcomp(filechars, "AC&quot

=0) then
strSQL = "INSERT INTO Alog (Host_Addr, UserID, LogDate, LogTime, LogRequest, RefURL, OpSysURL) VALUES ('" & _
strHost &"'," & "'" & strUser & "'," & "'" & datDate &"','" & strFinalTime & "','" & strRequest & "'," & "'" & strRefURL & "'," & "'" & strOpSys & "')"

objConn.Execute strSQL

end if

The error I'm getting is:

Microsoft OLE DB Provider for ODBC Drivers error 80004005
[Microsoft][ODBC Microsoft Access 97 Driver] Too many client tasks.

/logs/split.asp, line 468

Can somebody tell me what's going on? Access will allow about 63 records and then just shuts down business. I've been working on these error types for about 3 weeks and its becoming frustrating.

Any help is appreciated.

Thanks,
scripter73

codestorm · Jun 27, 2001

So this code is called once for each insert?
Couple of things ..
What point does objrs serve there?
You never close recordset and connection objects once
finished with them, and at a guess Access can only cope
with 64 concurrent connections .. <insert witticism here>
codestorm

scripter73 · Jun 28, 2001

First off thanks for all of your help.

Here’s some more info on my goal:

I have an application that basically reads text log files, manipulates data, and then writes to an Access database.

Consider (if you will) a file MAINFILE.ASP that calls FILEA.ASP. FILEA.ASP calls either FILEB.ASP or FILEC.ASP depending on user selection.

Now for the connections. I’m a little confused. Since Access can only handle 64 concurrent connections, what does Access “consider” a connection?

?set conn = server.createobject(“ADODB.Connection”)
?set rs = server.createobject(“ADODB.Recordset”)
?conn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & MdbFilePath & ";"

If all of these are considered connections, then I’m very guilty of sprinkling my code with them. However, I have the above connections in my MAINFILE.ASP code, but I do close them with standard rs.close, conn.close, set rs=nothing, and set conn=nothing. So shouldn’t that delete this connection in Access’ mind?

Consider also that in my FILEB.ASP/FILEC.ASP, I open one database connection to insert records into my Table1 and before closing those connections, I open the same database to insert records into a Table2. My reasoning for this was because when I first coded it, I couldn’t write to different tables under the same connection.

Everything you’re saying is making sense. So I guess my question really is, “Do all of these things like server.createobject and conn.Open count as concurrent connections to Access database?” If they do, then I’ll try to cut down my open connections.

About the recordset you mentioned? I don’t need it? Isn’t that what allows me to open my tables. I don’t understand why I don’t need it. Please advise.

In order for you to review what I’m doing, I’ll put the text code here (sorry for length):

<html><head>
<title>String Splitting</title>
<body>

<%
response.write "***************************** CONNECT LOG FILE PROCESSING ***********************" & " "
'***************************************
' LOG FILE VARIABLE DECLARATION
'***************************************
dim LogFile, LogFileObj

'LogFile = "d:\inetpub\

http://wwwroot\logs\"

& Session("ufilename&quot

LogFile = "d:\inetpub\

http://wwwroot\logs\logfiles\"

& Session("ufilename&quot

'LogFile = Session("ufilename&quot

filechars = UCase(Left(Session("ufilename&quot

,2)) 'holds the characters that define the log filename

Set LogFileObj = Server.CreateObject("Scripting.FileSystemObject&quot

'STRING DECLARATION

dim strSave 'string used to hold the single-quoted log-line
dim theString

'ARRAY DECLARATION

dim theParts, theParts2, theParts3
dim svrParts

'HOLDING VARIABLES
dim strRequest
dim strHost, strIdentd, strUser, strTime, strRealTime, strResult, strBytes
dim strFound
dim numberChar, firstfour
dim strRefURL, strOpSys

'DATABASE VARAIBLES
dim firstfilewrite

'=============FUNCTION START

Function StrQuoteReplace(strValue)
'Replace any double quote in strValue with a single quote.
'The second argument to Replace consists of
'two double quotes enclosed in a pair of double quotes.
'The third argument to Replace consists of
'a single quote enclosed in a pair of double quotes.
'StrQuoteReplace = Replace(strValue, "'", "''&quot

' single in a double, and two singles in a double
StrQuoteReplace = Replace(strValue, """", "'&quot

End Function

Function fncGetDayOrdinal( _
byVal intDay _
)
' Accepts a day of the month as an integer and returns the
' appropriate suffix

Dim strOrd

Select Case intDay
Case 1, 21, 31
strOrd = "st"
Case 2, 22
strOrd = "nd"
Case 3, 23
strOrd = "rd"
Case Else
strOrd = "th"
End Select

fncGetDayOrdinal = strOrd
End Function ' fncGetDayOrdinal

Function fncFmtDate( _
byVal strDate, _
byRef strFormat _
)
' Accepts strDate as a valid date/time,
' strFormat as the output template.
' The function finds each item in the
' template and replaces it with the
' relevant information extracted from strDate

' Template items (example)
' %m Month as a decimal (02)
' %B Full month name (February)
' %b Abbreviated month name (Feb )
' %d Day of the month (23)
' %O Ordinal of day of month (eg st or rd or nd)
' %j Day of the year (54)
' %Y Year with century (1998)
' %y Year without century (98)
' %w Weekday as integer (0 is Sunday)
' %a Abbreviated day name (Fri)
' %A Weekday Name (Friday)
' %H Hour in 24 hour format (24)
' %h Hour in 12 hour format (12)
' %N Minute as an integer (01)
' %n Minute as optional if minute <> 0
' %S Second as an integer (55)
' %P AM/PM Indicator (PM)

On Error Resume Next

Dim intPosItem
Dim int12HourPart
Dim str24HourPart
Dim strMinutePart
Dim strSecondPart
Dim strAMPM

' Insert Month Numbers
strFormat = Replace(strFormat, "%m", _
DatePart("m", strDate), 1, -1, vbBinaryCompare)

' Insert non-Abbreviated Month Names
strFormat = Replace(strFormat, "%B", _
MonthName(DatePart("m", strDate), _
False), 1, -1, vbBinaryCompare)

' Insert Abbreviated Month Names
strFormat = Replace(strFormat, "%b", _
MonthName(DatePart("m", strDate), _
True), 1, -1, vbBinaryCompare)

' Insert Day Of Month
strFormat = Replace(strFormat, "%d", _
DatePart("d",strDate), 1, _
-1, vbBinaryCompare)

' Insert Day of Month Ordinal (eg st, th, or rd)
strFormat = Replace(strFormat, "%O", _
fncGetDayOrdinal(Day(strDate)), _
1, -1, vbBinaryCompare)

' Insert Day of Year
strFormat = Replace(strFormat, "%j", _
DatePart("y",strDate), 1, _
-1, vbBinaryCompare)

' Insert Long Year (4 digit)
strFormat = Replace(strFormat, "%Y", _
DatePart("yyyy",strDate), 1, _
-1, vbBinaryCompare)

' Insert Short Year (2 digit)
strFormat = Replace(strFormat, "%y", _
Right(DatePart("yyyy",strDate),2), _
1, -1, vbBinaryCompare)

' Insert Weekday as Integer (eg 0 = Sunday)
strFormat = Replace(strFormat, "%w", _
DatePart("w",strDate,1), 1, _
-1, vbBinaryCompare)

' Insert Abbreviated Weekday Name (eg Sun)
strFormat = Replace(strFormat, "%a", _
WeekDayName(DatePart("w",strDate,1),True), 1, _
-1, vbBinaryCompare)

' Insert non-Abbreviated Weekday Name
strFormat = Replace(strFormat, "%A", _
WeekDayName(DatePart("w",strDate,1),False), 1, _
-1, vbBinaryCompare)

' Insert Hour in 24hr format
str24HourPart = DatePart("h",strDate)
If Len(str24HourPart) < 2 then str24HourPart = "0" & _
str24HourPart
strFormat = Replace(strFormat, "%H", str24HourPart, 1, _
-1, vbBinaryCompare)

' Insert Hour in 12hr format
int12HourPart = DatePart("h",strDate) Mod 12
If int12HourPart = 0 then int12HourPart = 12
strFormat = Replace(strFormat, "%h", int12HourPart, 1, _
-1, vbBinaryCompare)

' Insert Minutes
strMinutePart = DatePart("n",strDate)
If Len(strMinutePart) < 2 then _
strMinutePart = "0" & strMinutePart
strFormat = Replace(strFormat, "%N", strMinutePart, _
1, -1, vbBinaryCompare)

' Insert Optional Minutes
If CInt(strMinutePart) = 0 then
strFormat = Replace(strFormat, "%n", "", 1, _
-1, vbBinaryCompare)
Else
If CInt(strMinutePart) < 10 then _
strMinutePart = "0" & strMinutePart
strMinutePart = ":" & strMinutePart
strFormat = Replace(strFormat, "%n", strMinutePart, _
1, -1, vbBinaryCompare)
End if

' Insert Seconds
strSecondPart = DatePart("s",strDate)
If Len(strSecondPart) < 2 then _
strSecondPart = "0" & strSecondPart
strFormat = Replace(strFormat, "%S", strSecondPart, 1, _
-1, vbBinaryCompare)

' Insert AM/PM indicator
If DatePart("h",strDate) >= 12 then
strAMPM = "PM"
Else
strAMPM = "AM"
End If

strFormat = Replace(strFormat, "%P", strAMPM, 1, _
-1, vbBinaryCompare)

fncFmtDate = strFormat

'If there is an error output its value
If err.Number <> 0 then
Response.Clear
Response.Write "ERROR " & err.Number & _
": fmcFmtDate - " & err.Description
Response.Flush
Response.End
End if
End Function ' fncFmtDate

'=============FUNCTION END

'**************************
'READ THE LOG FILE
'**************************
'show the filenames being used
response.write " Log Filename = " & LogFile & " "

Set LogFileObj = Server.CreateObject("Scripting.FileSystemObject&quot

response.write "<hr>"
response.write " " & "Processing Log File..." & " "
If LogFileObj.FileExists(LogFile) Then
Set LogTextFile = LogFileObj.OpenTextFile(LogFile)

firstfilewrite = 0 'set the firstpass to Filenames table to 0

Do While Not LogTextFile.AtEndOfStream
strSave = LogTextFile.Readline
strSave = strQuoteReplace(strSave)

'split each string into an array (initial split based on quote)
theString=strSave 'copy the current line to a buffer
theParts = split(strSave,"'&quot

if Not LogTextFile.AtEndOfStream then
response.write ""
response.write " Array:" & " "
end if
for i=lbound(theParts) to ubound(theParts)
response.write theParts(i) & " "
next
response.write "----------------------------------------------------------------------" & " "

'search each element in array looking for certain strings
strfound = false
for i=lbound(theParts) to ubound(theParts)
numberchar = left(theParts(i),4)

if (strcomp(numberchar, "GET &quot

= 0) OR (strcomp(numberchar, "POST&quot

) then
'assign that part to the strRequest variable
if i<>ubound(theParts) then
strRequest = theParts(i+1)
end if
strFound = true

'now that strRequest was located, replace its section in array with a flag
if i<>ubound(theParts) then
theParts(i+1) = "null"
end if
response.write " "& "Found either a GET or POST in the array." & " "
response.write " Updating Array..."
exit for 'after finding string, exit forloop
end if

next
response.write " "

'show me theParts array
response.write " " & "Array - theParts" & " "
for k=lbound(theParts) to ubound(theParts)
response.write theParts(k) & " "
next

response.write "----------------------------------------------------------------------" & " "

'copy new array to another string
theString2 = ""
for iloop=lbound(theParts) to ubound(theParts)
if (theParts(iloop)="null&quot

=0 then
theString2 = theString2 & theParts(iloop)
end if 'build string with only non-null elements of array
next
'write out new string
response.write " Copying array to a new string..."

response.write " " & "New String = " & theString2 & " "

response.write " Splitting String into another array... "
response.write "theParts2 = "

'split the string again into an array (based on spaces)
theParts2 = split(theString2, " &quot

for l=lbound(theParts2) to ubound(theParts2)
response.write theParts2(l) & " "
next

response.write "----------------------------------------------------------------------" & " "

response.write " "

'Resize new array
'previously defined dim x,y
y=-1
redim theParts2(ubound(theParts))
for x=0 to ubound(theParts)
if theParts(x) <> "null" then
y=y+1
theParts2

= theParts(x)
end if
next
redim preserve theParts2

'resize down

response.write "Resizing Array..." & " "
response.write "The New theParts2 "

for z=lbound(theParts2) to ubound(theParts2)
response.write theParts2(z) & " "
next

response.write "----------------------------------------------------------------------" & " "
response.write " "

response.write "Copy theParts2 into another string...arrString"
strDelimiter="*"
'join array items with string delimiter
arrString = Join(theParts2, strDelimiter)
response.write " arrString (After Delimiting)" & " " & arrString & " "

response.write "----------------------------------------------------------------------" & " "
response.write "Copy string into third array: theParts3 "
'split apart one more time
theParts3 = split(arrString, " &quot

for n=lbound(theParts3) to ubound(theParts3)
response.write theParts3

& " "
next
response.write "----------------------------------------------------------------------" & " "
'*************************
'variable assignment
'*************************
for a=lbound(theParts3) to ubound(theParts3)
if instr(theParts3(a),".&quot

<>0 then
'response.write "You have a . in one of your strings!" & " "
strHost = theParts3(a)

else if instr(theParts3(a),"[&quot

<>0 then
'response.write "You have a [ in one of your strings!" & " "
strTime = theParts3(a)
end if

end if

'strResult and strBytes aren't required in report, so this is extra information
if (a=(ubound(theParts3)-1))<>0 then
'response.write "You've find the next to the last entry in array." & " "
strResult = theParts3(a)

else if (a=(ubound(theParts3)))<>0 then
'response.write "You've find the last entry in array." & " "
strBytes = theParts3(a)
end if

end if

next

'Assign automatic values to these variables because this is a CONNECT log file.
strUser = "- "
strRefURL = " "
strOpSys = " "

'***************************
'staus reporting
'***************************
response.write " " & "Status"
response.write " " & "Host(strHost) = " & strHost
response.write " " & "User (strUser) = " & strUser
strTime = Right(strTime, Len(strTime)-1)
response.write " " & "Date/Time (strTime)= " & strTime
response.write " " & "String Request(strRequest) = " & strRequest
response.write " " & "Referring URL (strRefURL) = " & strRefURL
response.write " " & "OS (strOpSys) = " & strOpSys
response.write " "

'***************************
'string manipulations
'format data for database
'***************************
response.write " Performing Date Time Manipulations... "
strTime=replace(strTime,":"," ",1,1)
response.write " " & "New Time is " & strTime

strPreFinalTime = fncFmtDate(strTime,"%m/%d/%y*%h:%N %P&quot

response.write " " & "Prefinal Time " & strPreFinalTime

strTimeArray = split(strPreFinalTime, "*&quot

strFinalDate = strTimeArray(0)
strFinalTime = strTimeArray(1)
response.write " " & "Final Date " & strFinalDate
response.write " " & "Final Time " & strFinalTime
datDate = CDate(strFinalDate)

response.write " ----------------------------------------------------------------------" & " "
response.write " FinalItems to Write to Database (Vartypes) "
response.write " " & "Host = " & strHost & " (" & vartype(strHost) & &quot

"
response.write " " & "User_ID = " & strUser & " (" & vartype(strUser) & &quot

"
response.write " " & "Date = " & datDate & " (" & vartype(datDate) & &quot

"
response.write " " & "Time = " & strFinalTime & " (" & vartype(strFinalTime) & &quot

"
response.write " " & "URL = " & strRequest & " (" & vartype(strRequest) & &quot

"
response.write " " & "Referring URL = " & strRefURL & " (" & vartype(strRefURL) & &quot

"
response.write " " & "Op Sys = " & strOpSys & " (" & vartype(strOpSys) & &quot

"

datDate = CDate(strFinalDate)
response.write " "

response.write " Updating Database... "

'********************************************************
'OPEN DATABASE CONNECTION - write data to database
'********************************************************
'create connection to database using dsn

dim adopenforwardonly, adlockreadonly, adcmdtable
adopenforwardonly = 0
adlockreadonly = 1
adcmdtable = 2

dim objconn, objrs
set objconn = server.createobject("ADODB.Connection&quot

set objrs = server.createobject("ADODB.Recordset&quot

'line 461
dim strdatabasetype
strdatabasetype = "Access"

'MCF 6/27/01 adding commands
MdbFilePath = Server.MapPath("TargetURL2.mdb&quot

objconn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & MdbFilePath & ";"

'MCF 6/27/01 - GETTING ERROR (COMMENT OUT) objconn.open "Provider=Microsoft.Jet.OLEDB.4.0;" &_
' "Data Source=d:\inetpub\

http://wwwroot\logs\TargetURL.mdb;"

&_
' "Persist Security Info=False"

objrs.open "Alog", objconn, adopenforwardonly, adlockreadonly, adcmdtable

'add new record
'check to make sure file data to upload is from CONNECT or DOMINO log file

if (strcomp(filechars, "NC&quot

= 0) OR (strcomp(filechars, "AC&quot

=0) then
strSQL = "INSERT INTO Alog (Host_Addr, UserID, LogDate, LogTime, LogRequest, RefURL, OpSysURL) VALUES ('" & _
strHost &"'," & "'" & strUser & "'," & "'" & datDate &"','" & strFinalTime & "','" & strRequest & "'," & "'" & strRefURL & "'," & "'" & strOpSys & "')"

objConn.Execute strSQL

end if

if firstfilewrite=0 then
'***************************************************************
'OPEN 2nd DATABASE CONNECTION - write filename to table
'***************************************************************

'dim adopenforwardonly, adlockreadonly, adcmdtable
adopenforwardonly = 0
adlockreadonly = 1
adcmdtable = 2

dim strUFile
strUFile = Session("ufilename&quot

dim conn, rs
set conn = server.createobject("ADODB.Connection&quot

set rs = server.createobject("ADODB.Recordset&quot

dim dbtype
dbtype = "Access" 'line 500

'MCF 6/27/01 adding commands
MdbFilePath = Server.MapPath("TargetURL.mdb&quot

conn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & MdbFilePath & ";"

'MCF 6/27/01 - GETTING ERROR (COMMENT OUT) conn.open "Provider=Microsoft.Jet.OLEDB.4.0;" &_
' "Data Source=d:\inetpub\

http://wwwroot\logs\TargetURL.mdb;"

&_
' "Persist Security Info=False"

rs.open "Filenames", conn, adopenforwardonly, adlockreadonly, adcmdtable

sqltext = "INSERT INTO Filenames (ExistingFilename) VALUES ('" & strUFile & "')"

Conn.Execute sqltext

rs.close
conn.close
set rs = nothing
set conn = nothing

firstfilewrite=1
end if
response.write "<hr>"

Loop 'do...while

Else response.write "File Does Not Exist." & " "

End IF

response.write "<hr>"

%>

</body>
</html>

Here’s my processing logic:

MAINFILE.ASP calls FILEA.ASP which calls either SPLIT.ASP which populates the Access database.

Thanks for all of your help. I really appreciate this service.

Scripter73

codestorm · Jun 28, 2001

A connection in this case is an ADODB.Connection object.

Recordsets, Commands, etc are associated with a connection
object (they aren't extra connections to the database in
themselves).

A connection object is not connected to a database as such
until it's Open method is called (at which point it's open
until the Close method is called, or it is destroyed).

It's good practice to use the Close method, just in case
something goes screwy with simply destroying it
(=null, or nothing in VBS).

Err, what else..

You don't need a recordset to insert into a database, as
this doesn't need to return anything (same as delete and
update). You only need a recordset object if you want to
get some data from the database.

And yes, you can run multiple SQL queries, affecting
different tables (within the same database) from the one
connection.

(Though I seem to remember reading somewhere of
a problem running more than one stored procedure command
thru it together?)

OK, just looked thru the code ..
You don't close objconn, only conn.
Also you don't need the recordset objects.

Hope that helps. <insert witticism here>
codestorm

lobstah · Jun 29, 2001

I got that same error the other day, after a SELECT statement to get data to update another database table with. So I saved the data in variables, closed the recordset before I did the objConn.Execute and all was well.

example:
objrs.open strSQL, 3, 1
mydata1 = objrs(0)
mydata2 = objrs(1)
objrs.close

strSQL = "UPDATE table1 SET data1 = '" & mydata1 & " WHERE data2 = " & mydata2 & "'"

objConn.Execute strSQL

And also, as codestorm mentions above, you don't need the recordset in the code you listed.....

joanne

scripter73 · Jun 29, 2001

Thanks codestorm/lobstah!

I did what you said codestorm, making sure I closed all of the connections that were opened, and also getting rid of the recordset connection.

Everything's working like a charm!

Thanks again, you're the best.

scripter73

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Access Stops Writing To My Database

scripter73

Programmer

codestorm

Programmer

scripter73

Programmer

codestorm

Programmer

lobstah

Programmer

scripter73

Programmer

Similar threads

Part and Inventory Search

Sponsor