Hi folks,
I have searched on this, but did not find any article that is comprehensive enough. I wonder if anyone can advise on database maintenance routines particularly for Microsoft Access.
I know:
Regular Back ups
Compact and repair database
Please fell free to add on to this list.
Thanks,
B
Morning guys and gals
I am trying to create a sceduling calendar for my department which displays given events day by day.
I have created this using subforms for each day and a combo box to select the month.
For display purposes it works fine but I am having a little difficulty with the functionality.
I want to be able to click into any of the subforms, and depending on whether there is data within, open a specific document.
1)If there is an item in the schedule the I want to open a report filtered for that date.
2)If the day is blank then I want to open a form to input an item. (This works but I cannot get the date to pre-fill)
I have created a seperate piece of code to do this but it doesn't want to work. It may be because my knowledge of VB isn't what it was or it may be that I am completely barking up the wrong tree.
Hopefully someone can have a look and let me know where I am going wrong.
Code
Private Sub OpenCalRep(date1 As Date)
If date1 = Null Then
DoCmd.OpenForm "frmmaintenance", , , , acFormAdd
Forms!frmmaintenance.txtDate = Forms!frmcalsite.txtDate
Else
DoCmd.OpenReport "rptmaintenance", acViewPreview, , "Tables!tblmaintenance.txtdate" = "forms!frmcalmain.sf1.form!txtdate"
End If
End Sub
I realise that there is no counter to increment the subform name yet (SF1, SF2, etc) but I want to get the code to work for just the first box initially so I know that I'm heading in the right direction.
This code is the prefilled from the subform by using:
Private Sub SF1_Enter()
OpenCalRep (Forms!frmcalmain.SF1.Form!txtDate)
End Sub
I realise that there is no counter to increment the subform name yet (SF1, SF2, etc) but I want to get the code to work for just the first box initially so I know that I'm heading in the right direction.
Regards
Jason
My database consists of several forms linked by open form buttons that function as "previous" and "next" navigators through this series of forms. Each time I switch from one form to the other, the record jumps back to the first record.
Question:
When entering a new record, how can I make it so that when I switch from the first form to subsequent forms, the record on the subsequent forms will match the record on the form prior to it without having to use the record navigation buttons at the bottom of the form.
Got a little system going whereby when a job is created it will display the date that the job is next due by.
I'm trying to work out how to create a qry that will display all due dates & not just the next one.
Here is how it should work.
If a job is created on the 1st of April 2007 & the type of job is MONTHLY then I want it to list the rest of the monthly orders that are planned for the year.
Here are the different PPM types that I'm using to calculate with
8784= Yearly
43848= 5 Yearly
2208= 3 Monthly
744= Monthly
168= Weekly
Has anyone done anything similar cause I can't see how it can be done?
This is what I'm using to create the next date due
NextDue: DateAdd("h",DLookUp("[PPMNextDue]","[tblPPMType]","[tblPPMType]![PPMID]=" & [PPMID]),[DateReported])
Here is my qry that the above code is in.
SELECT tblPPMType.PPMID, tblBuildings.PropertyName, tblPPMType.PPMType, tblTasks.DateReported, tblPPMType.PPMNextDue, DateAdd("h",DLookUp("[PPMNextDue]","[tblPPMType]","[tblPPMType]![PPMID]=" & [PPMID]),[DateReported]) AS NextDue, tblContracts.ConDescription
FROM tblContracts INNER JOIN (tblBuildings INNER JOIN (tblPPMType INNER JOIN tblTasks ON tblPPMType.PPMType = tblTasks.JobDetails) ON tblBuildings.ProID = tblTasks.BuildingID) ON tblContracts.ContID = tblTasks.ContractNo
ORDER BY tblPPMType.PPMType;
This is the result.
DateReported---------PPMNextDue--------NextDue
14/03/2007 14:35:34----8784----14/03/2008 14:35:34
15/03/2007 15:08:00----8784----15/03/2008 15:08:00
15/03/2007 10:07:11----8784----15/03/2008 10:07:11
14/03/2007 14:52:56----2208----14/06/2007 14:52:56
15/03/2007 15:10:21----2208----15/06/2007 15:10:21
15/03/2007 10:19:35----43848----15/03/2012 10:19:35
14/03/2007 14:54:13----43848----14/03/2012 14:54:13
14/03/2007 14:55:15----4416----14/09/2007 14:55:15
15/03/2007 22:36:27----744----15/04/2007 22:36:27
14/03/2007 14:56:30----744----14/04/2007 14:56:30
15/03/2007 20:34:27----744----15/04/2007 20:34:27
18/03/2007 22:38:41----744----18/04/2007 22:38:41
15/03/2007 13:39:02----744----15/04/2007 13:39:02
15/03/2007 10:58:55----168----22/03/2007 10:58:55
14/03/2007 14:59:21----168----21/03/2007 14:59:21
Any suggestions will be welcomed
thanks :)
Hi,
I can't seem to get this right...any help much appreciated:
I have a two tables LESSON<PAYMENT (One to Many)
LESSON
Lesson_ID (PK autonumber)
Lesson_Date (Date)
PAYMENT
Payment_ID (PK autonumber)
Lesson_ID (FK)
Payment_Date (Date)
I am maintaining LESSON using a simple form. On creating each new LESSON record, I also wish to insert a row into PAYMENT, using values from the LESSON table - Payment_ID(autonumber), Lesson_ID = LESSON.Lesson_ID, Payment_Date = LESSON.Lesson_Date
What is the best way to do this?
I have tried to add an Event Procedure to 'Before Insert' but I can't seem to get the syntax correct. Also, for this to work, do I need to paint all fields on LESSON form including (hidden) PK?
I have simplified the tables above but they are relevant to what I am trying to do. Any help would be much appreciated- am new to Access...rather frustrating.
Many thanks,
Simon.
PS I have searched through existing messages but can't see one that answers this, I apologise if this has been answered before- just point me to the orig post.
I have a table called schedule maintenance.
In that table I have a "date" field
Is it possible that if " todays" date = a date in the field date a e-mail can be sent to me to remind me there is a scheduled maintenance to be done.
I've built and implemented an Access 2010 application for a corporate client. It's entirely menu driven, with no user access to the navigation pane. It's split with multiple BEs on a share drive and the FEs on individual PCs (distributed via Auto FE Updater). Two hundred users have been defined to the application but so far there are only up to 10 max concurrent users at a time.
The clients want to add more users to the system but after that will go into maintenance and support mode. The question is how much I should charge for the maintenance and support service on a monthly basis.
I'm not looking for a $ amount as much as strategies/guidelines for pricing. The clients anticipate using the application for about four years, at which point a new, corporate-wide application should be rolled out and my app will get replaced by it. They will probably need to convert the data from my app into the new app, and will want/need occasional enhancements to the system over the next four years as well.
I'm trying to set up a simple query that links four tables. However, the tables are extremely large, all in excess of 1.5GB each so I had to split the tables up into four separate DBs. I've tried the following with no success:
1) Link the 4 tables in the DB which contains my primary key. This quickly inflates increases the file size above 2GB and won't let me go any further.
2) Build a remote query to connect the four tables. This looked promising until I tried to run the query and it became evident that it only knows to point to the last database source that you specified.
I'm running everything locally on my C drive. The data source are simple text files (1.6 million rows) from the FDA website.
Hello All...
Well, I am facing one problem..in my application; I need to show all forms / reports name of other database( .mdb ) file without opening the other database physically. I tried a lot but didnt succeded. I tried with below code..
Set AcApl = New Access.Application
Call AcApl.OpenCurrentDatabase(strfolder, True)
Set AcProj = AcApl.CurrentProject
Set frm1 = AcProj.AllForms
intCount = frm1.Count
But here wen the second line AcApl.opencurrentdatabase get executed at that time the database get open physically, and i dont want that..So is there any other way around..If so..please please help me..
Thanks in advance..!!
i have a database that runs updates from within itself.what i need is, this database to then open a another database run a update query, then close it.
View 4 Replies View RelatedDatabase: Access 2007
I have designed a touchscreen input system using Visual Basic.net and this writes to an Access Database. Each Touchscreen has its database locally so it can still work even if there are Network problems.
Now what I would like to do is have all these local databases write to a central database say every minute but only write new records to the central database. The Central Database can either be Access or SQL.
What is the best way to do this?
I have been working on a database for over a month now, and my boss just threw a monkey wrench in my work. I believed that the Metal Market Prices would be entered once a week in the current DB. My boss informs me today that he wants an employee to go in every morning and enter that days Price for Each Metal with respect to many different markets.
There is a total of 12 metals, and 5 markets. I need the data to be stored first by date, then by either market or metal, and lastly by which ever isn't used second (Either: date-market-metal, or date-metal-market). I think the Latter of the two methods makes the most sense. Is it possible for my current DB to lookup values from the Metals Database based on date-metal-market?
I have a problem that seems to be happening on several users' databases and is causing a big problem. None of the databases is a shared database...they are all single-user databases on stand-alone computers. I have tried looking for help within previous posts, but all seem to be related to shared databases.
I am getting an error message: "The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time." The database cannot be opened, imported, repaired...nothing seems to work.
Again...these are NOT shared databases. I appreciate any help I can get. I created the database for all of the secretaries in our school district to keep up with absence data. It involves many tables, queries, forms and reports, and has generally worked well. However I am now seeing several that are getting similar errors as mentioned.
Thanks!
I'm new to Access and VBA, for the record. What I need to do is copy a SQL database (table structures and records) and save it as a local Access database, so that the user can query and make any changes on the local file without editting the SQL database.
Looking around, it seems like the best option would be to use the TransferDatabase method with the acImport option. Does this sound reasonable, or can someone with more experience suggest a better way to go about it? Thanks.
I am testing the security of my DB X on Acccess 2007. I could create a new database Y and linked to the database X. Unfortunately i could change the records on the tables. I don't want other DB that make connections to my DB to change my tables!
View 1 Replies View RelatedIs it possible to access a table in one Database (Database A) from a separate Database (Database B) and if so how. I should clarify that this is an Access Database.
View 2 Replies View RelatedI created a database with forms, querys, etc. Now I was thinking of creating a exe so everyone can use this database without having access to all the design functions.
the only problem I see about doing this is if we need to use the same database for different project, we need to always create a new exe for each project.
Is there a way to make a empty database exe with just the layout, querys etc but with no data on the tables and have a save, open and save as option. So we can have several projects using the same database?
I was thinking to create a VB code for the database to delete all data on the tables when the database is open.
and before closing the database exporting all the data to a txt file or something.
Then the next time someone opens the database goes to the main form which has a open bottom which imports the txt file into the tables.
This way we can have several txt file for several project and use always the same exe database.
I am working on a database that will be an addition to an existing one on the company server. However, to make the overall layout not so complex and allow room for other additions in the future, I'd like to keep the databases separate. This will also ensure more efficiency, integrity and troubleshooting overall.
Does anyone know how this is done?
Thank you in advance for your help,
~Kilch :eek:
The menu for make a MDE file is blanked out or grey’d out. The database is partly made using access 2000 then xp
Jabez
I have a series of unrelated access databases and I would like to have one 'super menu' where i can click a button to go to different databases.
View 3 Replies View RelatedI have a car dealership database and have a servicing section.
I want to create a form to service these vehicles.
The only vehicles which will be displayed on the from are the sold ones (because you cannot service a vehcile which hasnt been sold) which i have a query for.
I also want the form to display the customer who owns the car the service details (eg what they need), the parts availiable and the parts required for the service.
The calculations i need to make in this form are as follows
quantity needed (parts required table) - quantity in stock (purchasing customer parts table)
quantity needed (parts required table) * price (Parts Table) + labour (servicing table) = part/labour costs (servicing table)
http://pic18.picturetrail.com/VOL885/3178839/6480291/97832270.jpg
any suggestions on how to do this would be fantastic
i havent been able to figure out how to get my subform to work properly....
i've posted in the forms section (http://www.access-programmers.co.uk/forums/showthread.php?t=99624 )
it is really important that i figure this out so that i can keep my job..
how else can i obtain help? even if i have to pay for it?
what do you guys suggest?
Hi all,
I was playing with passwords for one of my access db.
I tried opening the db with the password, but it won't work. So, I thought I forgot the password. I checked some posts on this forum that helped in retrieving the password and it's the same that I have been trying. I am not sure if the Name is correct, is there a way to retrieve the log on name?
I also noticed that when I try opening any other db on my system, it asks me to log on, I though I was only applying a password to one file.
Please help
Thanks!
Hello Here is my question I have a datbase with 4 tables
I have one of the table related to the other 3 by agent name.
I Have a page with the agents and all thier info. NO problem
I want to create a link from each agent to thier listings..
Do I need to have a seprate page for each agent or can one page
create the info I need with that agent..??
I have been tasked with setting up a database for all employees the info contained is along the following lines:
Employee Number:
Name:
Qualification Status:
Qualification Run Out Date:
To name but a few of the parameters
I have currently set up the start up screen, main switch board and the complete employee records table with form (for simple viewing/input).
My problem is this we have four departments that require access to the database
Admin - Require access to all employee records (View)
1 Dept - Require access to their employees (View and update records)
2 Dept - Require access to their employees (View and update records)
3 Dept - Require access to their employees (View and update records)
Currently i have created one complete employee record table (Admin) how do i break this down for each department so that when the info within each one is updated it in turn updates the main Admin table?
I started by copying and renaming the Admin table for each department but this never updated.
Any help and info would be appreciated.
On a slightly less important note for the time being at least can i password protect each command button on the main switchboard?
Hello,
I basically started teaching myself how to use Access 2000 about a week ago. I am working on a project for work, that requires to have a report of all the training records. But my boss wants it to be color coded. He wants one color to show the people's training classes that are 45 days out from being over due, he wants another color to show what classes are due. And another color that shows classes that are over due. In my table, I have a column for the classes that show the date that they took the class, and I have another column that shows the due date of the class. But I have no idea how to go about getting the report color coded with what he wants. Any help at all would be great. Many thanks in advance.