When I open a form, I want it to automatically check to see if there is are any updated files. I already have imported files into Access, but want to automatically check to see weather there have been any updates to the files.
Example
Form opens,
Checks the “C:MyDouments” for any updated Excel Spreadsheets (This could be done by the modify date and time),
Imports the updated Excel Spreadsheet into Access
End
If the code checks the Modify date and time of a excel file, how can I import these values into Access i.e. I could have some query that would check the date and time of an excel file against the date and time in the access table – and if different import the latest version.
I use a batch file to distribute the front end of a database application. Currently, everytime the user clicks the shortcut on her desktop, the batch file executes and copies the front end from a network location to user's local machine. The FE is updated like every couple of days. The users run the database more frequently.
I would like to modify the batch file so that it checks some attribute of the FE file to decide whether it needs to be updated. I can't rely on file size, since the FE includes temporary tables. I essentially need something like the "tag" property of form controls, only for files. It would be ideal if it was me who sets this property, like "version number". Except it has read without opening the file itself.
I have a table with several thousand records. Each record as a path field that indicates a specific file that is associated with a given record (not just a directory, it identifies a specific file). I'm pretty sure that most of them are correct but I've seen and been cleaning up paths that have had typos in them or were otherwise not setup correctly. Is there a way that I can generate a report or a list of some sort that would tell me exactly which file paths exist and which ones don't? or even just the ones that don't.
i'm trying to do something that i thought would be pretty simple. what happens is i have 2 files that get used, one by access and one by excel. they are both the same, except if one gets modified then the other doesnt obviously. (theres reasons for having the 2 files).
anyway. in access i want it to pick the newer of the two files (i assume this is using a fso system object?), if the one it normally doesnt use is newer, it should copy the new one over the old one and continue using the one it normally uses.
i cant simply use the other file as another user always has that file open.
any sample code on how to check if things are modifyed at a later date? and copy/pasting using those fso objects?
I have code working for some workers here ,who used to save excel files in a folder and my databse upload those files into database and then do the rest.I am looking for a code in vba which,on upload button matches the file in table and if the file already exists ,it should not upload again in the table and if it does not exists it should upload . I have attached my code below :
Private Sub Command0_Click() Dim strcPath As String strcPath = "O:QA FilesQC ReportingPending Review" Dim strcNewPath As String
I found and modified this code to allow me to check (fast) if a file exist on a http server or not (without downloading it).
Code: Public Function CheckNetFile(WebFile As String) Dim XmlHttpReq As Object Set XmlHttpReq = CreateObject("Microsoft.XMLHTTP") XmlHttpReq.Open "HEAD", WebFile, False
[Code] ....
In the above code I managed to declare Webfile as a string so I can call on it later but one thing I still need to tweak is to also get a result out of the test code in a better way than having to publically declare the "CheckNet" variable somewhere else in my app.
First prize would something like this:
If CheckNetFile ("http://www.mysite.co.za/test/test.txt") = 1 then "Yes" else "No"
How do I build the 'booleon check' into the original function?
halloo everybody Here is my code to check an excel sheet wether it is open or not. It is working good in the following situations.
1.if the excel file is not yet opened. then it is opening and bringing a value from some cell of the sheet1. 2.If the file is already opened and not yet closed. it is not opening again it is understanding that the file is already openrd and bringing the cell value. 3.It is working when it is closed by the user and again clicked the button to open. It is opening and bringing the value. 4.NOW the problem starts. IT IS NOT WORKING , If I click the button again to open. It is opening another copy of the same file. I did't understand why is it working perfectly? before I close the file and not working if I close the File.
please kindly check my code and respond to me . here is my code but it is not working when the file is already opened.
Private sub Cmd_Click()
Dim XL As New Excel.Application Dim wbk As New Excel.Workbook Dim ws As New Excel.Worksheet
WorkBookName = "Book2.xls" If Not WorkbookOpen(WorkBookName) Then chk = 1 Set wbk = XL.Workbooks.Open("C:Dokumente und EinstellungenKiran KarnatiDesktopEXCELBook2.xls") Else Set wbk = Workbooks(WorkBookName) End If
Set ws = wbk.Worksheets("Sheet1") If chk = 0 Then With ws Label48.Caption = .Cells(1, 2).Value .Cells(1, 3).Select End With Else With ws Text49.Value = .Cells(1, 2).Value End With End If XL.Visible = True
Set SA = Nothing Set XL = Nothing Set wbk = Nothing End Sub _________________________________________________
Function WorkbookOpen(WorkBookName As String) As Boolean 'Returns TRUE if the workbook is open WorkbookOpen = False On Error GoTo WorkBookNotOpen If Len(Excel.Workbooks(WorkBookName).Name) > 0 Then WorkbookOpen = True Exit Function End If
Actually what I am looking to do is have an Access application check some Web site for the presence of a number. This number could be the Serial number of the application and if this number exists to maybe show a message in the aplication or even terminate the application.
A quick and dirty way is for User Serial number 254, to put on a specific page of a Web site say arbitrary file 254.jpg (invisible on the Web site). The application would check for the existence of this file and if it is there, it could take some action. Everything would have to be done in stealth mode.
The following code can tell me if a specific URL exists, in this case [URL]
Code:
Private Sub cmdArbitray_Click() Dim blnDum As Boolean blnDum = blnCheckURL("http://www.VisualDentist.com") ' If blnDum = True then it exists End Sub Public Function blnCheckURL(ByVal strURL As String) As Boolean Const FLAG_ICC_FORCE_CONNECTION As Long = &H1 blnCheckURL = (InternetCheckConnection(strURL, FLAG_ICC_FORCE_CONNECTION, 0&) <> 0&) End Function
Dim objExcel As Excel.Application Set objExcel = New Excel.Application Dim strFileName As String strFileName = objExcel.Application.GetOpenFileName("Select CSV file, *.csv", , "CSV file")
If "FILE IS ALREADY OPEN" Then MsgBox "File is currently open. Please close and re-run this program.", vbInformation, "Program Error" GoTo end_: Else Set objworkbook = objExcel.Workbooks.Open(strFileName, , False) objExcel.Columns("A:B").Insert Shift:=xlToRight objExcel.Range("A5").FormulaR1C1 = "Account No" objExcel.Range("B5").FormulaR1C1 = "Invoice No" objExcel.Range("A6:A" & Range("C65536").End(xlUp).Row).FormulaR1C1 = Range("D1").Value objExcel.Range("B6:B" & Range("C65536").End(xlUp).Row).FormulaR1C1 = Range("F1").Value objExcel.Rows("1:4").Delete Shift:=xlUp
objExcel.Application.ActiveWorkbook.Close True MsgBox "CSV file formatted.", vbInformation End If
end_: Set objExcel = Nothing Set objworkbook = Nothing
Hi there,
I use the above code to open any CSV file, format it as required, then close/save it.
What I need to do is to find out if the file I have selected is already open and, if so, not to proceed with the code (see If "FILE NOT OPEN" line above).
I have tried several statements but am having no luck.
* I have a form that has a list box at the bottom of the form and all the fields (text boxes) displayed above. * As I select a record in the List Box it shows that record in the text boxes above and allows the user to edit that specific record. * I went one step futher and created drop down boxes out of the TextBoxes, which now reference a lookup table. * Although the users have a drop down box they can still enter a new entry.
My problem is that I want the lookup table to be updated with any new entrys that do not exist in the lookup table. I had this working at one point but cannot remember how or what I did.
Back at you guys with hopefully one more question (btw the command button code worked perfectly thanks)
Another thing my database has to be setup for is updates, Roughly 30 people will be using this database and to have them re-copy and paste the new database releases is out of the questions.
Does anyone have experience setting up code that automatically checks the version of the database created and if it differs downloads/installs the new version and the restarts the database so the associate will then be working in the current version?
I am looking to be pointed in the right direction to get this figured out, thanks.
Hi there,I know very little about Access but been asked to find out if this is possible:On a database we have a date field for when subscription is due, is there any way that if a DirectDebit method of payment has been chosen, when the date of renewal is, access will automatically update the year to the next one?Thank you very muchCathy
Have a bit of an odd problem with the DB im using at work. I have 1 DB on a shared drive that is accessed by 4 of us from our desktop PC's (password protected via 1 password that we all use - no separate logins).
The problem comes that we make changes to the data, exit via an exit button that uses a save all macro (that all works fine), however when we go back into to DB there are some times that the changes made have been lost and the data has reverted back to the previous version. There doesn't seem to be any specific pattern to this, it happens at random times and to random people.
Does anyone know what may be causing this, or anyway that i can prevent it happening?
I have a database that stores customer records and their associated insurance details. I need to be able to track any changes made to that record within a set time period ie if details are changed fine it is reported next time report is run. But if details are changed more than once in between reports then first detail change is lost.
I would like to work out a way that changes can be written to a ??seperate version of the table?? and stored in there to be reported seperately and not just the final change as is the snapshot nature of a report? Is this possible? Could I possible make another version of the main table appended onto it? I feel I 'know' what I need to do but not how to even begin it! Any help would be appreciated.
I have a time database which has a form to enter time. When entering time, The user must also select an item in a combo box that gives what category the user worked in. There are about 40 or so different categories. I want to know if it is possible to update the cobo box to the user's most recent selections. For example: if the user selects Management, which is at the bottom of the list, I would like to see Management at the top of the next list for the next time entry so that the user does not have to scroll down to the bottom again. Anyone have any ideas???
I have a work order entry form with a "Client" combo box. The Limit to List property is set to YES. If a client is not on the list, I get a prompt to create new client account in a different form. This all works fine.
But after I create a new client and get back to teh work order form, the client does not appear in the combo box unless I close the form and re-open it first. This is very annoying.
I have read all of the audit trail stuff and searched on my subject but I think my solution could be simpler,
All I want to do is copy the values of the current record on my form into a history table with all the same fields.
Example Form: [ID], [Machine], [Task], [Task Due], [Task Complete] I want to put a button on the form that reads complete task and when the user pushes the button it copies all of the details for that specific record they are looking at into my History Table that contains all of the same fields.
This will give me basic history on when each task was completed for a machine and I can access for reporting
I have a project that produces a roll report. To do this, I have two tables:
T1 (roughly 20,000 records) ID (Primary Key), PrevAmt, CurrAmt, Action
T2 (roughly 40,000 records) ID, Amt (linked Excel spreadsheet)
I then run 4 queries in sequence
Q1: Update T1 set PrevAmt=CurrAmt, CurrAmt=0; Q2: INSERT INTO T1 (ID, PrevAmt, CurrAmt) SELECT ID, 0, 0 FROM T2; /* keys that already exists will fail to insert)*/ Q3: Update T1 INNER JOIN T2 ON T1.ID = T2.ID SET T1.CurrAmt=T1.CurrAmt + T2.Amt; Q4: Update T1 SET Action = iif(PrevAmt=0 and CurrAmt<>0,"NEW",iif(PrevAmt<>0 and CurrAmt=0,"DROPPED",iif(PrevAmt<>CurrAmt,"CHANGED","UNCHANGED")));
T1 and the queries is in an Access file on a network drive. T2 is a link to an Excel sheet on a network drive.
When I run the queries from a PC on the local LAN, it takes about 2 minutes to update. I consider that acceptible. But I now have to run it from a PC attached to the network via a VPN over a somewhat slow DSL line. The queries take over 20 minutes to run. I want to cut that time down. But the Access file and the Excel file need to stay put as other users use them for other purposes. (Though they know not to access them till after they see my roll report in their in-box.)
What can I do to decrease the update time on this?
Hi there all i need to set a field on my form not to accept any updates to what ever was typed in the field before it was saved the first time kinda like After update event = no updates :confused:
How do I create a table relationship where a secondary reltated table stores past dates, status code and customer ID. For example: I want to keep track of the past dates that an order was revised and why it was revised. So, if I have a customer who was due for installation on 10/21/04 and status is PEND for pending. THen on 10/18/94 I change the date of PEND to 10/25/04. I want to keep track of the times it changed and the reason why. HOw can I make the program automatically create a record in the revision table storing the past code, reason and date using customer ID as key and teh revision table would allow multiple records for each customer ID. DId I make sense?
I am trying to come up with a way to update tables every week from two databases. I have one database for a satellite office and one for corporate. Each office has BOTH databases so as to keep up with their own data (we are not on a network). Each office will have to export their Project Table data to the other and then upload (update) the corresponding database. So:
ABC Division exports their project data from their Project Table once a week and sends to DEF Division and vice versa. Then ABC takes the exported Data from DEF and updates their database for DEF and vice versa.
Field names and additional field names will not happen; however, the data in those fields may be updated OR there may be new records added. So basically, I guess I need to delete the old and put in the new every week but make it VERY easy for the end users (who know nothing about Access). The tables that would be updated are related to other tables.
Does anyone have any suggestions where to get started? I've tried an Append query and that only adds records, not looks for changes in those records.
Ok I been at this most of the day and I have got no where fast.
I have a table with two fields [type] and [amount]
when populated I have something like this
hats 23 coats 33 shoes10 hats 18 coats 19 shoes 5
I need the individual sums of each of these records. for example shoes 15
I have created a query that does this for me. and I have put this information into a form. How do I get to the values to use else where. The form I created was a single form which shows coats 54 then by using the record navigation I get shoes 15. I think I need to pass these values into VBA but have not go a clue where to start.
I have a form with a number of drop-down lists on it and each of these lists is bound to a field in a table.
When I return the results of a search to this form I want to allow users to be able to cycle through the records and change them if they want. However, the changes should only be saved if the user clicks on the "update record button". This button is simply the save record button by the MS Access wizard.
At the moment if they change the value in one of the drop down lists and move onto the next record the change is saved even though the user doesn't click any button. I want to prevent this from happening.
Any ideas on how I might achieve this?
If I prevent "AllowEdits" in the form properties I can't update the form using the "update record button" either.
I have a table into which users insert records through a user form, I need to track the updates made in a record in a data set and i have a user log in function, it needs to tell me who updated last and what did they update.
