Resolved the date range issue, thanks, now I have a problem with the query results.
I have a query based upon several other queries that have the StaffID and the linking factor. When I combine some of the queries it works fine, but with others, seemingly those that have no valves or data in the tables, the query brings back no results what so ever.
The queries are running a simple count of records so they have, StaffID input, date range and it counts the records for that staff member within the date range. The second query gives a sum of the count of the first query.
But when I try to combine the queries so that I can produce a report for multiple work areas for audit, I hit problems, is there another, easy way for me to do this, maybe creating a report from invididual query results and only combining them for the report itself.
HELP! Can not attach actual db because of sensitive nature of the information contained, but would be willing to create a like db as sample.
I've developed a ASP.NET website which connects to a secured access database (user/pwd combos with an .mdw file) and allows users to enter data into the database. After the "due date" the system is locked and the database is transferred to a local computer via FTP for processing. Using a VB program and MS Access Reports, the data is printed out in a convenient format and distributed.
Recently, the more users that use my system the more frequently I've encountered problems with connecting to the database. I've been told that access databases are not designed to be used in a multiuser environment and that I should use MS SQL or mySQL instead.
My users report occasional errors and the debug information on the error page shows an 'unspecified error' prompted by a OLEDBConnection object's open method. Secondly, occasionally in the FTP site I see .ldb locking files with 10-20 locks on the database and the .mdw file held by the web application. I'm using a utility class that connects to the db in the constructor and closes the connection in its finalize method.
Code:Protected oConnection as OleDbConnectionProtected Sub Initialize() sConnString as String sConnString = "Provider=Microsoft.Jet.OLEDB.4.0....the rest of the connection string with data source and passwords oConnection = New OleDbConnection(sConnString) oConnection.Open()End SubProtected Overrides Sub Finalize()oConnection.Dispose()End Sub
First, is there something wrong with this code? Is it not being run at the initialization and finalization of each page load? Secondly, is there any way to avoid db connection errors without moving to ms sql?
I have a simple access database that 26 clerks in my office use to enter their weekly tasks. The database is split into a front end and back end, with the back end stored on a shared drive that we all have access to. We have been testing the database for 3 weeks with 5 clerks and no issues. This week is the first week that all 26 have used it, and immediately we encountered a problem.
I think the problem is happening because one of the tables in the database is linked to an excel spreadsheet. When multiple users try to use a dropbox that is linked to this table simultaneously they get an error message. I saved the excel file as a shared file, but this did not correct the problem.
I made a copy of the database before I split it. I know I can fix this if I import the excel spreadsheet into a copy of the database, link this table to the dropboxes, split it again, and redistribute it.
would like to leave the front ends linked to this spreadsheet, because we update it regularly. If not, we will have to have somebody update that particular table in the database intermittently.
I'm having trouble opening up the tools I am developing in Access for multiple users. I split all my databases so the users will only interface with the "Front End" and I just tried saving one as an ACCDE file. I had the ACCDE file open on my machine and asked another person in the office to try opening it (all our files are stored on a network drive, which is already identified as a trusted source).
She got the following error message:
"The database cannot be opened because the VBA project contained in it cannot be read. The database can be opened only if the VBA project is first deleted. Deleting the VBA project removes all code from modules, forms, and reports. You should back up your database before attempting to open the database and delete the VBA project"
How do I fix this? I don't want to publish several Front End Access files since each person in my office will ask me to customize their file and my supervisor wants everyone working off the same file. I thought the point of Access was that multiple users could open the same file and make data modifications. How do I achieve this while retaining all the fun, creative VBA solutions I have developed?
I'm scanning bar codes into the record search box, most of the time it works as it should but occasionally I get multiple "cannot open any more databases" errors. It still finds the product record but the error messages are super annoying. I am sure to clear the memory on the scanner each time before I scan in a new barcode but it doesn't seem to matter. Also sure to only have one or 2 forms open at a time, that doesn't seem to matter either. Most of the time I don't get the errors but when I have to click through like 10+ error messages (all identical). I only have one database open so I'm not sure what it even means. Also I have tried this in 2 different forms and it's the same for both forms. Seems like I get the errors in my initial scan and then after clearing them it seems to work without errors.
Custom filter I'm trying to set up. I have a data full of records with multiple columns and a form linked up to it. The form has a search box that works as well as navigation buttons.
Currently I am trying to get a filter to work. The filter will use up to five combo foxes to narrow down the fields, then return the filtered records after clicking a button. The button itself is where my code is. My issue now though is I cannot get the filter to work if I try to use more than one combo box.
The code I have at the moment is as follows: '------------------------------------------------------------ ' cmdApplyFilter_Click ' '------------------------------------------------------------ Private Sub cmdApplyFilter_Click()
End SubThe current error I am getting is Error 13: Type mismatch. I also know that the And might be the cause but I cannot change it to AND: it keeps changing back.
I've got a number of different append and delete queries running on command on one of my forms, in which it makes a copy of all of the data on that form and included subforms, copies them into another table, and then deletes all of the data from that record. When this runs, I get an error saying "record deleted" and then another error message. Now it is deleting the records and it is moving them, however when i go onto one of the subforms, all the fields in the subform show "Deleted#" until i go back to the previous record or forward to the next, then they clear, but everytime it brings up the other error message it says "end or Debug" which I don't want it doing.
I am running a report based on a query. When the query returns zero rows, the count function in the report gives an error. Probably a simple solution but I've searched and can't find an answer. What do I do to stop the report when there is no data?
Can someone help me please???:rolleyes: I have to query for the avg balance from customer table, custbal field from a certain state... I've tried everything and cannot seem to get the expression correct. How do I do this???? any help is appricated :D
I have a table that stores the criteria that the query is supposed to pull from so that when a user logs into my database, it reads that the person is part of a sepcific unit and then pulls that specific criteria and places it in a text box on a form. When they click a button on that form, it then opens the form that is connected to the query. The data type for the field I need criteria for is a number, so the criteria that shows up in the text box is this "1 or 3 or 5" so that it shows those specific numbers. If i put that directly into the query, it works just fine, but if I try to connect it from the text box in the form, it gives me a "data mismatch error" or an error explaining that criteria is too vast for access to pull the information. How do I make it read the information in there so that I dont have to create 1000 forms for each different unit.
Example:
Field: Unit Table: Master Log Data Criteria: 1 or 2 or 3
Works fine!
Field: Unit Table: Master Log Data Criteria: [Forms]![Selector]![Criteria]
The text box that is referenced in the criteria in the query says 1 or 2 or 3 and then I get an error... If the information is the same, why does it work when I place it in the query specifically, but not when its in the text box?
It works great until I get to a string that looks like this
4110-SPECIAL:OLY:LONG-TRACK:REPAIRS
Which I need to return the word SPECIAL but it gives me #Error in the Query.
It would seem that when there are multiple : it is giving the error but when there is only one it works fine. Is there anyther way to atchieve what I am doing?
I am doing this in an update query to get data from Quickbooks into a table in Access.
I am working on a database that contains patient demographic information. I have a form that prompts the user to enter either the medical record number or part of the patient name. Once you click search it'll then display a 2nd form with a list box outputting the results. Then from there the user can click on one of the entries in the listbox and it'll display the full demographic information on a 3rd form.
Now my problem is on the search part, it completely ignores if I have a medical record number entered. It continues to search by name only.
In my query for med rec # I have
[Forms]![frmSearch]![txtHistn]
where txtHistn is the text box field passing into the query. And for patient name column in the query under OR I have the following:
Like "*" & [forms]![frmSearch]![txtPname] & "*"
So I am confused why it ignores the medical record number entirely.
I have a test database attached. This is just a sample with dummy data entered and not designed pretty. Through this up for another issue I had yesterday that has been resolved but now discovered this query one. The frmSearch is how it begins. If you search by Smith it'll bring up the two Smiths I have entered. If I leave the field blank and enter 1 for the medical record number it treats it as null and displays everything. How can I fix this?
And while on the topic of query, the true database I am working with resides on an Power I series (formerly AS400s) and only linking to their tables. The data is entered in all Caps in the tables. How can I force whatever the user enters into the search screen that it will automatically uppercase the letters before performing the search? Without having the user to remember to enter with their cap locks on.
How does one handle function errors when running a query? Specifically:
(a) I have a user-defined function that lacks any error handling code except for an Exit Function that gets triggered if there is an unacceptable input parameter (e.g. typo in a flag value). (b) This works fine when the function is simply part of VBA code or used in the Immediate window of the VBA editor. However, (c) when the function is used in a SQL Select Query, I get bumped into the de-bugger, and Exit from there does not get me out of the Query. It just gets me out of the current record being processed and then hangs up again on the next.
It seems, therefore, that the function needs to have some instructions that abort the entire query when the function aborts the very first time.
I am writing a small database using MS Access 2007. I have in it a function for running queries and printing reports. Printing works just fine if any one of the multiple query criteria is give.
I also want to trap errors and present a message if the user clicks the "Print" button without a query criteria. At the moment I only get a run-time syntax error message which has a "debug" button for accessing the entire code. I want a message that will say "There was no search criteria".
This is the code for printing:
Code: Private Sub cmdPrint_Click() Dim varWhere As Variant varWhere = BuildFilter ' Update the record source Me.RecordSource = "SELECT * FROM q_Vehicles " & varWhere
When I click a tab (table,queries,reports or module) I receive an error that says (" isn't an index in this table. Look in the indexes collection of the TableDef object to determine the valid index names). Please help.
i have been sick for quite a while,But back on track now.In the past i was building a car event db and would like to finish it.I have checked it out and there seems to be a few errors that i can't remember anything about.
you must have outlook installed otherwise some missing references come up.also there seems to be a problem with the date on the invoice form,the backup section in utilites.
If someone could have a look and point me in the right direction it would be greatly appreciated.
I have database which has worked normally, and just made a couple of amendments
I then go to edit a form (which I had not amended) and get an Access has an fatal error etc.
I reopened it. and repaired compiled etc, but absolutely impossible to get into this form. Access crashes every time I try to do something with it. I can't even delete the form.
(This was A97)
There's no problem, I just got my last back up, and imported the changes I had made from the faulty dbs -
I just wondered if anyone else ever got these issues, and what they did about them.
I've created an Access Database off of an old Fox Pro System. What I need to do is take the data from the tables in the old system and convert them in Access '03. I have roughly 4600 records to transfer.
When I copy the data from the old system and paste it in an Access table, only 300 or so records are pasted. Also, when I check the paste errors, there are less than 100 in there. Where are they going?
Some of the files aren't pasting because there were input errors on some. Others are linked with a department and Acct number and some of those are no longer current, so another reason why some of these records aren't pasting. This is a problem, but should only be around 500-1000 records tops.
I have noticed if I paste less at a time, I get less errors and more are actually copied through, but I don't know exactly how many I should do at a time?
Some questions I have are:
1. How many should I paste at a time?
2. Am I missing something with the paste errors, is there like a setting I can adjust?
3. Is there a better way to convert this old table into a new table?
Any suggestions or comments would be great because I have been stuck on this for a while and I DO NOT want to input these records one by one.
Thank you in advance,
Josh
Here's an email I sent to one of my friends seeing if he had any insight. I might explain my problem a little better here:
Purchasing Department has an old Fox Pro Contract System. I made this same system into Access and it works great. My problem is now converting the tables from the old system into the new system. There are approx. 4200 records. I have tried pasting them all in together and also sections at a time, but everything I do so, I get more or less copied through, and more or less paste errors displaying the ones that hadn’t copied over.
The reason some are not pasting in is because of old user input errors. Another reason is department and account numbers have changed therefore when the copy takes place, if Access doesn’t recognize an Acct number, then it cannot relate it to a department and will not paste in. I’m fine with that though. The problem I’m having is I’m always getting different amounts of errors each time I do this. For example, I did the first 500 records. Had 21 paste errors, all the others pasted fine. I then tried to do less so I copied the first 400 and only received 3 errors, but then I did the next 400 after that and only 200 pasted through and it only displayed 20 or so paste errors. I think there are roughly 400-500 bad records so I’m not understanding why all but those 400-500 paste.
We have an access db that has been working fine for ages and now when we click on a New Record button we are getting the following error:-
Run time error 2105 - you can't go to the specified record. erroring out on the line DoCmd.GoToRecord , , acNewRec. We are not aware of anything changing on the PC that the db is sitting on. I have spent the whole day searching the web but have found nothing helpful.
I have compacted and repaired the db, even went to the form/table security to see if all users can edd/add.
I've created an Access Database off of an old Fox Pro System. What I need to do is take the data from the tables in the old system and convert them in Access '03. I have roughly 4600 records to transfer.
When I copy the data from the old system and paste it in an Access table, only 300 or so records are pasted. Also, when I check the paste errors, there are less than 100 in there. Where are they going?
Some of the files aren't pasting because there were input errors on some. Others are linked with a department and Acct number and some of those are no longer current, so another reason why some of these records aren't pasting. This is a problem, but should only be around 500-1000 records tops.
I have noticed if I paste less at a time, I get less errors and more are actually copied through, but I don't know exactly how many I should do at a time?
Some questions I have are:
1. How many should I paste at a time?
2. Am I missing something with the paste errors, is there like a setting I can adjust?
3. Is there a better way to convert this old table into a new table?
Any suggestions or comments would be great because I have been stuck on this for a while and I DO NOT want to input these records one by one.....
I do a query to remove "." from a list of my servers. This works great, but for every name on the list without a "." I get an error in the output. Is there any way to remove these errors from the output, so I only see the data that has been properly pruned down?
I have an unbound combo box that is sporadically failing with an error message when an entry in its pulldown list is selected. The error message is to the effect: 'The value you entered isn't valid for this field' .
The record source for the combo box is a table/query which is updated by an event triggered by another control on the form. That code also sets the Format property of the combo box to match the native Format of the field from which the combo box is retrieving the data. So data from Yes/No field, a date field, or a currency field, for example, display properly in the revised combo box. When the combo box is populated from a source field that has no special format (the usual case, as with text fields), the code resets the combo box's Format to "".
Again, the combo box has no control source (it is unbound). So, the only culprit for an error message like this that I can think of is this Format property. Yet, when the error occurs, the combo box's Format is properly set to "", which should permit any kind of data, and I try entering any kind of data at all -- all data is rejected with this error message.
this is probably an old one but its catching me out. I'm writing a sports club database in ASP. When users submit their club details in the form to Access, I get errors if it has the characters ' in any of the fields. I've managed to get around " by replacing it with "". However, this doesn't seem to work with '
I'm new to ASP... anyhow the part of my form which deals with this is:
I also do a bit more error checking on the email address, for length, @ etc. This bit seems to work ok. However, Any tips on how to improve the error handling for ' or " before its submitted to access is appreciated.
My Access 2000, created the dummy's way, has been perfectly happy with its query expressions. But when I create a module calling up these queries, I get Compile Error on many perfectly legal expressions such as Left([pfn],1). I've tried repairng the database but to no avail. What is going on, please?
Btw, DBicken, it's when I include your text concantenation code (Posting 6/21/06: "A whole lotta text/stitching") but I don't think it's your fault: it's just exposing it. I can't try your suggestion until I solve this problem.
The problem is that when an admin update the database from a web application that I created in .asp, it gave an error message. The only way to fix this is to restart the IIS services and others that depend on IIS. But this is not practical. Code:Microsoft OLE DB Provider for ODBC Drivers error '80004005'[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query./FoodMenu/CalendarPopUp.asp, line 62 Here is the code for 'CalendarPopUp.asp'. Its quite long. Code:<%@ Language = "VBScript"%><%Option Explicitdim NewEvent 'As Booleandim EventDate 'As Datedim EventStartTime 'As Datedim EventEndTime 'As Datedim EventBLD 'As Stringdim EventDescription 'As Stringdim EventDateString 'As String dim EventID 'As Stringdim Conn 'As ADODB.Connectiondim rstPopUp 'As ADODB.Recordsetdim SQL 'As Stringdim BLD 'As Stringfunction SafeQuote(InString)safequote = replace(InString,"'","`")safequote = replace(InString, vbCrLf, "<br>")safequote = replace(InString, " ", " ")end functionfunction SafeDate(DateValue)if isdate(DateValue) thenSafeDate = "#" & DateValue & "#"elseSafeDate = "NULL"end if end function%><!--#include file="include/incConn.asp"--><%'***** Save Form Data *****if Request.Form("cmdSaveRec") = "Save Event" thenif len(Request.Form("txtDetails")) > 0 thenif Request.Form("NewEvent") = "True" thenSQL = "INSERT INTO tblCalendar ( [Date]"SQL = SQL & ", StartTime"SQL = SQL &", EndTime" SQL = SQL &", BLD"SQL = SQL &", Description ) "SQL = SQL & "VALUES ( "SQL = SQL & SafeDate(Request.Form("txtEventDate")) & ", " SQL = SQL & SafeDate(Request.Form("txtStartTime")) & ", " SQL = SQL & SafeDate(Request.Form("txtEndTime")) & ", " SQL = SQL & "'" & SafeQuote(Request.Form("BLD")) & "', "SQL = SQL & "'" & SafeQuote(Request.Form("txtDetails")) & "' );"'Response.Write sqlConn.Execute SQLelseSQL = "UPDATE tblCalendar SET "SQL = SQL & "[Date] = " & SafeDate(Request.Form("txtEventDate")) & ", "SQL = SQL & "StartTime = " & SafeDate(Request.Form("txtStartTime")) & ", " SQL = SQL & "EndTime = " & SafeDate(Request.Form("txtEndTime")) & ", "SQL = SQL & "BLD = '" & SafeQuote(Request.Form("BLD")) & "', "SQL = SQL & "Description = '" & SafeQuote(Request.Form("txtDetails")) & "' "SQL = SQL & "WHERE ID = " & Request.Form("ID") & ";"'Response.Write sqlConn.Execute SQLend ifelseend ifSet rstPopUp = NothingConn.CloseSet Conn = Nothingend if'***** Process QueryString *****if Request.QueryString("NewEvent") = "True" thenNewEvent = TrueelseNewEvent = Falseend ifEventDate = Cdate(Request.QueryString("Date"))EventDateString = Request.QueryString("Date")if len(Request.QueryString("EventID")) > 0 thenEventID = Request.QueryString("EventID")SQL = "SELECT * FROM tblCalendar WHERE ID = " & EventID & ";"set rstPopUp = Conn.Execute(SQL) if not (rstPopUp.EOF and rstPopUP.BOF) thenrstPopUp.MoveFirstEventDate = rstPopUp("Date")EventDateString = cstr(Month(EventDate)) & "-" & _cstr(Day(EventDate)) & "-" & cstr(Year(EventDate)) EventStartTime = rstPopUp("StartTime")EventEndTime = rstPopUp("EndTime")BLD = rstPopUp("BLD")EventDescription = rstPopUp("Description")elserstPopUp.Close Set rstPopUp = NothingConn.CloseSet Conn = Nothingend ifend if%><html><head><%if NewEvent then%><title>Add New Event</title><%else%><title>Edit Event</title><%end if%></head><body bgcolor=#0066a9 text=black><table border="1" width="481" id="table1"><tr align=center><td align=center width="480"><font color=white>Breakfast 8am-10am Lunch 11am-2pm Dinner 6pm-10pm</font></td></tr></table><%if Request.Form("cmdSaveRec") = "Save Event" then%><script for=window event=onload LANGUAGE="VBScript">close()</script><%End If%><table width=480 height=380 border=0><form action=CalendarPopUp.asp name=frmEvent method=post><input type="hidden" name="ID" value="<%=Request.QueryString("EventID")%>"> <tr> <td align=left bgcolor=#0066a9> </td> <td width=55% bgcolor=silver> <table width=100% height=100% border=1 bordercolordark=Gray bordercolorlight=White> <tr bgcolor=silver bordercolor=Silver> <td align=center valign=middle><input type=hidden name=NewEvent value=<%=NewEvent%>> <table height=100% border=0 cellpadding=1 cellspacing=1> <tr> <td align=center><b>Select a Date:</b><br><input type="text" name=txtEventDate Value="<%=EventDateString%>" size=10> </td></tr><tr><!-- <td align=center> <b>Select a Start and End Time:<br>(i.e. 3:00pm) (i.e. 5:00pm)<br></b> <input type="text" name=txtStartTime value="<%=EventStartTime%>" size=10> / <input type="text" name=txtEndTime value="<%=EventEndTime%>" size=10> </td> --></tr><tr> <td align=center><input type="submit" name=cmdSaveRec value="Save Event" size=25> </td></tr> </table> </td> </tr> </table></td><td align=right> </td> </tr> <tr> <td align=center bgcolor=#0066a9 colspan=3><select size="1" name="BLD"><option selected>choose meal type</option><option>Breakfast</option><option>Lunch</option><option>Dinner</option><%=EventBLD%></select></td> </tr> <tr> <td align=center bgcolor=Silver colspan=3> <table width=100% height=100% border=1 bordercolordark=Gray bordercolorlight=White> <tr bgcolor=silver bordercolor=Silver> <td align=left> Please Enter a Brief Description of the Event:<center><textarea rows=9 cols=53 name=txtDetails><%=Replace(EventDescription,"<br>", vbCrLf)%></textarea></center> </td> </tr> </table></td> </tr></table></form></center></body></html><%set rstPopUp = Nothingset Conn = Nothing%>
So, I look up in this site and follow it. No luck. I even follow theis link Code:http://www.webwizguide.info/asp/faq/server_permissions.asp But it was already configured that way.
Even when I try to add, it gave me a similar error message. Code:Microsoft OLE DB Provider for ODBC Drivers error '80004005'[Microsoft][ODBC Microsoft Access Driver] Could not delete from specified tables./FoodMenu/include/incAdminDel.asp, line 21 Here is the code for 'incAdminDel.asp' file Code:dim TmpString 'As Stringdim Item 'As Stringselect case Request.Form("cmdTopic")'***** Community Calendar *****case "Edit Community Calendar"for each Item in Request.Formif left(Request.Form(Item),6) = "Delete" thenSQL = "DELETE * FROM tblCalendar WHERE ID =" & _mid(Request.Form(Item),7) & ";"Conn.Execute SQLend if
Can anyone tell me what I need to look for?
P.S. This is an Online Food Menu which is inside an 'Employee Directory' that is a web application. When a user open up the 'Employee Directory', it load up the page along with an icon to view 'Online Food Menu'. Kind of like having a web application within a web application. Updating the Employee Directory is not a problem. The issue lies on the admin side where s/he try to update the menu, it give 2 of the above errors that I mention.
Background I'm appending data using an append query. I have a known error that I'm trying to work around. sometimes when trying to append, the field names are slightly different and I get a prompt for a parameter value. ideally i'd like all the field names to match up...
So.... I'm trying to error trap this. what I need is some code that will...
if prompted for a parameter, cancel the parameter and goto query 2 (or error1).
I can't seem to use on error because access isn't treating it as an error...
