Trouble Updating Access Db Through FP

Jan 13, 2005

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

View Replies

Trouble Updating Recordset

Jun 29, 2006

Hello, thanks for reading.

I have a form that is based on dynamic query. There is a "main" form where a user selects search criteria from 2 fields. This then calls another form, where the results are displayed.

I am trying to update the record set at the top of my code in the form. I am getting a prompt to "enter a parameter" when I execute. Can some one help?

Database attached...

Public Function refresh()
' Check for LIKE job id
If Forms!main.job_id > "" Then
varWhere = varWhere & "[JobID] LIKE """ & [Forms]![main]![job_id] & "*"" AND "
End If

' Check for LIKE title and description
If Forms!main.keyword > "" Then
varWhere = varWhere & "[JobTitle] LIKE """ & [Forms]![main]![keyword] & "*"" AND "
End If

' Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere

' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If

BuildFilter = varWhere

' Update the record source
Me.RecordSource = "SELECT * FROM Acacia_Joblist_all_knowledge " & BuildFilter
End Function

View 7 Replies View Related

Trouble Updating Recordset

Jul 7, 2006

View 3 Replies View Related

Trouble Updating Parent Tables With Child Table/form

Jul 6, 2006

Sorry this is so long but I am trying to make it as detailed as possible...

The Setup

My database is similar to the sample "Service Call" db. I have a main table called "tblTT" which has an autonumber primary key. The table also contains several foreign keys such as UserID (not an autonumber) from "tblUser", TechID (also not an autonumber) from "tblTech", etc. I have the relationships setup with "Enforced Referential Integrity" for both updating and deleting records. Each relationship has a RIGHT OUTER JOIN ("Join 3" in access) so that all records from child (tblTT) and only equal from parent (tblUser, tblTech, etc.) will be included. I have a form for nearly every table which serves different purposes but the main function of the DB is to create new Trouble Tickets (TT's), a.k.a. service calls. Therefore the main form used is my "frmTT" form in add mode. The form contains all the fields from my "tblTT" table and contains (directly) no fields from any other table (I guess indirectly it contains fields from all the parent tables...).

The Problem

When I pull up "frmTT" and try to create a new Trouble Ticket for a user that does not yet exist in the table "tblUser" I get the error "You cannot add or change a record bcause a related record is required in table 'tblUser'".

What I want is for my users (the "Techs") to be able to create a new Trouble Ticket without having to worry about populating the "tblUser" table (and other parent tables) first.

My Solution

I was going to (and unless someone can find an answer for me still will) fix this using VB script by setting up a query to check all the parent tables for the values in their corresponding fields in the form. If the query returns no results an "INSERT INTO" statement will run to populate the parents tables so that the form will save itself into the Trouble Ticket table ("tblTT").

I feel that this is a huge work around and not the proper fix. I would prefer to do this the right way both to have a correctly setup DB and for future reference. Can anyone help me with this?

Thanks,
Andy

View 3 Replies View Related

Trouble Opening Access Files

Nov 27, 2006

Hi,

I was wondering if i could get some help here. I think this is to do with the security issue. I usually double-click to open or run an access file (for example student.mdb) that sits on the server with no problem. But this time, nothing happen when i did double-clicked on it from my laptop while the file was being open by someone'else on another computer. I know that it makes sense that we shouldn't open a file that's being used by other users. But my curiosity is that should it not give you a warning like (you know) you're not able to save any changes or things like that. No, i didnot get any of that messages. It's just nothing happen after trying to open it by double-clicking it.

I have set the macro security to low level but that did not help either. Is there any thing that i need to tweak?

Thank you in advance

View 2 Replies View Related

Trouble Opening Access Db With Shortcuts

Aug 21, 2007

Ok this is a weird issue that has been troubling me, On some computers that I have, Access refuses to open databases through a shortcut. The only way to open any databases is to first open access the go to file open.

I've tried creating new shortcuts, as well as mapping a drive and making a shortcut from there...but nothing is working. Is there a setting that access has to preven this? The same database can be opened on different computer without a hitch....

View 1 Replies View Related

Trouble With Export Feature In Access

Sep 6, 2005

I have a form whose fields are calculated based off VBA code, including fuctions, SQL, etc. Because of the nature of my form, I cannot save the calculations to a table before exporting to and excel spreadsheet.

When I use the File>Export feature of Access and save to and Excel format, I am not able to change the format of my numbers in Excel. For example, I have tried to highlight some of my exported numbers, right-click, and change the format to currency in Excel (or even decimal places). In order for excel to change the format, I have to select the cell, place my cursor in the edit window, and hit enter.....then the format will change.....is there any way to avoid this?

Please let me know if this isn't clear, and I'll attempt to explain it better.

View 5 Replies View Related

Access & File List Trouble

Oct 29, 2004

Hi,

I wonder if anyone can help me.

I have a form which has a links to a table of student ID's. I also have a project folder where images and documents are loaded into. These can be jpegs, bitmaps, gifs, office files (all types) etc

The files are named by the user and dropped into the folder. The name relates to the student ID. For example student: 3001 can have multiple files: 3001.jpg, 3001.doc, 3001.ppt etc.

When each record (student ID) is displayed on screen I need a list box (or something) to display the files associated with this student ID. Then, if possible, the user can double click on the file(s) to open them in the computers default program.

I'd be really grateful for any help on this one. It's got me baffled. I'm only a starter to VBA.

Thanx.
Chris Coleman

View 7 Replies View Related

Trouble Importing External Data - Access Limit?

Oct 23, 2006

I'm trying to import about 18 excel spreadsheets into one database in Access. I've been using "get external data" to import the spreadsheets as tables, and the first 8 of them worked fine, but now I cannot import any more excel files. The only error message I'm getting is "An error occurred trying to import file 'C:....xls' The file was not imported." And this error pops up after I have gone through all of the importing steps. Did I exhaust Access's resources? It's not a format issue, and I've restarted, etc. I'm at a loss.

View 2 Replies View Related

Trouble! Replacing Characters In A Large Access Database

Jun 1, 2007

Howdy,

I am trying to clean up one field of a large database. Currently this field has many records that are listed as "XXXX, Inc.". I am trying to find a way to change all of those to "XXXX Inc" in the entire database.

I saw raskews code snippet on changing multiple characters in a string, but I have to admit I have only use VBA in Excel before and having a hard time with the basics.

Does anyone have some code or ideas on how to do this or something similar?
:confused:

Thanks,
Will

View 4 Replies View Related

Having Trouble Thinking Of A Way To Approach This (to Do With Importing Access Data)

Feb 26, 2008

Edit:I just realised i had accidently writted the title as (to do with importing access data) it should read (to do with importing excel data)This is going to be a trick hard to understand question but I will try my best to explain itI have a database set out in the following wayhttp://img524.imageshack.us/img524/1350/databasetableli1.pngThe way it works is; Let's pretend Access Programmers is a company and working on different forums is a different jobSo on one record it would readJames.90| Access Programmers|Tables Forum| Wed=3= Mon=2Then the record below might readJames.90| Access programmers | Forms Forum| mon=5 tue=6So each record is one unique company,Project and CTR which the person has worked for that week meaning if you only work on one forum you would only write one record out each weekNow the data i am receiving is in an excel file where it's set out in a daily basis Where One Day Date|Name|Company|CTR|etcSo if a person works 5 days a week on 2 companies each day that is 10 records when it should only be 2 recordsSo to sum it up. My database is set out weekly and the excel data is set out dailyMy questionWhat would be the best way to convert this data into the database. Changing the database structure around is not an option and i can't change the format we recieve the excel data in. I can change it once i have the file thorough a converter but i can't change the raw source of the dataWhat would be a way to solve this problem because i am completly stummted and am open to any option of converting or anythingThankyou for your time. Also if you have trouble understanding what i mean Please say so and i will upload a copy of the database and a copy of the excel sheet!

View 10 Replies View Related

Having Trouble Woth Type Conversion When Importing From Excel To Access

Feb 16, 2005

Hello,
I have getting type conversion errors for numeric and currency fields when importing an Ecxel spreadsheet into a Table in Access. I have tried changing the data type to text in Excel, and that is what it says I have done when I examine the field formats in question. How can I force this to be text, it keeps reverting,
i.e. the fields in question, to numeric and currency. Can anyone help? Thank you.
Joe

View 4 Replies View Related

Updating Objects In Another Access DB

Sep 25, 2004

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.

Help

Thanks

View 2 Replies View Related

Updating A Table In Access Using VB

Jul 6, 2005

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?

Thanks,

B

View 7 Replies View Related

Updating Access Tables

Apr 16, 2006

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.

Thankyou

View 1 Replies View Related

Updating Access Query HELP!

Mar 27, 2007

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

<%
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
%>

<font class="headertext">FAQ Edit / Delete</font><br><br><br>
<center><%=responsewrite%><P></center>

<%
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
%>



FAQ_EDIT.ASP CODE


<%
Dim ID
ID = Request.Form("ID")

If ID <> "" Then

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
%>

<font class="headertext">Edit an FAQ</font><br><br><br>
<table cellpadding = 10 width = 450 bgcolor = DDDDDD>
<tr valign = top>
<td>
<form name = workform action = "faq_edit.asp" method = "post">

<b>Subject:</b><br>
<input type = text value = "<%=subject%>" name = "subject" size =83 >
</td>
</tr>
<tr>
<td><b>Question:</b><br>
<textarea name = "question" cols = 60 rows = 7><%=question%></textarea>
</td>
</tr>
<tr>
<td><b>Answer:</b><br>
<textarea name = "answer" cols = 60 rows = 7><%=answer%></textarea>
</td>
</tr>
<tr>
<td align = center>
<input type = hidden value = "<%=Request.Querystring("ID")%>" name = "ID">
<input class = "btn" type = button value = "Update" onclick = "if(document.workform.subject.value=='' || document.workform.question.value=='' || document.workform.answer.value==''){alert('Please fill out all fields')}else{document.workform.submit();}">
<input class = "btn" type = reset value = "Clear Form">
</td>
</tr>
</table>
</center>


Thank You!

View 1 Replies View Related

Updating MS Access On Web Server

Dec 20, 2007

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"

call adoconnection.open(connectionstring)
adorecordset.cursorlocation = aduseclient

sqlstring = "SELECT * from [tblOwnerLog] WHERE [UserId] = '"&username&"' AND [Password] = '"&oldpw&"';"

call adorecordset.open(sqlstring, adoconnection,adopenkeyset,adlockoptimistic)

I have tried many ways, by altering my SQLString etc, but seem to get all tied up.

I want to update adorecordset("password") with the new password.

Any help would be much appreciated.

Thanks

Jim

View 4 Replies View Related

Updating Existing MS Access Solution

Mar 7, 2006

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:

View 2 Replies View Related

Advice On Updating Someone Else's Access Database!

Mar 15, 2007

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?

Thank!!!

View 3 Replies View Related

Advice On Updating Someone Else's Access Database!

Mar 15, 2007

View 3 Replies View Related

Access File Became Very Large During Updating

Mar 16, 2007

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.

Thanks,
pggsB

View 2 Replies View Related

Updating Access Tables From Excel

Mar 26, 2008

Hi, can someone please help with this?

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???

Thanks in advance!

Steve

View 2 Replies View Related

Noob In Access Needs Help With Date Updating

Aug 4, 2005

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

What doesn't kill u makes u stronger

View 6 Replies View Related

Updating Yes/No Fields In MS Access Database

Feb 9, 2005

Hi there,

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 ).

View 1 Replies View Related

Updating Access Database Table

Feb 21, 2005

Hi

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.

Any help would be great!

View 1 Replies View Related

Forms :: Excel Graph In Access Not Updating

May 7, 2014

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

[Code] ....

View 2 Replies View Related