Hi,
I have been given an Access database to maintain and it has some performance issues.
I have been looking through this forums for recommendatons regardng size etc but didn't really find anything.
It is all in one file (might consider splitting it..) and it has about 350 forms, 300 database queries, 130 database tables and 200 Macros!! Filesize something around 200 MB.
In one of the forms there is a drop down that when changed refreshes two other dropdowns. I have chyecked the queris used and they are really fast but it still takes at least 6-7 seconds for the 2 dropdowns to reload!
I don't know if it is due to the way it is done, the VB code calls a macro that calls a query.
Personally I wouldn't have done it like that but there has been about 2 years since I did anything complicated with access..
Or is it the size/complexity of it that makes it slow? Does anyone have experince of a similar system?
I was given this project and I'm not sure how to start it. I have a table (Employees) that need to receive salary increases based on their level. Their level is calculated based on their time with the company, but some are base on hours worked and some based on days worked. For example my table looks like this;
Name PSG Hire date Peter 037-30-1 06/01/2005 John 051-21-1 09/15/20005
There are different rules for the PSG, as I said earlier some calculate their level in days and some on hours worked. Example
PSG Level Rule 037-30-1 01 1st 1000 worked hrs 02 2nd 1000 worked hrs 03 3rd 1000 worked hrs 021-21-1 01 0 - 80 worked Days 02 81 - 160 worked Days 03 161 - 240 worked Days
I need to create a table or report that will populate the correct LEVEL to the employee depending on what their PSG is. Any ideas?
First of all, I had some experience many years ago with setting up a db. Nothing since, so if I am in the wrong place, please feel free to say so.
I am in charge of a very large list of people, mostly dead, for a historical society in my town, and much of the data is on paper or in Excel files. In order to get a handle on how to organize it in a db, I have been entering data from paper to Excel for some time. Gradually, I have been able to combine different things, and sort out data to fit in different tables. A person has personal info LastName, First etc, birth/death data, cemetary records, historical artifacts catalog numbers, military records (I know the height & hair/eye color of civil war vets!) of battles fought, medals awarded, ranks, etc, etc. It is obvious to me that a db would be a much better place to keep this data than in Excel, but that would require a db to be designed. I understand enough about dbs to start the process, but I still have a bit more parsing to do. (You wouldn't believe how many different kinds of information can be shoved into the Remarks field!) I have spoken with some people who work with dbs for corporations, FoxPro etc. and they agree that a db would be better.
The problem that they see (all claim to know little or nothing about Access) is something to do with the Primary Key. Let me give you an example: John Doe is a person who at present is assigned a personal ID. That ID goes everywhere with him because he is not the only John Doe. In fact I have 7 John Does, not all born in the same century, not necessarily related, hopefully with different birthdates, usually in a different grave, but sometimes the grave is unknown as with many veterans who died in prison, etc. and sometimes there is only an approximate birthdate or something like that, if I'm lucky. Also sometimes gravestones list children's names and it is unknown if the child is buried there or with their spouse. In the case of one woman, there are 4 listings as she was a daughter and married three times with a name on the stone of each husband. Of course, one can assume that she was not buried with the husbands who died earlier than the last one, BUT that is not always true, because sometimes women remarried other men for convenience but asked to be buried with their true love. See, it gets complicated sometimes and this was the reason I assigned each a PERSONAL ID. I had to find a way to reference each one to make sure that they were with the right persons.
For geneological purposes, it is important to have as many relationships as possible connected to each individual. One John Doe might have 4 children by two wives. Each wife has a husband and his ID as well as the children and their IDs listed, under the husband and son and daughter fields which include the husband, son, and daughter ID fields. My DB professionals say that that is a real problem. How do I set up a db so that I keep the correct husband with the correct wife without their respective IDs?
These IDs are a convenience to me as I enter data. They could be dispensed with once they have a Primary Key, AND are related to those other people. The trouble is, I haven't the foggiest idea of how I am going to get that accomplished when I transfer the data from Excel to Access.
And so I turn to you good people for some suggestions, even if it is to go to a different URL and bother them. Thanks in advance.
I am new to both Access and VBA. Can anyone provide any recommendations as far as good books on both Access and VBA, or either one seperately? Thanks for any help you can provide!
I was wondering if anyone can recommend a good book for learning more about Access?
I have basic experience with the programme, but am hoping to tackle the old 'questionnaire/survey' issue. A lot of books I can find on amazon seem to be set at such a level that I can already do (multiple tables, simple queries and reports), I need something a little more in depth. Others don't seem to tackle the specific issues I'm going to face (although I could be wrong about that!). Also, as I usually don't bother with computer manuals (I generally pick up and play with new programmes) I don't know which ones are best (e.g. Microsoft own, 'Dummies' etc)?
Recently I moved the back end of some databases I develop and maintain to a SQL Server for speed and stability purposes. Whilst this forum has been great for digging out answers to various Access problems I encounter, I persume there are some similar forums dealing with SQL. Has anyone got some recommendations ?????
I have our primary web based inventory system that I am exporting to Excel and using this as an import to Access for the main raw data for my database. This being inventory it changes daily so I am updating this table every day. When I try to append the table it ads all the records. I am wanting an easy way to add only the new records/take out the ones that are no longer there. Basically update the table with what is currently there.The only have I have found to do this is by running non-matching queries and update queries.
Someone, who is no longer working at out organisation, created a system in Access which we are trying to get into, however the creater put on some security which will not let us open the system to alter. Is there a way of getting into this?
hi i am trying to make a quiz system using ms access i want to select 30 questions randomly from a questionbak of 100 or more also i need to select 3 answers randomly including the corect answer from answer bank that has 5 possible answers for each question
1.st Job: I have an access 2003 db. I want to implement barcode system to the DB in which I can print barcodes in any kind of barcode printer and also when imputing an order read data from barcode scanner etc.
2nd Job: I want to put access db to a server so I can view reports, forms, imput data and retrive data from internet explorer window (with a password).
I will give my DB to you so you can work on it. Please do the pricing seperate for each job.
Please PM or Reply if you have more than 500 posts. PLEASE RESPOND IF YOU REALLY KNOW HOW TO DO IT.
I have a system DSN, pervasive ODBC engine interface. In Access 2003 I can link to the DSN but can only link to some of the files. This started when I changed to a new computer. Before, I had a link to the same database using Access 2002 with no problems.
My office computer, along with the phone system, printers etc took a lightening strike last week. The hard drive survived but not the computer. I was able to get the office access db onto a new system but now I get errors when running it. When opened, the main menu appears. Whoopee!Not so fast. When I select an item, I get "the expression On Click you entered as the event property setting produced the following Automation error. The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure]There may have been an error evaluating the function, event, or macro"
Pressing the button a second time does not produce the error and opens the correct form.This form works properly. The second problem is with a second report form that opens properly.This form expects dates and accepts them but when I try to print the report, access closes with no error message.
First post, so I hope I'm following the post-etiquette!
Anyway, I've just been employed by a company who still uses access 2.0 and lotus smartsuite.
Basically I'm gonna have to migrate a few of their backbone databases to access 2000+
I've managed to find the old Microsoft access 2.0 book in the company amazingly, which is a help.
I was just wondering if anyone knows any good sites for migration, or any particular problems that may be encountered. I'm just doing some background work at the moment, this won't be happening for a few weeks (hopefully!)
Any help would be greatly appreciated.
I'll just take this opportunity to say that I've found the site very useful in the last few weeks and hope I can contribute in the future when I break out of newbie status! -Spud.
Through word of mouth I hear that you can creat a link that can go from Access and link to the personal company system. Is this true? If it is, is there a standard code to use?
After learning that 2007 has no User Security roles, and not having Sharepoint or a SQL server, I decided to work starting with Bob's Simple Login script located here (http://www.btabdevelopment.com/main/AccessSamples/tabid/54/Default.aspx).
I've got it functioning fine and incorporated some of the options also made available here (http://www.databasedev.co.uk/login.html).
You'll see the code below used to store info in a hidden form that is holding the username and permissions level. I'm looking to try and store this information into a global variable instead of a hidden table.
I know that I could define it as a variable right here in the code, but how do I define it as a Global variable so I can use it later in the application in the VBA?
Private Sub cmdLogin_Click() Dim strUser As String Dim strPWD As String Dim intUSL As Integer
strUser = Me.txtUser If DCount("[UserPWD]", "tblUsers", "[UserName]='" & Me.txtUser & "'") > 0 Then strPWD = DLookup("[UserPWD]", "tblUsers", "[UserName]='" & Me.txtUser & "'") If strPWD = Me.txtPwd Then intUSL = DLookup("[SecurityGroup]", "tblUsers", "[UserName]='" & Me.txtUser & "'") Forms!frmUSL.txtUN = strUser Forms!frmUSL.txtUSL = intUSL Select Case intUSL Case 1 DoCmd.OpenForm "frmHome", acNormal Case 2 DoCmd.OpenForm "frmHome", acNormal, , , acFormReadOnly Case 3 MsgBox "Not configured yet", vbExclamation, "Not configured" Case 4 MsgBox "Not configured yet", vbExclamation, "Not configured" End Select DoCmd.Close acForm, "frmLogin", acSaveNo Else If MsgBox("You entered an incorrect password" & vbCrLf & _ "Would you like to re-end your password?", vbQuestion + vbYesNo, "Restricted Access") = vbYes Then Me.txtPwd.Value = "" Counter = Counter + 1 If Counter = DLookup("[OptionValueNum]", "tblOptions", "[OptionsID]=1") Then MsgBox "You have entered an incorrect password too many times. This database will now close!", vbCritical, "Wrong password!" DoCmd.Quit End If Else DoCmd.Quit End If End If End If End Sub
Will keeping your field size shorter result in a smaller MDB file?
Or does Access only use as much space as there is real data in its fields.
Way back in the dBASE III days, dBASE would pad all your "real" information with as many spaces as necessary to fill up your field. I suspect that the MDB structure is probably smarter than that.
Another question on the same topic - I believe there is a maximum number of characters in a record (4000?). Can your field sizes add up to more than 4000, as long as the actual data, all combined, never totals 4000...? Thanks............ ..dc
I have an Access 2010 Database frontend to SQL Server 2008 Backend. So far there are 3 Tabs in the Navigation System. The first one is the primary Data Entry form which has a tab control with 3 subforms.
Here's the problem. The database is designed to track potential members based on different types of Ads. The primary Data Entry form holds all of the demographic data for the person that called in response to an ad. The first tab has a subform to track how many times that person has contacted us. The second subform tracks which advertisements the person is contacting us about and the third tracks which Events that person attended. There is no correlation between contacts, Ads and Events.
On the Advertisements we have about 7 different types of Ads we do and each ad can be run multiple times on different days. We want to be able to track which Ad the person contacted us about. The Advertisement subform has a comgo box for the Ad type and a combo box for the ad date. the source for the combo box for the ad date includes the combobox for the adtype as the criteria. That criteria is what's giving me the problem. I've used:
I'm still in design phase but I'm going to have more than 30 tables. I know I'm going to have a pretty good number of forms, queries and reports. Should I start preparing for the worst in case this db gets too big? What kind of techiques can I use to make sure that it still runs quickly enough?
we have one access database with size about 600mb and 1 million rows in one table.My concern is is it bad to have so much data in access database. If so what is the possible solution for that.
I will like to know if access can store a lot of data. I am using a database that was written in access and the database has been in use since like 2004. How long can we still use this database. It is becoming slow and having one or two error messages.
I have a large db. I need something to monitor the size of it. For example the db is saved on a server and used throughout the day. What I need is something to email me an alert when the db reaches 900mbs. Then I can compact and repair the database back down.
I do purge it annually though. Currently it is about 750 mbs.
I am working on something and have to link I think a few thousand tables from a "store bought" database here at work. Is there a limit in regards to access? I know they are just linking but I didnt know if Access gets nutty after a while...
Anyone encounter functionality breakdown due to size or complexity?
Hi, i am wondering how to reduce the size of access. i have included pictures within forms which obviously made the database bigger (it is currently around 12mb). However i have tried to delete the pictures to reduce the size, this method did not work at all. The database remains the same size. does anyone have any suggestions on how to solve this problem i am having?
