MS Access

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

MS Access

Logged in as: Guest

arrSession:exec spGetSession 2,2,2214

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> WSH & Client Side VBScript >> MS Access
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1]

Message

<< Older Topic Newer Topic >>

MS Access - 3/2/2005 2:36:04 AM

zahid_a

Posts: 14
Score: 0
Joined: 2/16/2005
From: Canada
Status: offline

i have a huge database running on MS Access, and i have ASP doing the search job on the records. one problem i came across is whenever the record in MS Access contains any special character (', ., -) the server just throws me out of the program and returns an error.

i got to know that MS Access does not support searching for the special characters, but is there a way to get around it????

any help will be greatly appreciated...

thanks

_____________________________

zahid

Post #: 1

Re: MS Access - 3/2/2005 11:21:38 AM

token

Posts: 1917
Score: 0
Joined: 1/14/2005
From:
Status: offline

First of all, if it is actually *huge*, I would suggest you use SQL; let it be MS SQL, mySQL, IBM DB2, essBase, Informix, you name it.

I have been using special character without any problems (most of the time if I remembered correctly) and there are usually ways to deal with that. If you can post your codes so others can get a chance to look at it, perhaps someone will think of something that might help.

Because the nature of the Database, you might also want to let us know the field type (eg: string, number, date etc) so that it's easier for any of us to go through your code.

(in reply to zahid_a)

Post #: 2

Re: MS Access - 3/3/2005 5:24:01 AM

zahid_a

Posts: 14
Score: 0
Joined: 2/16/2005
From: Canada
Status: offline

well....the database is really huge...close to 4000 records, but due to financials the only viable option for the time being is to proceed with MS Access. i have been testing the database and the error only pops up when searched for " ' " apostrophe.

regarding the field type:

Folder = text
See also = text
File summary = memo
Subcontents = memo

This is the exact error as it appears on the screen:

MSDataShape error '80040e14'

Data shape command text contains a syntax error at or near position 220 in the command. The text near the error is: " folder} AS Test RELATE folder to folder)".

<% OPTION EXPLICIT %>



<HTML>
<HEAD>
<TITLE> TRYING THE SHAPE & APPEND </TITLE>
</HEAD>
<BODY>

<%

Dim strSQL, objRS, objChild, strConnection, strFolder, inti

inti = 0

strFolder = Request.Form("FOLDER")

strConnection = "Provider=MSDataShape;dsn=vertical;database=vertical final.mdb"

strSQL = "SHAPE { SELECT folder, FileSummary, SeeAlso FROM folder WHERE folder LIKE '%" & strFolder & "%' ORDER BY folder}" & _
"APPEND ({ SELECT folder, subcontent FROM subcontents WHERE folder LIKE '%" & strFolder & "%' ORDER BY folder} AS Test RELATE folder to folder)"

Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open strSQL, strConnection

Set objChild = Server.CreateObject("ADODB.Recordset")
objChild.Open "subcontents", strConnection, adOpenStatic, adLockReadOnly, adCmdTable

While Not objRS.EOF
Response.Write "" & "Folder: " & "" & objRS("folder") & " "
Response.Write "" & "File Summary: " & "" & objRS("FileSummary") & " "
Response.Write "" & "See Also: " & "" & objRS("SeeAlso") & " "
Response.Write "" & "Subcontents: " & "" & " "

Set objChild = objRS.Fields("Test").Value

While Not objChild.EOF
Response.Write " " & " " & " " & " " & objChild("subcontent")
Response.Write " "
objChild.MoveNext

Wend
Response.Write " "
Response.Write "<hr>"
inti = inti + 1
objRS.MoveNext
Wend

Response.Write "There are " & inti & " records on this page."

objRS.Close
Set objRS = Nothing

%>

</BODY>
</HTML>

(in reply to zahid_a)

Post #: 3

Re: MS Access - 3/3/2005 5:42:13 AM

drbelden

Posts: 109
Score: 0
Joined: 12/21/2004
From: USA
Status: offline

I wonder if by searching for ' it is thinking you are defining a literal string (which would be empty). Just a guess. Have you tried searching for this useing chr(x)?

(in reply to zahid_a)

Post #: 4

Re: MS Access - 3/3/2005 6:05:52 AM

zahid_a

Posts: 14
Score: 0
Joined: 2/16/2005
From: Canada
Status: offline

i found the solution to the problem... the problem was occuring only with the apostrophe('); i inserted the following line of code:

strFolder = Request.Form("FOLDER")
strFolder = Replace(strFolder, "'", "''")

This would actually let access accept apostrophe as part of the search...
thanks anyways for ur help

(in reply to zahid_a)