Archiving MS Access 2007 Table Records With Specific Date?
Jul 23, 2012How do i archive Ms Access 2007 table records with specific date?
Any easy way to do it without writing any macros?
ADVERTISEMENT
Import Specific Excel Worksheet Into A Table In MS Access 2007
Aug 21, 2012I have been trying to import an excel sheet (a specific excel sheet in the workbook) using this method but I get an error:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Customer", "C:Download.xlsx", True, "CustomerFormatted"
where "CustomerFormatted" is the sheet I am trying to import in to the "Customer" table.
The error I get is Run Time Error 3011, The MS Access engine could not find the object "CustomerFormatted" make sure its name is spelled correctly ....
Is there any other way I can import an excel sheet in to an access table?
Queries :: Select All Dates Since A Given Date Without Referencing A Table? (Access 2007)
Apr 23, 2014Is it possible to create a query to select all dates from a given reference date? I don't mean all dates in a table - I mean all dates generally?
(The idea being to fill the first field in the resultant dataset with the list of dates, then run subqueries off that to fill the remaining calculated fields)
I'm currently using a date field in one of my tables to populate this first field (the full SQL is in a separate thread here)
But that was just a convenient way of getting a list of dates; the dates in that table don't actually have any significance to the resulting dataset (other than they should roughly overlap with the dates I'm looking for)
The flaw in that method is that the table from which I get those dates can only ever have dates up to and including yesterday. I also need to get today's date in there (and calculate the subqueries based on that date as well).
It's also possible - although unlikely - that there could be random dates missing from that table as well - in which case I need to plug those gaps and calculate my fields for those missing dates as well.
For clarity; that first field (AsOfDate) should contain every weekday from the earliest date in that table (i.e. Min([tblBalances].[BalanceDate]) up to and including today. It doesn't matter if any of the dates inbetween are missing from tblBalances as the subqueries will just return zeroes for those dates (which is exactly what I want to see).
Is It Possible To Access Specific Records Without Locking The Whole Table?
Nov 19, 2007Is it possible to access specific records from a Microsoft Access table without locking the whole table?. I have different processess accessing at the same time different records from the same table and I getting an execption, indicating the table is locked. Can any one provide me some help regarding how to access specifi records without locking the whole table in MS Access?
View 1 Replies View RelatedModules & VBA :: Access 2007 / Code For Bulk Importing TXT File Into Separate Records Same Table?
Jul 9, 2014I have 12,000 cvs that i need to get into An access database so i can start to extract email info etc. They are stored in one folder All Cvs under each of their names edc.txt (i have converted them to .txt) i want to create one table with two fields name (taken from the cv filename and contents (taken fromthe contents of the .txt file). I am using Access 2007.
View 2 Replies View RelatedAccess 2007 Not Finding Specific Printer
Jan 16, 2014When attempting to print a report in Access which was setup to print to a specific printer I am getting the following message.
This document was previously formatted for [printer name] which is currently not available. Do you want to use the default printer [printer name]
I don't understand because the printer and port name it is referencing is available and matches perfectly! I've tried removing the printer and deleting the port and re-adding it with no success.
How To Populate Data Field With Specific Date To Existing Table In Access 2010
Jun 14, 2013I have a table with more than a million records. I want to add a new column with date equals to e.g.3/14/2012 for all the records. I am using update query, but it is super slow (taking more than a hour)..is there any other way to do this?
View 6 Replies View RelatedQueries :: Access 2007 - Query To Pull Fri / Sat / Sun Occurrences In A Specific Month?
Jul 7, 2014Access 2007 query that has a specific date range between #6/1/12014# and #7/1/2014# returns the date I need. How do I now get it to pull just occurrences on Fri Sat and Sun of the month of June?
View 5 Replies View RelatedArchiving Records
Oct 3, 2006Hi guys
I need some help working out a way to archive records. All i really want is a button on a form that when clicked deletes the record currently displayed on the form from its table and moves it to another table that i will call 'archives'.
I think it might have something to do with using an append query but i cannot think how to do it.
Thanks in advance.
James
Archiving Records
Jul 24, 2006Hi to everyone. I was assigned to collect all data regarding the electronic equipment of the firm that I work for and I just need a push to start with :D
My thought is to create 3 tables (one containing the users, another one containing info about the equipment and a third one to be something like an archive or a log...e.g. this monitor is now currently being used by me but in the past somebody else had it).
I am not quite sure how to do this (the archiving I mean...) Any help is welcome
Thanks...
Kyriakos
Archiving Records
Oct 3, 2006Hi guys
I need some help working out a way to archive records. All i really want is a button on a form that when clicked deletes the record currently displayed on the form from its table and moves it to another table that i will call 'archives'.
I think it might have something to do with using an append query but i cannot think how to do it.
Thanks in advance.
James
Archiving Records From Two Tables
Nov 24, 2006I have a database for tracking tasks. each task may have associated documents for it which are kept in a separate table.
I have an archiving procedure which takes closed or completed tasks (from the main table) and appends them to an archive table but it leaves the associated documents in the second table, or if I enforce integrity it will delete them.
Anyone have a suggestion on how i can archive records from two tables at the same time I include the archive code for your information....
Many Thanks as always for your help and assistance
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''
' procedure archives closed or complete records to archive table and then '
' Deletes original records from source table '
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''
On Error GoTo Err_btnArchiveAdhoc_Click
Dim strMySql As String, strResponse As String, intRecordCount As Integer
Dim cnn As ADODB.Connection
Dim rstTemp As ADODB.Recordset
strResponse = MsgBox("By clicking Yes you will Archive any closed or" & _
vbCrLf & "completed tasks and delete the original records from the table", vbExclamation + vbYesNo, gstrAppTitle)
If strResponse = vbNo Then
Exit Sub
Else
Set cnn = CurrentProject.Connection
Set rstTemp = New ADODB.Recordset
' builds sql string to append closed or complete records from the original table into archive table
strMySql = "INSERT INTO tblArchiveAdHocTasks ( Seq_Number, Item_Type, TaskTitle, Task_Description, TaskActions, StartDate, TgtDate, ReviewDate, TaskLead, Status, LastUpdatedBy, estHours, Progress, PersIDAssocPerson1, PersIDAssocPerson2, PersIDAssocPerson3, PersIDAssocPerson4, PersIDAssocPerson5 )"
strMySql = strMySql + " SELECT tblAdHocTask.Seq_Number, tblAdHocTask.Item_Type, tblAdHocTask.TaskTitle, tblAdHocTask.Task_Description, tblAdHocTask.TaskActions, tblAdHocTask.StartDate, tblAdHocTask.TgtDate, tblAdHocTask.ReviewDate, tblAdHocTask.TaskLead, tblAdHocTask.Status, tblAdHocTask.LastUpdatedBy, tblAdHocTask.estHours, tblAdHocTask.Progress, tblAdHocTask.PersIDAssocPerson1, tblAdHocTask.PersIDAssocPerson2, tblAdHocTask.PersIDAssocPerson3, tblAdHocTask.PersIDAssocPerson4, tblAdHocTask.PersIDAssocPerson5"
strMySql = strMySql + " FROM tblAdHocTask "
strMySql = strMySql + " WHERE (((tblAdHocTask.Status)=6 Or (tblAdHocTask.Status)=10))" ' status 6 is closed and 10 is complete
DoCmd.SetWarnings False
'DoCmd.RunSQL strMySql ' runs append query
rstTemp.CursorLocation = adUseClient
rstTemp.Open strMySql, cnn, adOpenDynamic, adLockOptimistic
DoCmd.SetWarnings True ' turn warnings back on
' deletes records that have been archived above
strMySql = "DELETE tblAdHocTask.Seq_Number, tblAdHocTask.Item_Type, tblAdHocTask.TaskTitle, tblAdHocTask.Task_Description, tblAdHocTask.TaskActions, tblAdHocTask.StartDate, tblAdHocTask.TgtDate, tblAdHocTask.ReviewDate, tblAdHocTask.TaskLead, tblAdHocTask.Status, tblAdHocTask.LastUpdatedBy, tblAdHocTask.estHours, tblAdHocTask.Progress, tblAdHocTask.PersIDAssocPerson1, tblAdHocTask.PersIDAssocPerson2, tblAdHocTask.PersIDAssocPerson3, tblAdHocTask.PersIDAssocPerson4, tblAdHocTask.PersIDAssocPerson5"
strMySql = strMySql + " FROM tblAdHocTask"
strMySql = strMySql + " WHERE (((tblAdHocTask.Status) = 6 Or (tblAdHocTask.Status) = 10))" ' status 6 is closed status 10 is complete
rstTemp.CursorLocation = adUseClient
rstTemp.Open strMySql, cnn, adOpenDynamic, adLockOptimistic
End If
MsgBox "Export Complete ", vbInformation, gstrAppTitle
Exit_btnArchiveAdhoc_Click:
Exit Sub
Err_btnArchiveAdhoc_Click:
If Err.Number = 2501 Then ' runtime error as user cancelled delete part of query
Exit Sub
End If
MsgBox "'Error" & Err.Number & "'" & vbCrLf & Err.Description
Resume Exit_btnArchiveAdhoc_Click
End Sub
Archiving Old Records Through A Form
Dec 11, 2007I'm not even sure which forum to put this in, as it involves a form, two tables, some queries, and possibly a macro. I've poked around the message boards here and haven't quite found a method that works for my situation. So here we go!
I have a recruiting database that I've developed for some other users in the office who are not Access builders. They have asked if there is a way to archive old records into another table for safekeeping, which I can do through the database window, but which they probably couldn't. I want to give them the ability to tuck these records away as needed, and without them having to ask me to do so every time.
The main table in the DB is called tblPosition. There is a form called frmPositionManagement which is bound to this table. It's through this form that the recruiters make all their updates and whatnot to records stored in tblPosition. I also have a table called tblArchive which I have created using the exact same fields as tblPosition so I can store the outdated records there.
I'm looking for a programmatic way to do this. I was hoping to put a button on frmPositionManagement that would let the user remove that record from tblPosition and send it to tblArchive. I had figured on creating a macro (mcoArchive) that took the following steps:
* MsgBox "Are you certain you want to archive this record?"
* Run an Append Query to add the selected record to tblArchive
* Run a Delete Query to remove the record from tblPosition
* Close the Append Query
* Close the Delete Query
Then I started building those two queries and the macro, and the wheels fell off my brain.
The difficulty I'm having is getting the system to say to itself, "The record currently displayed on the frmPositionManagement is the one I need to append and delete." I have a specific record selected on the form, so how do I pass that record's unique ID through to the two queries to make this record the one which is appended/deleted?
I had tried setting the criteria for the unique key in the Append/Delete queries to =[frmPositionManagement]![AutoID], but that just led to a pop-up box that asked me to input frmPositionManagement!AutoID, which is not what I wanted at all.
My VB isn't all that great, so I was trying to stick to macros, but if someone has an idea for a programmatic solution for this conundrum, I'd appreciate hearing it!
Thanks,
Andreas
Queries :: Records Active ON Or AFTER A Specific Date
Nov 26, 2014I have a query from a table (to reduce the number of fields).The list is all the positions people have performed, as person could have one record another could have 10 the number is unlimited.
Fields are:-
-employee ID
-start date
-position
I need to find any records that were active ON or AFTER 01/09/2014. This will be 1 record for most but some could have multiple.I think it makes it more difficult that there is no END DATE.
Tables :: Access 2003 Table Data Import Into Access 2007 Table
Apr 26, 2015i have a database in access 2003 when i open it with access 2003 it shows data in table but when i open same table in access 2007 it shows only header rows , no data
how can i see this data into access 2007 or excel 2007.i want to link these table data with excel 2007 or access 2007 but with above problem i can't do it
Access 2007 Date Format
May 2, 2008I have converted an old database to Access 2007 and find I need to edit my queries that use the date format function.
eg change from Format([date],'yyyy') to Year(date)
I can change each query manually but is there a quicker way to do this?
Access 2007 / Date Picker Doesn't Appear
Aug 7, 2010I've got the data type set to Time/Date and the Properties of the date picker set to, 'Yes, for Dates' but it still doesn't appear in my table column.
How do you add it to a form? (Access 2007)
Displaying Form Records Created By User And Specific To A Date
Jul 24, 2005Hi,
I was wondering how difficult it would be to do the following:
1. A user opens up a form.
2. The user uses a combo box to select his/her name.
3. There is a date combo box on the form and by default, it is set to today's date. So when the user selects his/her name, the records for today are displayed. Changing the date will show only the records for the date shown.
4. The user will have the ability to add and delete records specific to the criteria chosen in the text boxes. Adding new records will only add records for the date chosen in the combo box. E.g. if he/she adds a record for today, it will only be seen when today's date is selected.
Will a subform have to be used for something like this? Or could it be done with one form using filters?
Any thoughts and/or approaches on this would be greatly appreciated.
TIA.
Modules & VBA :: Constructing SQL To Insert New Records (Access 2007)
Jul 11, 2013I have a command button on a form which loops through pages on a tab control and constructs an SQL string to insert a new record into a corresponding table (each tab has it's own separate table)
The tables are linked via a common account ID (an autonumber on a separate 'Core' table, the latest record for which is generated earlier in the code; this section creates a linked record in each of the other tables)
I've used a naming convention in each page such that each control name and it's corresponding field name in the destination table are the same (apart from the first 3 characters which I use to identify the control type, i.e. "chk", "txt" etc.)
For one of my pages, the insertion of the new record keeps failing. I've gotten it to work once or twice but only by randomly changing some of the values on the form (checking / unchecking boxes or keying data into random textboxes etc.) But I can't identify why it works sometimes and not others?
The other 3 tabs/pages insert records with no issue, every time.
I've tried debugging but I don't get any error message when I execute the constructed SQL; there are no apparent control violations and all of the relevant fields are correctly named & referenced. The only 'required' field in each table is the common Account ID, which is present, correct & not duplicated, so omitting null / empty fields or passing empty strings / values for the remaining fields shouldn't be an issue (?)
I can only assume it's a problem with a value being passed somewhere but what's stumping me is that on the other pages, it inserts the records exactly as expected (whether data has been provided or not)
Stepping through the VBA, it looks like a record should be inserted - but when I check the table, there's nothing there?
Code:
Dim pge As Page
Dim ctl As Control
Dim strSQL As String
Dim strSQLFields As String
Dim strSQLValues As String
[Code] .....
Forms :: Make Specific Textbox Become Current System Date To All Records
Sep 24, 2014I have a problem in my Database system. The title of my Database is Employee Evaluation. This is my problem.. I already make the default value the txtbox a current system date. but for the next day.. the txtbox for all record should become a current system date. but only the for the new record is the current system date. the all records are not current system date... I want from the opening of my system the textbox of all records are should be current system date.
View 14 Replies View RelatedQueries :: Task Completion Database - Search Records With Specific Expiry Date
Nov 17, 2014I have a task completion database. Each record of model consists of an expiry date. I need to review and update the records before the expiry date. Each model may have more than one record as I have updated the models a couple of times. I would like to select all of records with the expiry date less than 30 days so I can plan to review and update them on time. How to select all these records.
View 2 Replies View RelatedModules & VBA :: Archiving Data - Table Structures Are Identical
Jul 26, 2013I'm trying to create an archiving routine as my database is becoming very large. For about 10 tables I want to shift certain records to an external database which would have the required 10 tables with the same table names and structure.
So far so good. I now want to automate everything using vba. I can see how to use the INSERT INTO statement but I don't want to have to name every field as there are hundreds. I just can't see how to do this.
If the table structures are identical how do I neatly shift a bunch of records from one to the other using code.
Forms :: Access 2007 - No Records Shown When Continuous Form Is Opened
Dec 21, 2014IN Access 2007 when a Continuous form is opened there is no records shown.
Only after hitting the page down records are shown.
Queries :: Access 2007 - Insert Into Table Where Source Is Another Table And Form
Feb 3, 2015If you want to use a "DoCmd.RunSQL "INSERT INTO" command to insert data in a table and the data to insert comes from a table and a form, could this be done in one pass?
So...writing a record wit 4 values from table1 together with a additional value from a textbox in table2 as 5 values.
Table Problems Moving From Access 2003 -> Access 2007
Jun 19, 2007Attached is a screenshot of the relationships in a database I built a couple of years ago. It's worked absolutely fine in Access 2003 and currently has over 18,000 customers with associated information in it.
However, when I open the database in Access 2007 the performance is awful. All the forms are very slow to respond when tabbing between form elements. I've experimented by reducing the number of form elements calling on related data on a given page and whilst this improves performance it reduces usability - something I don't want to compromise on especially since Access 2007 should be able to cope with this.
My next question is therefore whether I've got the most efficient underlying table design and I can't see any other way of doing it than my current method so I'd be grateful for any feedback or advice anyone has.
Queries :: Converting CSV File Into Text Format - Export Records With Specific Series And Date
Jun 7, 2015I have a CSV file and want to convert it in a text format with some filtered data and with some formatting. This is an everyday task for me. So I made a table and imported the data in to it by the command :
DoCmd.TransferText acImportDelim, "fo Import Specification", "fo", FileName:="C:UserswelcomeDesktopfo.csv", HasFieldNames:=True
Actually I have a column "SERIES", contains various series like "EQ", "BE", "DR", "BZ", "D1" and so on. And one more column with the dates having 4 / 5 current months dates and one next months date and one next to next month's date. And every date has got several thousand records.
now the issue is that : After importing these several thousand records, I want to export it but with a specific date and with a specific series.
The other thing is that, these dates change every month so if hard coded, the problem will occur the next month.
I use this code for export :
DoCmd.TransferText acExportDelim, "NewFnoSpec", "fnoquery", "C:UserswelcomeDesktopFO Output.txt", True
this code is working fine but when the month will change, the code won't work.
Can we have a date & series picker attached to this query, so it can export the records with the specified SERIES & DATE.
I tried putting a textbox on the form named TxtDate and in a Query ( Design mode ) under the date column, in criteria I have put [Forms]![Futures]![TxtDate] and after putting this line, the query becomes empty and no data is there.