Open Ms Access

[yatie]

Hi,

Is it correct if i say Ms Access Database cannot open 2 times. Normally in SQL connection open 2 times. getData.open for open the table and getdata.source to open the field. For example:

myDSN="driver={Microsoft Access Driver (*.mdb)};DBQ=c:/DataAmbridge.mdb"
set getData=server.createobject("adodb.recordset")
getData.open "Select * from INVENTORY ",myDSN,2,2
'
'
'
'
'
'
'
'
'
'
getData.source = "Select MODEL, STATUS, DATE,VARIAN, PREPARED_BY,CHECKED_BY, " & _
"FROM INVENTORY P WHERE " & SrcString & DateCond & _
"Type ='IPC' AND order by MODEL"
getData.open


but error :

Error Type:
ADODB.Recordset (0x800A0E79)
Operation is not allowed when the object is open.
/EditInventory_List.asp, line 178


please help me all... ;)

[jsawkang]

try this

Set cn = Server.CreateObject("ADODB.Connection")
sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("c:/DataAmbridge.mdb") & ";" & _
"Persist Security Info=False"
cn.Open(sConnection)

SQL=""Select * from INVENTORY "
set rs=cn.execute(SQL)
.
.
.
.
sSQL="Select MODEL, STATUS, DATE,VARIAN, PREPARED_BY,CHECKED_BY, " & _
"FROM INVENTORY P WHERE " & SrcString & DateCond & _
"Type ='IPC' AND order by MODEL"
set rec=cn.execute(sSQL)

[yatie]

i try your code . But out error :

Error Type:
Server.MapPath(), ASP 0172 (0x80004005)
The Path parameter for the MapPath method must be a virtual path. A physical path was used.
/EditInventory_List1.asp, line 16


why?

[galvatron]

never used getdata before but this is what I'm guessing

everytime you're done with the first recordset. like you're done with

myDSN="driver={Microsoft Access Driver (*.mdb)};DBQ=c:/DataAmbridge.mdb"
set getData=server.createobject("adodb.recordset")
getData.open "Select * from INVENTORY ",myDSN,2,2

at the end of the codes try writing

getData.close

before using getData.open again

just guessing.

or you could use a different method for calling recordset.

[yatie]

if i do script to calling date -->

myDSN="driver={Microsoft Access Driver (*.mdb)};DBQ=c:/DataAmbridge.mdb"
set getData=server.createobject("adodb.recordset")
getData.Open "Select * from INVENTORY DATE = '"&trim(session("date_P"))&"' order by NO",myDSN,2,2

error out :

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
/Inventory_C.asp, line 244


please tell me y?

is it have another script to calling date??

[tev]

where ?

[Kodo]

don't use table names or field names with spaces in them. if you do , then you must surround them in [ ] in your statements.

date variables syntax for Access db must look like

#"&datevar&"#

for SQL server it's

'"&datevar&"'

[yatie]

I do this code in form , i want to calling record and i want select Date .


Kodo,
I'm not clear about ur explanation. Can you explain one more time ..details.

Ok. this is the code:
myDSN="driver={Microsoft Access Driver (*.mdb)};DBQ=c:/DataAmbridge.mdb"
set getData=server.createobject("adodb.recordset")
getData.Open "select * from INVENTORY where DATE = '"&trim(session("date_P"))&"' order by NO",myDSN,2,2

but i get error:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
/Inventory_C.asp, line 223


Is it u have other script to calling date. My DataType is datetime.

[Kodo]

yes,
the Jet engine for Access REQUIRES you to use the the following syntax for variables when using dates with your queries.

#"&MyDateVar&"#

so when you insert, update, select etc.. with Access, you MUST use that syntax.