Help with access DB coding

[austenr]

Hi,

First of all, this is NOT a homework assignment its a work assignment. I am having trouble either connecting to the DB or something. The code is supposed to search an access DB called ETG and the table called Application. I have the form part worked out but when I enter a search term and press Submit, I get page cannot be displayed? Any help getting past this is appreciated.

[Tommy]

Hi,

I'm not familiar with this statement:
strDBPath = Server.MapPath("database.mdb")

Will it return the "\" at the end of the path and does it look like ex "C:\Directory\database.mdb" ?

[austenr]

The file path to the DB is C:\wamp\www\etg.mdb

[austenr]

Using apache as a test server

[Tommy]

And what does the variable strDBPath contain when it runs?
Your code says <strDBPath = Server.MapPath("database.mdb")>
which would be (using your example) "C:\wamp\www\database.mdb"

So I would start with changing
strDBPath = Server.MapPath("database.mdb")
to
strDBPath = Server.MapPath("etg.mdb")
:yes

[austenr]

Hmmm...didnt work

[Tommy]

And what does the variable strDBPath contain when it runs?

I would also change the SQL to :
strSQL = "SELECT Application.Title, Application.Type " _
& "FROM Application " _
& "WHERE Application.Title LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "OR Application.Type LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "ORDER BY Application.Title;"
Type may be a reserved word not sure in HTML

[austenr]

OK got another question. The project has changed some. What the customer wants is to have a search box, type in any word or part of a word or part of a phrase. Search the table. Return the title field and part of the description.

So if you are searching a table that has Two fields, item and description. Your search term is say shirt. If the word shirt is found in either the item or description in a record, first display the full item description and then underneath it, the full description of the item.

Would that be easier than what is being attempted with the current code?

[Tommy]

I think the % is for Dates?:dunno

The code seems to be fine it may be the SQL that is incorrect, but I don't have a database setup, or Apache running. I cannot confirm that the database string is concatenated correctly, I also cannot determine if an error has occured or if the database has a problem.:help

I also have no idea if the database requires a user name and/or a password.:bug:

This is what I would do on the SQL::whistle:

strSearch = Replace(strSearch, "'", "''")
strSQL = "SELECT item, description " _
& "FROM TableName" _
& "WHERE item LIKE '*" & strSearch & *' " _
& "OR description LIKE '*" & strSearch & "*' " _
& "ORDER BY item;"

This should generate a SQL string that looks like this::whip

strSQL = "SELECT item, description " _
& "FROM TableName " _
& "WHERE item LIKE '*shirt*' " _
& "OR description LIKE '*shirt*' " _
& "ORDER BY item;"

If this doesn't work I wouldn't think you are connected to the database.: pray2:

[stanl]

Hi,

I'm not familiar with this statement:
strDBPath = Server.MapPath("database.mdb")

Will it return the "\" at the end of the path and does it look like ex "C:\Directory\database.mdb" ?

It is ASP, so if you are running locally you would need IIS extensions installed. I think the SQL was originally designed to overcome syntax errors, if for example one were to input- 'Lil' Bush - as a search string. Also, is it ETG.mdb or ETG.accdb?

Stan

[austenr]

The DB is called ETG.mdb. Question though. Is access a better choice than say mySQL if you are running this on a server. IOW, which is the better choice? Was thinking about using embedded PHP but not really sure.

[stanl]

The DB is called ETG.mdb. Question though. Is access a better choice than say mySQL if you are running this on a server. IOW, which is the better choice? Was thinking about using embedded PHP but not really sure.

works fine as ASP... try

http://www.fundamentalsolution.com/Downloads/test.asp

and enter beer as a search

This is on a friends site, so I can't leave it there for long. Originally you sent me an .accdb file, I converted it back to .mdb and ran the page. Interesting, if tried with .accdb, the ACE.12 Provider is needed and the server sends back an error that it is not installed... that would be an issue if they use Access 2007 format

Stan

[stanl]

P.S. -

change the url link to test1.asp, which used the .accdb file and see what I mean. Stan

[Tommy]

ASP I should have know. Thanks Stan at least now I know I haven't completely lost it. I have IIS installed and running.

I got the "txt" file and changed the extension to html, cut and pasted in DreamWeaver (which is where I saw ASP for the first time).... setup a test database in access(2000 and 2003)....
So the database is actually .accdb? By what Stan is saying is that the new ext for 2007?
So that means strDBPath = Server.MapPath("etg.mdb") would not get filled correctly?

FWIW in this case I would use php, postgreSQL, Apache on Linux, but that is just because that is what I am familiar with.

Since all I can seem to do is ask questions I'll bow out of this one. :)

[stanl]

So the database is actually .accdb? By what Stan is saying is that the new ext for 2007?


The etg can be either, I think it started as a 2003 .mdb. As you can see from the two links I posted, I used .asp as an extension.

Maybe this deserves a separate thread, but I am curious about the error message when test1.asp is run - the friend's site I placed the test on is hosted by Go-Daddy, and the Ace.12 Provider is installed on the local PC -

So the general question is: if you upgrade from .mdb to .accdb and modify all .asp (or other) pages to reflect this, what needs to be done in order to find the Provider. I'll probably post this on Experts-Exchange.

I think the OP is working for people who would prefer an asp solution, and PHP may be difficult to integrate. Haven't messed with PHP that much, but hey :dunno this forum runs fine under it. My experience with MySQL is 'pseudo-crap', so your suggestion of PostGRE makes sense.

.02 Stan

P.S. I originally ran the code under XP Home, which doesn't support IIS, although there is a hack.

[Tommy]

I'll probably post this on Experts-Exchange

Being a nosey type person would you post the link for EE? I'm dragontooth there so if I poke my ugly head in....

P.S. I originally ran the code under XP Home, which doesn't support IIS, although there is a hack.
:) may I have a link on this also? I like really need this one, I messed up on my home development PC and put XP home on it and really need IIS for some of the things in VS 2005. I have another PC with PRO but it's not a development PC.:doh: No horse power

[stanl]

http://www.webthang.co.uk/Tuts/tuts_server/iis_xph/pippo_xp.asp

tell me what you think.

P.S. Isn't it strange that VSTO is all about Excel,Word and Powerpoint - Nothing about Access

[Tommy]

P.S. Isn't it strange that VSTO is all about Excel,Word and Powerpoint - Nothing about Access

LOL and what happened to FoxPro?:devil2: Do you think the VSTO for powerpoint is better? From what I see the VBA is lacking.

I don't have Win2000 I skipped that OS like BOB and ME, LOL I was reading the article on how when the boss walked in and gave me a pro upgrade for home. There goes my excuse for not taking it home with me.:mkay


tell me what you think

I wasn't looking foward to it. Looked like about a day or two's work. Worth it, but now not needed. :clap: I might of known I finally get a workaround and bang "Don't work so hard, here." :banghead: All of that just to access the report features.

Oh PostgreSQL and SQL Server coexist just fine on the same machine. Keeping in mind they are not "in production". FYI

[stanl]

Well DragonTooth.... I did post on EE and got one of those 'I need the points' MoronsExchange reply. But then I did contact GoDaddy and lo and behold, they do not support ACE 12 and can't say if they are going to... saying its a Microsoft thing.

The point [I think] is that Web Hosting services that support ASP normally install MDAC, but ACE 12 is not part of MDAC - so my question was 'What get's installed???

Next thing [Maybe you can help here] what say you have a PHP site and need to search an .accdb file - what would you install? :banghead: Stan

[Tommy]

What I have found out is this. You have to have Access 2007 installed to work with the new file format. ACE.12 Provider. for a connection string see: http://www.mcse.ms/message2326215.html

To work with a .accdb it would need (this is what I am reading) At this point in time it's anything that wants to communicate with Access 2007 new file format :banghead:

Microsoft Windows Server 2003 with SP1
Microsoft Windows SharePoint Services
Access 2007

Now what would I do ? I don't think it matters what language you use just use what you are comfortable with, but I would set up an error trap and try to attach to the database using the ACE.12 (if that is what I am looking for) if it complains Access 2007 is not installed on the server:dunno Other than that there isn't a whole lot you can do.

[stanl]

What I have found out is this. You have to have Access 2007 installed to work with the new file format. ACE.12 Provider. for a connection string see: http://www.mcse.ms/message2326215.html


Since it's an ADO Provider you can pretty much work with .accdb, just not with the Access GUI - here is a simple sub you can pop into Excel to get details about tables in an .accdb


Sub collist()
cMDB = Application.GetOpenFilename(FileFilter:="Access 2007 (*.accdb), *.accdb", _
Title:="Select File", MultiSelect:=False)
If Len(cMDB) < 5 Then Exit Sub
cXML = "C:\temp\test.xml"
If Len(Dir(cXML)) > 0 Then Kill cXML
cConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & cMDB & ";"
Set oConn = CreateObject("ADODB.Connection")
oConn.Open (cConn)
Set oRS = oConn.OpenSchema(20) 'get all tables
oRS.Save cXML, 1 'creates a persisted recordset in XML
oRS.Close
Set oRS = Nothing
oConn.Close
Set oConn = Nothing
End Sub



Now what would I do ? I don't think it matters what language you use just use what you are comfortable with, but I would set up an error trap and try to attach to the database using the ACE.12 (if that is what I am looking for) if it complains Access 2007 is not installed on the server:dunno Other than that there isn't a whole lot you can do.

I was thinking more along the lines of NOT recommending a migration to 2007 from 2003 as a web backend. Stan

EDIT: Added a Underscore Tommy

[Tommy]

what say you have a PHP site and need to search an .accdb file - what would you install?

LOL my php site wouldn't be on a windows OS so I would be SOL.:bug:
I also wouldn't use Access for this.

What I have done and what I would do is built an application in VB that would use ODBC drivers to attach to a database on a Linux server running PostgreSQL which in turn would be accessed by another server running a web server. In this manner the database is seperate for security and less downtime if a crash occurs. The users can access tha database from Windows for the data entry, the WWW can request information from the database via the web server. This also provides a stable enviroment so in 10 years when something finally breaks you get to learn it all over again to fix it. :devil2:

What's so funny is I set up the database in access to get it right and import it to another database.