Access Database – Editing records

Topic

New Reply

I have created an Access 2000 database which has been imported into a FP2000 web, and has several “database results” pages accessing it throughout the web.

I would like to be able to design a form (or forms) to be able to select one of the records in the database and edit it (or delete it) and then save the record back to the database.

I am guessing there is a way to do this in ASP, but have no clue how to start. Every sample I see has database connection strings that I can’t make work.

I am hosting this website with a provider, so if there is anything I need to tell them to make this happen, please let me know this too.

Thanks in advance!

Todd

Reply | Quote

Replies

As with most questions there is no one right answer but let me take a stab at helping.

First you need to know if the service (ISP) supports ACTIVE SERVER PAGES (ASP). If it is an NT/IIS server there is a good possibility. Some Unix boxed support ASP but not many. Support for FrontPage server extensions by itself is not a guarantee of support for ASP.

Once that is out of the way and you have verified that ASP is available the simplest way for someone to create a series of forms like you described would be to get a copy of GENERIC DB from http://www.learnasp.com/links/genericdb.asp
Your post doesn’t indicate if more than one table is involved so additional help is difficult. The ASP script contained in GenericDB is fully functional and can be a great learning tool, but not for a novice ASP programmer. I would suggest a Beginning ASP book published by WROX to help get you started if GenericDB doesn’t do the trick (sorry woody but I dont know of a book of yours that covers ASP ).

The connection string you mentioned is paramount to getting Access to provide data to any form or for that matter probably your results page so the connection may be defined and working in those pages somewhere.

Hope this helps

Reply | Quote

The ISP does support ASP, and FP Server Extension. As I mentioned in my post, I am using the database results feature of FP2000 to show data from the database, but I would like for someone else to be able to edit the data online without having to open up the database through Front Page.

I have tried the Generic DB code, but can’t make the database connection using it. My database does have multiple tables, but I don’t need to connect to multiple tables on a single page.

Reply | Quote

Here is a sample connect string that works:

strConnect = “Provider=Microsoft.Jet.OLEDB.4.0;” & _
“Data Source=e:intranet_rootwwwroottestfpdbmydb.mdb;” & _
“Jet OLEDB:System Database=d:xxxyyyzzzmdbsysdb.mdw;” & _
“User ID=joeblow;Password=”

The first line is obvious. The second line is the path on the server. I think it would work with a relative path but actually I haven’t tested it. The third line is needed to actually get into this particular database. The privileges in this case are very messy; luckily it is on the intranet and I can manually futz with it. The fourth line also is self-explanatory.

I then open the database with this:

Set objConn = Server.CreateObject(“ADODB.Connection”)
objConn.Open strConnect

So far, nothing exciting, right? I can run a stored query as follows:

Set objRS = Server.CreateObject(“ADODB.Recordset”)
objRS.Open “zQuery”, objConn, adOpenForwardOnly, adLockReadOnly, adCmdStoredProc

And use ASP methods to throw together a table. This is basically the work that the FPSE do for you, but if you can do this with ASP and ADO, that’s progress from where you are.

Now, I have not written code to edit the database yet. Just have not gotten around to it. But there is an example in Chapter 12 of Osborne’s FrontPage 2000 Developer’s Guide that makes it look fairly simple. You have 3 pages:

(1) Standard FPSE-powered query page. Create a link that passes a unique value to an editing page.

(2) An editing page which allows the user to input revised information. The form action is an update page.

(3) A very brief page that writes the data entered on the editing page to the database.

Time (and copyright) does not permit me to input the example here, but it looks fairly manageable. Hope this helps.

Reply | Quote

Using the information in jscher2000 post with GenericDB you need to change the line in YOUR asp file that starts with:

Session(“dbconn”)= Provider=Microsoft.Jet.OLEDB.4.0; Data Source=e:intranet_rootwwwroottestfpdbmydb.mdb”

In my source that is all on one line! GenericDB works great but has all the limitations of using Access on a web site because of the jet engine. I believe lines three and four in his connection string are only required if Access security is turned on in the database. Otherwise the default is used.

The key is the full path on the ISP’s server for the Data Source. If they won’t provide it for some reason the path can be retrieved using the server object I think. If turns out to be a problem post back and I will look it up.

You will also have to set the right permissions on the directory where the database resides so I would recommend that is be in its own directory. It will need read write and DELETE because a yourfilename.LDB file will get created and in certain circumstances that I haven’t nailed down yet, if this file isn’t deleted errors that say the file is locked by another user occur and the only way to cure it is to stop and start IIS, something ISPs really hate.

More than you wanted to know I am sure, hope it helps

Reply | Quote