I have a Database in which I would like to archive data once it is now longer actively being used. I have VBA code that does everything i need it to, however I would like to name the new archive table related to the dates in the data being archived. Below is the code I am using to archive and delete the information from the main table.
Code:
Dim strSQLCreate As String
Dim strSQLDelete As String
Dim strArchiveTableName As String
[Code].....
I know it will be in the "strArchiveTableName" variable, I am just not sure how to pull the dates out of the data being moved. The TestData table holds a date and time for each test, and the earliest and latest dates is what i want to append to the table name.
For example: Currently "tblTestData_A102Archive"What I want "tblTestData_A102_1/1/2012-1/1/2013"
I am trying to write code in an excel worksheet to try to export excel data to a table in access.
The range in which the data is situated in excel varies. So the range address varies with the data. After a lot of searching I wrote the following code which works for a range which is fixed. The code is the following:
Dim acc As New Access.Application Range(Range("b22"), Range("b22").End(xlDown)).Select ActiveWorkbook.Names.Add Name:="ghazla", RefersTo:=Selection acc.OpenCurrentDatabase "D:PayRollFactoryJasminePayroll.accdb" acc.DoCmd.TransferSpreadsheet _
[Code] .....
As one can see from the first two lines of code I am trying to first delineate the range and then to name it. The intent is to name the range "ghazla" and so to cater for the fact that the extent of data varies.
The code works with the fixed range ="Sheet2$B140". However what I need to do is to adjust the code so that I can insert "ghazla" as the range name so that this can vary.
I'm currently working with Access 2007 building a way to create PODs to clients. I have most everything laid out, one problem I can't seem to get around is naming the file the SOW #. Below is the code:
Function Run_all_PODs_01() Dim myPath As String Dim name As Object
DoCmd.OpenQuery "Q5 SOW bill requested data points all", acViewNormal, acEdit DoCmd.OpenQuery "Q5 SOW bill requested All 01", acViewNormal, acEdit
[Code] ...
I can't seem to get the name to equal the SOW number.
The table that I'm trying to reference is "SOW bill requested data points" it is a basic table one row with a column header as SOW #. I'm not sure how to tell it name the file that data point in the column.
Been hunting a round looking for some code that will output my access report to excel but more inportantly add a unique field from the report to the name of the file. example would be [FileName] & [ReferenceNo].xls.
I would like to add the "Month" of the information queried to my file name at export if possible. I included my current code and an example of how I would like the title to read.
I need to create a word file (it needs to be word) based on template (not a very complicated template) max 1 to 1 and 1/2 pages long.
the word doc needs to be named from the recordset but for now assume 12345.doc and the next one will be 12346.doc etc (I have a unique number system - available from tables /query .
i can either make the word doc in code or use a template (template would be better) recordset could have 20-30 in it each time i run it - but to be run every week
second half is how to email this out. i had in mind a email system
email to . test@testhotmail.xyz file attach = this folder where the docs are store and attach 12345
in a loop
so either
create word file - email word file in a loop each time or create all the word files and then send them individual
I have created a form in Access updating the files each month but I need to keep the previous month data as historical record . how can I creat and archiving function / command so that the files that have been updated are not lost and will be kept for future use? thank you for your help
I need to archive some data in a table with a checkbox on a form. I have tenants table & property tables. Sometimes tenants move out & new move in but i don't want to delete information from old tenant. But i can't have duplicate property id's assigned to different tenants.
So I made a checkbox on the tenants form that sets the tenant to active or inactive with a status column in the tenant table. how do i delete only the property ID so i don't have duplicates when the new tenant moves in?
I have a single table database for inventory. Every 3 months or so, some records get changed or updated. My manager wants to keep an archive of all of the records that have been changed so we can go back and look at an history of all of the records. So, My thought is to create an "Archive" table, appended all of the current records to it. Then, when changes are made, create an Append Query, or what ever works, to copy just the changed records from the Main to the Archive table. There are only 200 items in the inventory so it is not a large database.
i'm in the process of creating an Access database to import data in text files and then export the data as fixed width text files, this is now working fine
My next step is to be able to setup an "Archive" Table which will hold all the data i have imported across multiple import proceudres for the last 30 days, i then want to be able to "De-Dupe" any files i import against this to ensure i never load duplicate data.
I work for an airline, and we use an Access Database to track all baggage claims. The file is getting pretty large, so we want to remove the closed claims from the main table. Is it better to append the closed claims to a new table, or would it be better to export the closed files to an Excel document?
If I choose the option to Append to a new table, can this be done to a second database or just create an additional table in the existing file?
Any help that you can give, I'd greatly appreciate.
I have a form with names and addresses on it that gets its information from a querry. On the form tis a button that operates a tick box to Arcive a record. I also have a list box that finds a record on my database when I click on the name.
My problem is that even though I archive the record and the persons details dont appear, the name still appears in the list box. How can I remove/hide the name from the list box as well. I tried a requery but it didnt work?
i have a form with various fields, on this form i have an archive button that places certain fields in an archive form. however this does not remove the information from the main form. pls see attached
Here is a question I have been hammering away at for a while but have not come up with an answer yet.
I am trying to come up with a way to make a digital archive in addition to the access database that i have been working on. Right now I have records for customers and all of their reservations, as this is a travel agency. I would like to be able to save all emails, pdf receipts, and any other applicable files that may pertain to that customer and/or on one level lower, their reservation.
Yes, I have heard of the attachment option in Access 2007, but I am getting mixed feelings about using that to solve this problem. One reason is because I have heard that this can make a database absolutely huge very quickly. (One question I have about that is whether or not that large size ends up slowing down the database or not?) My other concern is that after creating everything I would really like to integrate the database into SQL Server and only use access as the front end for forms and reports. And SQL Server 2005 does not work with access's new handy attachment feature in 2007.
This must be a common need with various businesses, so maybe you all have a few ideas out there. I would love to hear them!!! I am open to anything that solves this problem. Thanks for taking the time to read this and help me out.
Hi, we have a database for keeping record of our games (unreal tournament games).... Its just a simple database and we enter our results based on the following fields: -
Opponent: Game Type: Players: Maps: Date: Result:
the file has started to get quite big and wondered if it would be possible to automatically move entries say that were older than a month old to a new table, or archive table. Im not a big access genious so i hope i have explained enough for you to understand what im trying to do here.
How can I remove (delete) a page from a table (the entire row that has that specific data) and have it go into an archive? I don't want to completely delete the information, but I don't want to have it in my current table and have to have all kinds of filters and other criteria to view in my queries and reports.
I try to use common naming conventions wherever possible. Every now and then I come across what should be common but I can't find a standard method to use.
It not really a huge problem, but as this is the General forum I thought I'd ask.
Does anyone have a good link with a long list of conventions?
Does anyone have a standard way to name a table which permenantly resides in the client but the data is very much temporary.
I use these tables a lot and was just interested.
No matter if there isn't one, just seems a good idea to open up a discussion for anyone else who wastes time like I do on the name of objects etc. rather than doing the work:-)
Following on from entries in a previous post (see http://www.access-programmers.co.uk/forums/showthread.php?p=612071#post612071 ) I wondered what people considered to sensible naming conventions.
For example should a table be called "Order Details", "Order_Details", "OrderDetails" or "tblOrderDetails".
Should variables in VBA be given a prefix which indicates their type eg strString or intInteger etc.
Hi My database is to have a Personnel data area whereby I can store all relevant details for employees within the company. I plan to incorporate a feature whereby I can alos maintain a history of documentation written and issued including links to the actual documents for that employee during their time with the company.
Has anyone done a similar thing they would be willing to allow me to use? I figure this would contain links to work documents stored on the hard drive in a specific folder. Selection of a specific item from a historical list - would invoike Word /similar and display the appropriate file...
Any ideas would be gratefully received. Thank you.
I have been trying to figure out how to do this and the transferdatabase function wouldn't do what I needed. I have a table that has a bunch of records, each being a request for cad work somebody wants me to do. I change the status of each one to pending, working, or complete. Anyway I want to only keep the completed task records in the database for 90 days. After that the records will be exported to an archive database and purged from the working database. The problem I face is not getting the records I need “I used date stamps ;)” but getting the records I filtered to append the archive database table and not overwrite it. I made backups ;) of my db and am testing on those and cant figure this out. Below is the code I used to export. Like I said it overides the table rather than append...
DoCmd.TransferDatabase acExport, "Microsoft Access", _ "X:Archive.mdb", acTable, "tRequest", _ "tRequest" MsgBox "its done" Any ideas, pointers, stern words and a link to a post I missed, anything to help is appreciated. :o Thanks, Eric
I need to produce a query that will show only one File and location even though there are multiple records contained within that File. To clarify,
In each individual record I have Id_No, Surname, File_Name, DOB, Location.
I need to shred the file once the person is over 25 years old, but some File_Name have different people with different dates of birth. I would like to display the File_Name only if all of other records in that File_Name are over 25 years. I have attached a picture of the query which I use to find the over 25's
Not only do a lot of people not format code (nothing a copy/paste dump of 300 lines with no spacing), they use control names like textbox1, combo47, and form12. It drives me bonkers. This is in part from Expert One-on-One Microsoft Access Application Development. Please read it and understand it. (The book isn't bad either.) Reagrdless of whether or not you agree with it, the point here is that you use some sort of naming convention, even if it's not this one as variants exist. Without it, maintaining your code is exponentially harder, and asking for help in here without some sort of standard convention is far less likely to get a response.That is all. :)Leszynski Naming Conventions (http://en.wikipedia.org/wiki/Leszynski_naming_convention)
i have the following code and it works fine makes a xls called trash
now i want to be able to name the xls myself (via a form) form called xlsexport and the field i wil have to name it willb e called xlsname
is it a simple case of
objActiveWkb.Worksheets(1).SaveAs FileName:="c:[form]![xlsexport]![xlsname]- this doens't sound right to me ??
code below( yes theres some bits in there i need to tidy i know)
Private Sub Command0_Click()
Dim dbs As DAO.Database
Dim rstGetRecordSet As Recordset
Dim objXL As Object Dim objCreateWkb As Object Dim objActiveWkb As Object
Set dbs = CurrentDb Set objXL = CreateObject("Excel.Application") Set objCreateWkb = objXL.Workbooks.Add Set objActiveWkb = objXL.Application.ActiveWorkbook
Set objActiveWkb = Nothing Set objCreateWkb = Nothing Set objXL = Nothing rstGetRecordSet.Close dbs.Close Set rstGetRecordSet = Nothing Set dbs = Nothing
