Hello, I'm stuck on what seems to be a somewhat simple problem. I have an access db and have an admin area to allow for edits/deletions for the faq's entries. I was able to add in queries to sort the massive amount of data (3000 entries), but when I try to edit or delete one of them, I get the error message that the Database or object is read-only. I am somewhat new to coding, and if anyone could look over the below code and make any recommendations, it would be greatly appreciated.
FAQ_EDITDELETE.ASP CODE
<!--#include file="../globalvariables/backglobalvariablez.asp"-->
<%
Dim ID, responsewrite
ID = Request.Querystring("ID")
If ID <> "" Then
Set objCon = Server.CreateObject("ADODB.Connection")
objCon.Open connectionstring
Set objRS = Server.CreateObject("ADODB.Recordset")
strQuery = "Delete from Dilemmas where ID = " & ID
objRS.Open strQuery, objCon
responsewrite="<font color = green>FAQ Deleted Successfully</font>"
Set objRS = Nothing
Set objCon = Nothing
end if
%>
<%
Set objCon = Server.CreateObject("ADODB.Connection")
objCon.Open connectionstring
Set objRS = Server.CreateObject("ADODB.Recordset")
strQuery = "SELECT * from Dilemmas order by subject asc"
objRS.Open strQuery, objCon
IF objRS.EOF Then
Response.Write("<tr><td colspan = 3>No Faqs</td></tr>")
Else
Do While not objRS.EOF
x = x + 1
%>
<table border = 0 bgcolor = DDDDDD cellpadding = 7 cellspacing = 0 width = 530>
<tr bgcolor = CCCCCC>
<td align = left><b><%=objRS("subject")%></b></td>
<td align = right><input class = "btn" type = button onclick = "top.location.href='faq_edit.asp?id=<%=objRS("ID")%>'" value = "Edit" class = "btn"> <input class = "btn" type = button onclick = "fndelete('<%=objRS("ID")%>');" value = "Delete" class = "btn"></td>
</tr>
<tr>
<td colspan = 2><b>Question:</b><br><%=replace(objRS("question"),vbcrlf,"<BR>")%></td>
</tr>
<tr>
<td colspan = 2><b>Answer:</b><br><%=replace(objRS("answer"),vbcrlf,"<BR>")%></td>
</tr>
</table>
<br>
<%
objRS.MoveNext
Loop
End If
objRS.Close
Set objRS = Nothing
Set objCon = Nothing
%>
Set objCon = Server.CreateObject("ADODB.Connection")
objCon.Open connectionstring
Set objRSAddComments = Server.CreateObject("ADODB.Recordset")
strQuery = "SELECT * FROM Dilemmas where ID = " & ID & " "
objRSAddComments.CursorType = 2
objRSAddComments.LockType = 3
objRSAddComments.Open strQuery, objCon
objRSAddComments.Fields("subject")=strformat(request.form("subject"))
objRSAddComments.Fields("question")=strformat(request.form("question"))
objRSAddComments.Fields("answer")=strformat(request.form("answer"))
objRSAddComments.Update
objRSAddComments.Close
response.redirect("faq_editdelete.asp")
end if
ID = Request.Querystring("ID")
Set objCon = Server.CreateObject("ADODB.Connection")
objCon.Open connectionstring
Set objRS = Server.CreateObject("ADODB.Recordset")
strQuery = "SELECT * from Dilemmas where ID = " & ID
objRS.Open strQuery, objCon
IF objRS.EOF Then
Else
dim subject, question, answer
subject = objRS("subject")
question = objRS("question")
answer = objRS("answer")
End If
objRS.Close
Set objRS = Nothing
Set objCon = Nothing
%>
I am trying to do an auto time stamp in a data access page without having to go to new record. What I am doing is farming out parts of a record to different departments, and I want to time stamp each time each department performs their query, but I want to be able to display the data for the department, and store the timestamp in the original table.
Everything is stored under the table [Appointments] and different departments enter certain things. For instance, Reception gives the customer a ticket # which they enter into the database based on the query for the customer appointment. On the data-access page, I want them to be able to see their check in time. I am able to do this with an expression, but I can't store it in the field. Here's what I have:
Expr1:Now() (this is in the query)
In the data access page I display Expr1 with no problem. How do I get the field [Reception time stamp] to equal Expr1? When I try to do it in the query, it treats it as another expression and doesn't store it in the field (Reception time stamp = Expr1)
Is there a way to assign a field the value of an expression in queries or data access pages?
In other words, for the field I have named "Reception Time Stamp" is their anyway to plug in the Now() value when I do my query? Not search for Now() but have it show Now() when I use the query in a data access page without having to create new record. I know how to do it when you time stamp it with new record, but how can I do it with a query that pulls up an existing record that more info is being updated on it.
Table Demographics: Account Number (Primary) Name Address SSN DOB
Table Appointments: Appointment Number (Primary Autonumber) Appointment Date Appointment Time Account Number (Relationship tied to Demographics) Reception Ticket # Reception Timestamp Cashier Amount Collected Cashier Timestamp
Relationship is One to Many (One Demographic to Many Appointments)
Everybody will be entering data on a data access page using internet explorer, which makes this more tricky, as it would be easy to do using forms. So here's how it works. Appointments enters customer information, appt date, appt time, and account number.
Reception pulls up by date and name(queries) the appointment, and then enters a ticket number (Like taking a number to be waited upon) After they enter this and write this ticket number, I want an auto-time stamp to be saved under the appointment table for reception time stamp.
Then, when their number gets called, the cashier pulls up the patient by ticket number(queries) and collects money from the customer. After they write the amount they collected, I need an auto-time stamp.
The reason I need to auto time stamp is so I can calculate time difference from the customer walking in the door and going out the door. The main reason this has proven difficult is because I am doing all data entry on data access pages, and everybody will be using internet explorer. Another reason is because almost all info is being saved under appointments, and each department only enters a couple of things towards completing the appointment record.
I'm a bit in over my head. Unfamiliar with Access macros, I need to write a bunch of them for work, and soon. Unixen I can deal with, largely undocumented convoluted Access macros are something else....
My current problem is: I have a table. The first column has a value in it for almost every row. However, there are six other columns after it. I need to write a macro to automatically delete all of the rows that don't have data in the last six columns. Microsoft Help is, as always, of zero use. The FindRecord feature allows me to use expressions to search, but of course, the help fails to tell me what syntax Access uses.
Oh, and it asks me to select a table from the drop-down list. Can I use wildcards here? Is there a way to get it to automatically open the newest table, or will we have to change the macro accordingly each month?
Most tutorials/guides I'm Googling rather brilliantly repeat the same things the help does, AKA, are useless. (Why do they bother writing them if you're not giving new information...?)
I'd ask about the other various access questions I have, but I can pick them up as I go, this is the most pressing question.
I am doing bug and enhancement development for an existing Access Application.
Periodically (after testing) I need to take my changed objects and export them to the production database.
My problems are: 1. Is there any easy way to tell which objects have been changed (is there any flag I can set at the beginning of a development cycle and then check to see which objects have been changed)
2. When exporting these objects they don't replace the existing objects in the Production Database they add a new object with the number "1" appended. If there was a form called "fCustomer Input" I end up with a new form called "fCustomer Input1"
This problem must come up with other development efforts.
I am trying to setup a webpage for people to update access 2000 db using FP2000 and DRW. I do not want update all fields in a record though - a couple of them are static. I have been unable to do this. I am using code lifted from another page and DB that does exactly the same thing for the update. I either get the error data type mismatch, or I should provide default values for all form related fields, or no records get updated. Is there anyone who can point me inthe right direction? Thanks, Tim
I have a form that updates a table. It's got some convaluted logic (I didn't write it I swear ). I am trying to add some VB code so that when the form closes, the table (TableA) opens, all records are selected, and any commas are replaced by periods. The table should then be saved and close.
I know how to open, select records, save, and close, but I can figure out how to get it to do the find and replace. Any takers?
Is it possible to update Access tables through a macro of some sort.
Thing is: I would like to collect a membership list in a table. I will add new members on my database but i would like to update another database to have the same data as me. Especially for this particular table.
I will have a look around (i never used access before) but if there is a tip on where to go and find such a feature would be much appreciated.
Hi, Using MS Access on a Server on the web with main programming in .asp I have done all the hard work to have a userid logon with password and stop anyone getting in. I have now set up a change password facility and that again is working great on checking userid, old password, new and repeat new password. I cannot get the update part working to update the new password. It is in the way I have specified the "call adorecordset.open" as that is the line I get the error as incompatible parameters.
Basic Code is :
set adoconnection = server.createobject("adodb.connection") set adorecordset = server.createobject("adodb.recordset") connectionstring = "Provider=Microsoft.jet.OLEDB.4.0;Data Source=" & server.mappath(databaseid) & "" & databaseid & "user.mdb"
I have searched the forum but have failed to find the answer to my problem. I have a front end ms access 2000 solution that I distribute to user PC's with an MDE back end data base on a server. I now need to release a new version that includes changes to forms, queries and tables. However there is data in the original mde data base I need to retain. Is there an easy method to migrate that data to the new data base. I have changed some relationships but this should not affect data integrity - most change is related to adding new fieldsto existing tables or new tables (no previous data). If I create a new empty mde will I be able to import old data into it from previous mde?:confused:
Hi, im currently working on a database which is for someone else. Whe it is handed over to them, they will no doubt want some changes done every so oftern eg new reports, changes to forms etc.
What is the best way to do this? The database holds a lot of data, so I dont think its feasible for them to send it to me via email everytime they need something changed!
Ive noticed a few posts here about splitting the database into a front/back end. If this was done, would they just have to send me the front end; this would probably be a smaller file ye?
If this is going to be the best way round the problem; how easy is it to split a database which is basically already made?
Hi, im currently working on a database which is for someone else. Whe it is handed over to them, they will no doubt want some changes done every so oftern eg new reports, changes to forms etc.
What is the best way to do this? The database holds a lot of data, so I dont think its feasible for them to send it to me via email everytime they need something changed!
Ive noticed a few posts here about splitting the database into a front/back end. If this was done, would they just have to send me the front end; this would probably be a smaller file ye?
If this is going to be the best way round the problem; how easy is it to split a database which is basically already made?
I am writing a vba procedure to updating some records in another Access database.
rsAccess.Open "SELECT * FROM AI_Table",conAccess, adOpenForwardOnly, adLockPessimistic
rsAccess!OCRExist = "Exist" rsAccess.Update
it has about 3 millions of records in that AI_Table. In the procedure, I perform some calculation and put the result into a TEXT(50) field in the AI_TABLE. As it was updating the records, I could see the size of the Access database file (the one contained AI_Table) grew very quickly, almost 1 MB/sec. I am pretty sure I am not adding that much data. If I stop the procedure and packed the database, it shrunk a lot.
I am just wondering if there is anything wrong with the way I am locking or updating the records.
I have a table for storing details of share prices relating to specific certificate numbers, so only the £ value and the value date changes when we update (done manually at present).
The updates for different companies are done at different times, hence I cannot just delete and import new data, it needs to be an update to a value from an excel sheet (the excell sheet is downloaded from the web provider in question).
I had thought of using "get external data" to create a new or ad to a new table, then an update query to update the main table from the new one, but again cannot seem to get it to work on the specific certificate numbers.
As you can see I have little knowledge on code etc, and have so far only used macros to automate the application we use, can anyone please help???
For my own database which i created in Access i have something called a mutation date. so when i changed something i typed the date of that day. Now i want that to go automaticly. How do i do that??? It would be great if someone could help me with that. Once there's something changed in the record i need to make the date of that record changed. Hope u can Help me SilverBlood
I have a number of Yes/No fields in my database (their default value is False), and I am looking to update these via one of my webpages via INSERT INTO... statement.
What I have is a checkbox on the page, with checked value as true, and then before the values are written to the database, for the unchecked boxes, it converts all empty check box values (ie the ones which have been left unticked) to false, so the INSERT INTO statement will either write true or false for each checkbox, like this... true,true,false,true
The thing is, on executing the code, it add all the other details fine, and hits no errors, but NONE of the checkboxes get updated... these ALL remain unticked in the database, so ignoring any true values which are written to it. Please can someone help, I would be sooo thankful ).
Beginner at ASP VBscript. Hi I'm trying to update a Access database through a form using ASP VBScript. Kind of lost. I've looked at some sites and they all have complex ways of doing it. CAn anyone help with a simple little example. say a table with firstname and second name.
I'm trying to have a linked Excel chart in Access form. What I've done so far is create a chart in Excel and Paste Special>>Linked into Access.
I also have code inside Excel that will update chart data, it works fine.
Then I have code in Access that calls the code in Excel to update the data.
The data gets updated fine and the chart in Excel gets updated but the chart in Access only gets updated if I close and open the form again.
Here is the code that will update the Excel Data
Public Sub Import_VRSS_Graph_Data(strDayType As String, strTimeBand As String, strEntrance As String, Ws As Worksheet) ' Create a connection object. Dim cnPubs As ADODB.Connection Set cnPubs = New ADODB.Connection ' Provide the connection string. Dim strConn As String
I've set up a product database in Access for my small business. One of its functions is to track the prices of competing products online.I'm trying to get Access to automatically update these prices en-mass, yanking the data off the websites in question without me having to manually enter hundreds of prices each week.
I have a unique record for each product instance as it appears online. For example, there are three unique records for a 4oz hand sanitizer I sell (one for each of my competitors that sells it). Each record has the price, quantity, and unique URL on which the product appears.I'm not great with HTML and don't really have any programming expertise aside from some pretty simple VBASIC.
Been wondering how I can update fields in my Access database table using data that lies in an excel spreadsheet. They have a common row ie say account number and other common fields that need to be updated.
I have a form that has combo boxes and text fields (as well as sub forms). There is also a button linked to some code that says'
Private Sub cmdQuote_Click() 'Creates quote date and prints quote Me.QuoteDate = Now() Me.cbAgentID.Requery DoCmd.OpenReport "Quote", acViewPreview, , "BookingID = " & Me.BookingID End Sub
When the button is pressed the QuoteDate field (it is bound) should be be populated, but unfortunately it is not. I have played with refresh and requery but cannot derive a solution.
i am getting stuck while updating the data in the database table using a command button in the MS Access2003 forms. when i click the command button in the form, a message "Run time 2185: you cant refer to a property or method for a control unless the control has a focus". the code is as follows.
rivate Sub Command10_Click() Dim query As String
query = "select RESOURCEINFO from tbl_control where CONTROLNAME='" + Combo4.Text + "'"
If (cn.State <> 1) Then cn.Open "dsn=ABC", "", "" End If rs.Open query, cn, adOpenKeyset, adLockOptimistic RESOURCEINFO.SetFocus rs.Fields(0) = RESOURCEINFO rs.Update
rs.Close Set rs = Nothing cn.Close Set cn = Nothing On Error GoTo Err_Command10_Click
I have to use MS Access as front end with ODBC connection to Oracle 9i
DB.
The application(forms) should be able to update, delete ,insert records into oracle tables(backend).
i have a main form,which has some unique id's and other info about the ids and the subform shows several matching id's for that unique id in main form. the user who uses this application should be able to 1) search for the unique id in the main form such that the subform displays all its matches 2) they should be able to select anyone match and say approve(there can only be one match), then that particular record should be updated in the table.IF I USE A CHECK BOX AND IF THEY CLICK ON ONE RECORD AS MATCH,HOW DO I TAKE THAT RECORD SAY THE ID , NAME ADDRESS AND ALL DETAILS AND UPDATE THE TABLE??? similarly when they select some other record i should give option of deleting other irrelevant matches in the backend table.
the main form and the subform uses the same table as source.updates are to another table, i should also have to put entry into audit table about which record was deleted and which one inserted..
How should i do this?? i am new to MS access .VBA, any help would be highly appreciated!
Access Database 2010 is used to capture progress on accounts. We are able to perform remedies on multiple accounts in the field and would like to update the records in Access all at once (by batch) rather than one-by-one.
I would like to export specific records from Access into Excel, make the updates to the records in Excel, then import the changes back into Access. I am looking for the updated Excel spreadsheet to overwrite the existing data in Access for that particular record.
