Error after creating second recordset

[illufox]

I'm working on creating a web application with data from an Access database.
The first project is an inser record form which displays just fine after
creating one recordset. However, as soon as I create a second record set I get
the following error: Error Type: Microsoft OLE DB Provider for ODBC Drivers
(0x80004005) [Microsoft][ODBC Microsoft Access Driver] Could not use
'(unknown)'; file already in use. When I test the recordsets, both display the
data correct. The connection is also testing fine. I've recreated the
connection and the recordsets but I still get the same error after creating a
second recordset. Any help is greatly appreciated. Below is the full code on
that page:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/EventsDB.asp" -->
<%
' *** Edit Operations: declare variables

Dim MM_editAction
Dim MM_abortEdit
Dim MM_editQuery
Dim MM_editCmd

Dim MM_editConnection
Dim MM_editTable
Dim MM_editRedirectUrl
Dim MM_editColumn
Dim MM_recordId

Dim MM_fieldsStr
Dim MM_columnsStr
Dim MM_fields
Dim MM_columns
Dim MM_typeArray
Dim MM_formVal
Dim MM_delim
Dim MM_altVal
Dim MM_emptyVal
Dim MM_i

MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
If (Request.QueryString <> "") Then
MM_editAction = MM_editAction & "?" & Request.QueryString
End If

' boolean to abort record edit
MM_abortEdit = false

' query string to execute
MM_editQuery = ""
%>
<%
' *** Insert Record: set variables

If (CStr(Request("MM_insert")) = "form1") Then

MM_editConnection = MM_EventsDB_STRING
MM_editTable = "Meeting"
MM_editRedirectUrl = "details.asp"
MM_fieldsStr =
"Date_S|value|Date_E|value|EventName|value|Locatio n|value|EventOverview|value|Ob
jectives|value|ProjectLead|value|Involvement|value |Budget|value|Attendees|value|
Venue|value|StaffHotel|value|Status|value|Financal Year|value|Quarter|value|Event
Homepage|value|TypeOfMeeting|value|OnSite|value|Ma rketingLead|value|GerneralMemo
|value|HotelName|value|HotelFamily|value|HotelCont act|value|ContactPHONE|value|C
ontactEMAIL|value|HotelCity|value|HotelComment|val ue|ExpectedRMNTs|value|Actuall
RMNTs|value|ActualRate|value|CateringCharge|value| CostCenter|value|Industry|valu
e|State|value|BoothSize|value|ADDStaff|value"
MM_columnsStr =
"Date_S|',none,NULL|Date_E|',none,NULL|EventName|' ,none,''|Location|',none,''|Ev
entOverview|',none,''|Objectives|',none,''|Project Lead|',none,''|Involvement|',n
one,''|Budget|none,none,NULL|Attendees|none,none,N ULL|Venue|',none,''|StaffHotel
|',none,''|Status|',none,''|FinancalYear|',none,'' |Quarter|none,none,NULL|EventH
omepage|',none,''|TypeOfMeeting|',none,''|OnSite|' ,none,''|MarketingLead|',none,
''|GerneralMemo|',none,''|HotelName|',none,''|Hote lFamily|',none,''|HotelContact
|',none,''|ContactPHONE|',none,''|ContactEMAIL|',n one,''|HotelCity|',none,''|Hot
elComment|',none,''|ExpectedRMNTs|none,none,NULL|A ctuallRMNTs|none,none,NULL|Act
ualRate|none,none,NULL|CateringCharge|none,none,NU LL|CostCenter|',none,''|Indust
ry|',none,''|State|',none,''|BoothSize|',none,''|A DDStaff|',none,''"

' create the MM_fields and MM_columns arrays
MM_fields = Split(MM_fieldsStr, "|")
MM_columns = Split(MM_columnsStr, "|")

' set the form values
For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
MM_fields(MM_i+1) = CStr(Request.Form(MM_fields(MM_i)))
Next

' append the query string to the redirect URL
If (MM_editRedirectUrl <> "" And Request.QueryString <> "") Then
If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And
Request.QueryString <> "") Then
MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
Else
MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
End If
End If

End If
%>
<%
' *** Insert Record: construct a sql insert statement and execute it

Dim MM_tableValues
Dim MM_dbValues

If (CStr(Request("MM_insert")) <> "") Then

' create the sql insert statement
MM_tableValues = ""
MM_dbValues = ""
For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
MM_formVal = MM_fields(MM_i+1)
MM_typeArray = Split(MM_columns(MM_i+1),",")
MM_delim = MM_typeArray(0)
If (MM_delim = "none") Then MM_delim = ""
MM_altVal = MM_typeArray(1)
If (MM_altVal = "none") Then MM_altVal = ""
MM_emptyVal = MM_typeArray(2)
If (MM_emptyVal = "none") Then MM_emptyVal = ""
If (MM_formVal = "") Then
MM_formVal = MM_emptyVal
Else
If (MM_altVal <> "") Then
MM_formVal = MM_altVal
ElseIf (MM_delim = "'") Then ' escape quotes
MM_formVal = "'" & Replace(MM_formVal,"'","''") & "'"
Else
MM_formVal = MM_delim + MM_formVal + MM_delim
End If
End If
If (MM_i <> LBound(MM_fields)) Then
MM_tableValues = MM_tableValues & ","
MM_dbValues = MM_dbValues & ","
End If
MM_tableValues = MM_tableValues & MM_columns(MM_i)
MM_dbValues = MM_dbValues & MM_formVal
Next
MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues & ")
values (" & MM_dbValues & ")"

If (Not MM_abortEdit) Then
' execute the insert
Set MM_editCmd = Server.CreateObject("ADODB.Command")
MM_editCmd.ActiveConnection = MM_editConnection
MM_editCmd.CommandText = MM_editQuery
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close

If (MM_editRedirectUrl <> "") Then
Response.Redirect(MM_editRedirectUrl)
End If
End If

End If
%>
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_EventsDB_STRING
Recordset1.Source = "SELECT * FROM Meeting"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>
<%
Dim Recordset2
Dim Recordset2_numRows

Set Recordset2 = Server.CreateObject("ADODB.Recordset")
Recordset2.ActiveConnection = MM_EventsDB_STRING
Recordset2.Source = "SELECT * FROM Industries ORDER BY Industry ASC"
Recordset2.CursorType = 0
Recordset2.CursorLocation = 2
Recordset2.LockType = 1
Recordset2.Open()

Recordset2_numRows = 0
%>

// HTML for the form goes here...

<%
Recordset1.Close()
Set Recordset1 = Nothing
%>
<%
Recordset2.Close()
Set Recordset2 = Nothing
%>

[illufox]

I remember now that I had the same problem last year but unfortunately that
forum post no longer exists. I have sleepless nights over this. I searched for
a solution everywhere - books, online forums, help function in
DW.....nothing.... Anybody?

[Paul Whitham TMM]

It is a permissions issue on your local machine. There is a tutorial on
[url]www.charon.co.uk[/url] on how to solve it

--
Regards

Paul Whitham
Macromedia Certified Professional for Dreamweaver MX2004
Valleybiz Internet Design
[url]www.valleybiz.net[/url]

Team Macromedia Volunteer for Ultradev/Dreamweaver MX
[url]www.macromedia.com/support/forums/team_macromedia[/url]

"illufox" <webforumsuser@macromedia.com> wrote in message
news:d17q01$rda$1@forums.macromedia.com...

> I'm working on creating a web application with data from an Access

database.

> The first project is an inser record form which displays just fine after
> creating one recordset. However, as soon as I create a second record set I

get

> the following error: Error Type: Microsoft OLE DB Provider for ODBC

Drivers

> (0x80004005) [Microsoft][ODBC Microsoft Access Driver] Could not use
> '(unknown)'; file already in use. When I test the recordsets, both

display the

> data correct. The connection is also testing fine. I've recreated the
> connection and the recordsets but I still get the same error after

creating a

> second recordset. Any help is greatly appreciated. Below is the full

code on

> that page:
>
> <%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
> <!--#include file="Connections/EventsDB.asp" -->
> <%
> ' *** Edit Operations: declare variables
>
> Dim MM_editAction
> Dim MM_abortEdit
> Dim MM_editQuery
> Dim MM_editCmd
>
> Dim MM_editConnection
> Dim MM_editTable
> Dim MM_editRedirectUrl
> Dim MM_editColumn
> Dim MM_recordId
>
> Dim MM_fieldsStr
> Dim MM_columnsStr
> Dim MM_fields
> Dim MM_columns
> Dim MM_typeArray
> Dim MM_formVal
> Dim MM_delim
> Dim MM_altVal
> Dim MM_emptyVal
> Dim MM_i
>
> MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
> If (Request.QueryString <> "") Then
> MM_editAction = MM_editAction & "?" & Request.QueryString
> End If
>
> ' boolean to abort record edit
> MM_abortEdit = false
>
> ' query string to execute
> MM_editQuery = ""
> %>
> <%
> ' *** Insert Record: set variables
>
> If (CStr(Request("MM_insert")) = "form1") Then
>
> MM_editConnection = MM_EventsDB_STRING
> MM_editTable = "Meeting"
> MM_editRedirectUrl = "details.asp"
> MM_fieldsStr =
>

"Date_S|value|Date_E|value|EventName|value|Locatio n|value|EventOverview|valu
e|Ob

>

jectives|value|ProjectLead|value|Involvement|value |Budget|value|Attendees|va
lue|

>

Venue|value|StaffHotel|value|Status|value|Financal Year|value|Quarter|value|E
vent

>

Homepage|value|TypeOfMeeting|value|OnSite|value|Ma rketingLead|value|Gerneral
Memo

>

|value|HotelName|value|HotelFamily|value|HotelCont act|value|ContactPHONE|val
ue|C

>

ontactEMAIL|value|HotelCity|value|HotelComment|val ue|ExpectedRMNTs|value|Act
uall

>

RMNTs|value|ActualRate|value|CateringCharge|value| CostCenter|value|Industry|
valu

> e|State|value|BoothSize|value|ADDStaff|value"
> MM_columnsStr =
>

"Date_S|',none,NULL|Date_E|',none,NULL|EventName|' ,none,''|Location|',none,'
'|Ev

>

entOverview|',none,''|Objectives|',none,''|Project Lead|',none,''|Involvement
|',n

>

one,''|Budget|none,none,NULL|Attendees|none,none,N ULL|Venue|',none,''|StaffH
otel

>

|',none,''|Status|',none,''|FinancalYear|',none,'' |Quarter|none,none,NULL|Ev
entH

>

omepage|',none,''|TypeOfMeeting|',none,''|OnSite|' ,none,''|MarketingLead|',n
one,

>

''|GerneralMemo|',none,''|HotelName|',none,''|Hote lFamily|',none,''|HotelCon
tact

>

|',none,''|ContactPHONE|',none,''|ContactEMAIL|',n one,''|HotelCity|',none,''
|Hot

>

elComment|',none,''|ExpectedRMNTs|none,none,NULL|A ctuallRMNTs|none,none,NULL
|Act

>

ualRate|none,none,NULL|CateringCharge|none,none,NU LL|CostCenter|',none,''|In
dust

> ry|',none,''|State|',none,''|BoothSize|',none,''|A DDStaff|',none,''"
>
> ' create the MM_fields and MM_columns arrays
> MM_fields = Split(MM_fieldsStr, "|")
> MM_columns = Split(MM_columnsStr, "|")
>
> ' set the form values
> For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
> MM_fields(MM_i+1) = CStr(Request.Form(MM_fields(MM_i)))
> Next
>
> ' append the query string to the redirect URL
> If (MM_editRedirectUrl <> "" And Request.QueryString <> "") Then
> If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And
> Request.QueryString <> "") Then
> MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
> Else
> MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
> End If
> End If
>
> End If
> %>
> <%
> ' *** Insert Record: construct a sql insert statement and execute it
>
> Dim MM_tableValues
> Dim MM_dbValues
>
> If (CStr(Request("MM_insert")) <> "") Then
>
> ' create the sql insert statement
> MM_tableValues = ""
> MM_dbValues = ""
> For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
> MM_formVal = MM_fields(MM_i+1)
> MM_typeArray = Split(MM_columns(MM_i+1),",")
> MM_delim = MM_typeArray(0)
> If (MM_delim = "none") Then MM_delim = ""
> MM_altVal = MM_typeArray(1)
> If (MM_altVal = "none") Then MM_altVal = ""
> MM_emptyVal = MM_typeArray(2)
> If (MM_emptyVal = "none") Then MM_emptyVal = ""
> If (MM_formVal = "") Then
> MM_formVal = MM_emptyVal
> Else
> If (MM_altVal <> "") Then
> MM_formVal = MM_altVal
> ElseIf (MM_delim = "'") Then ' escape quotes
> MM_formVal = "'" & Replace(MM_formVal,"'","''") & "'"
> Else
> MM_formVal = MM_delim + MM_formVal + MM_delim
> End If
> End If
> If (MM_i <> LBound(MM_fields)) Then
> MM_tableValues = MM_tableValues & ","
> MM_dbValues = MM_dbValues & ","
> End If
> MM_tableValues = MM_tableValues & MM_columns(MM_i)
> MM_dbValues = MM_dbValues & MM_formVal
> Next
> MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues &

")

> values (" & MM_dbValues & ")"
>
> If (Not MM_abortEdit) Then
> ' execute the insert
> Set MM_editCmd = Server.CreateObject("ADODB.Command")
> MM_editCmd.ActiveConnection = MM_editConnection
> MM_editCmd.CommandText = MM_editQuery
> MM_editCmd.Execute
> MM_editCmd.ActiveConnection.Close
>
> If (MM_editRedirectUrl <> "") Then
> Response.Redirect(MM_editRedirectUrl)
> End If
> End If
>
> End If
> %>
> <%
> Dim Recordset1
> Dim Recordset1_numRows
>
> Set Recordset1 = Server.CreateObject("ADODB.Recordset")
> Recordset1.ActiveConnection = MM_EventsDB_STRING
> Recordset1.Source = "SELECT * FROM Meeting"
> Recordset1.CursorType = 0
> Recordset1.CursorLocation = 2
> Recordset1.LockType = 1
> Recordset1.Open()
>
> Recordset1_numRows = 0
> %>
> <%
> Dim Recordset2
> Dim Recordset2_numRows
>
> Set Recordset2 = Server.CreateObject("ADODB.Recordset")
> Recordset2.ActiveConnection = MM_EventsDB_STRING
> Recordset2.Source = "SELECT * FROM Industries ORDER BY Industry ASC"
> Recordset2.CursorType = 0
> Recordset2.CursorLocation = 2
> Recordset2.LockType = 1
> Recordset2.Open()
>
> Recordset2_numRows = 0
> %>
>
> // HTML for the form goes here...
>
> <%
> Recordset1.Close()
> Set Recordset1 = Nothing
> %>
> <%
> Recordset2.Close()
> Set Recordset2 = Nothing
> %>
>

[illufox]

Thanks, I've read the 'The infamous 80004005 errors' article and checked my
security settings. I have full permissions and the file sharing box is
unchecked. I wonder if it has something to do with the fact that the Access
database has a password protection on it? But why am I able to pull down all
the records and only get an error if I add more than one recordset?