I have an application at a client site which is split into two mdb files, a front end (forms, queries, etc.) and a backend (tables, data only).I have a query involving multiple tables, which creates an Excel Spreadsheet. Sometime after installing it at client site, it no longer works because the entire contents of the query itself are wiped out -- totally empty. When I open up the query in design mode, it is empty -- no tables, no columns, no select blank, empty.
I'm at the end of a long project that has one last step. I was hoping that someone here would be generous enough to help. I'm pretty much a novice at Access and am sure that my question can be easily answered. So here is the problem.
I have data that is loaded from a PDA to a form in Access. The data can then be viewed in a list box which is great. I also need to be able to save the data from the list box, using a button, as an excel spreadsheet. Is there anyone here that is willing to guide me to a solution?? Thanks in advance -Tom
I am trying to export a table from access to excel. It has a couple of columns with times in and when excel opens it just shows 00/01/1900 for all the values.
Here is my query that creates the table for exporting:
Code: SELECT tblRoadClosuresAdditionalDates.Reference, tblRoadClosuresAdditionalDates.DateOfClosure, CStr(Format([TimeFrom],"Medium Time")) AS TimeFromMod, TimeValue([TimeTo]) AS TimeToMod, tblRoadClosuresAdditionalDates.NumberOfPeople INTO tmpAdditionDates FROM tblRoadClosuresAdditionalDates WHERE (((tblRoadClosuresAdditionalDates.Reference)=[Forms]![frmProgressRoadClosures]![sfrmRoadClosuresAdditionalDates].[Form]![Reference]));
You can see a couple of my attempts to handle this data above.
I am using this code to create my excel table.
Code: DoCmd.OpenQuery "qryRCADditionalDatesFilteredExport" DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "tmpAdditionDates", "xxxxxxxxxxxxxx", No
way to handle this field so that I get just the time in the medium time format appearing in the excel table.
I'm using Access 2010's DoCmd.OutputTo in VBA to export reports to PDF. The "look and feel" of the PDFs are very important, as they will be distributed to clients of my company. I'm using special corporate fonts that are legally licensed for embedding as a subset. Two are .ttf (TrueType) and two are .otf (OpenType) fonts. The ttf fonts embed fine; the otf fonts do not, and the PDF viewer substitutes something it thinks is close (but really isn't). In the properties of the fonts in Windows Control Panel, the embedding properties are exactly the same for both.
Any way to force the fonts to embed? or any other workaround? Also, is there any way to edit-protect PDFs with VBA code? Or apply any other type of PDF security such as requiring a password to open?
I am trying to create a saved export on a table i have. However i want to filter so when i do the saved export it exports the table with the filtered result only.However when i filter my table and export it and save the export. The first export comes out with the filtered results but when i go to my list of saved exports and export it again, it doesnt come filtered and its just an export of the whole table.
We have small data dumps from a webservice delivered to us daily in csv format. I'd like to create a system where we can have the data automatically imported into either Access.
My idea is this:
1. CSV is downloaded to <x> folder.
2. Scheduled Task runs script to look in folder <x>
3. If CSV is found in folder <x>, import it's data into a fixed Access db->table.
4. Move CSV to an archive folder to avoid re-processing.
Is this feasible? How complicated would it be, and how to get it on track?
I have created user defined function for lastdayofweek, lastdayofmonth, and firstdayofmonth. When I apply this function to an expression in my query, it returns a julian date. How do I apply the format for the calendar date?
Maybe I am not calling my function correctly. Here's my functions below as well as my expression..
Function LastDayThisMonth() LastDayThisMonth = DateSerial(Year(Date), Month(Date) + 1, 0) End Function
Function FirstDayThisMonth() FirstDayThisMonth = DateSerial(Year(Date), Month(Date), 1) End Function
I'm merging a few databases together. They're fairly simple on their own but I'd like them as one big database. I read that I should import each access database into a new, blank one.
It all works fine, but none of the saved imports and exports come with them. Unfortunately, these imports and exports are relied upon quite heavily.
Is there a way to bring saved imports over from other databases, or will I have to rebuild each one?
I currently require a macro that takes the record and when clicked, it opens a form and displays this record. This is so that I can use it to click buttons and open existing reports based on the data and field that match the 'clicked' record.
I have to join multiple values into a string for summarizing data on reports and exports. This process in vba is taking up to 10 minutes to process and will get worse as the size of these reports grow.
My method so far is to query the individual items into a recordset, loop through the values, adding them to the string then return the string in the query.
Here is an example:
Public Function SO_Description(intSO As Integer) As String Dim db As DAO.Database Dim rs As DAO.Recordset Dim strSQLSelect As String SO_Description = "Profiles: " Set db = CurrentDb
I have an excel spreadsheet with 8 tabs. They are all in the same format and column order. They are employees grouped by region. My ultimate goal is to merge all of these onto one excel tab, relatively instantly. I created a master tab and tried doing array formulas and Vlookups, it worked but my spreadsheet was way too slow.
My solution? Import and link them to an Access database, step complete. Create an XML export then import into Excel.
My problem? The only way to update the excel tab with the combined tabs is to save the excel file after changes, go back into Access, re-export to XML, then go back into excel and refresh the data.
My questions, is there any way to automate this process to the point that I can change excel, save, then hit refresh on my excel tab with the XML import to auto-update?
I am wondering if there is a quicker way to export a query to excel then have the data in that query removed from the original table. (effectively cutting the data from the table and exporting to excel)
I understand that this can be done by exporting the query to excel then running the same query as a delete query to remove the data but I just wondered if this is the most efficient way.
I have experience of VB in excel but currently only use the basic macro builder in Access though if Access VB is more efficient I can easily learn.
on my query there's a colunm which contain based on two other column. so is it posible using if function like on excel or i have to use module to do it? i've read iif function but still dont understand.
what i need to do is; if column A contain 1 and column B contain 2A, column C will be filled with "so close". if column A contain 3 and column B contain 2B, column C will be filled with "near". and so on.
on my query there's a colunm which contain based on two other column. so is it posible using if function like on excel or i have to use module to do it? i've read iif function but still dont understand.
what i need to do is; if column A contain 1 and column B contain 2A, column C will be filled with "so close". if column A contain 3 and column B contain 2B, column C will be filled with "near". and so on.
I have a query in Access which selects records with a date value in between 2 dates from a field called 'Period', e.g. 01 Jan 07 to 01 Apr 07 (>="01 Jan 07" AND <="01 Apr 07") which returns all the records I would expect, i.e all records between 01 Jan 07 and 01 Apr 07.
The 'Period' data/values are put together using 2 fields strMonth e.g. Jan, Feb, Mar, etc strYear e.g. 2006, 2007, etc and built using the expressionPeriod: CDate("01 " & Trim([tblBookIt]![strMonth]) & " " & [tblBookIt]![strYear]) returning a valid date value for each record returned in the query
When I run this query using 'Get External Data' in Excel using the Query Wizard, the Filter Data option doesn't give me all the period values that are on the table. I have 2 years of data in the table, Jan 06 to Feb 08, each record having a Month (strMonth) and Year (strYear) field but running the query in Excel only gives me the option to select Mar 07 to Feb 08.
I am struggling to work out why when using Excel to run the query it seems to be ignoring all data with a date value prior to 01 Mar 07. I know this query is very specefic to my database which you don't have access to, but am hoping someone is aware of what I may be doing wrong.
Please would someone be able to advise me how I could link an Access Query to an Excel spreadsheet. Also, is it possible for the Excel spreadsheet to be 'updated' automatically when the data in the Access Query is updated ?
I've been searching for a good solution for this and can't find it. Hopefully something like this is possible.
I need to produce a report in excel from my database that needs to include information from 5 different tables. The relationship between two of these tables is a many-to-many relationship connected by a junction table. (I hope that is the right terminology)
My report needs to be in a certain format and needs to include all fields in the same order for every record.
The problem arises because of the many-to-many relationship. I have a tblOrder and a tblAgent linked by the junction table tblAgentOrder. For every order there can between 1 and four different agents associated with the order which are stored in the junction table. In addition these agents will be of different types 1-4.
Ex: One order could have an agent of type 1 and an agent of type 4. While another order may only have one agent of type 2.
I need the query to give me the result of OrderID, DateOpened, AgentType1, AgentType2, AgentType3, AgentType4. Regardless of if the order actually had an AgentType3 or 4 associated with it. In this case the fields would be blank.
Right now I'm getting: OrderID, DateOpened, AgentType1 OrderID, DateOpened, AgentType2
I hope this makes sense, but let me know if I need to clarify anything.
I have a form that allows users to specify the information they want to be displayed on a subform. I have used the below code (simpilified for example purposes) to create the SQL string and sent it to the subform for display. I would like to have an export to excel button to export the recordSource to a spredsheet. is this possible?
Hi all, when exporting a query to Excel is it possible to perform page set up controls to the Excel sheet? Or will I need to create VBA page set up controls with in a Set objXL = New Excel.Application??:confused: :confused: :confused:
I have a query in access 2002 named "FMC DETAIL / MEMBER" when i export to excel 2002 with named "FMC DETAIL_member" the export says "Path is invalid"
My main point is that "/" should be replace with "_" in file name automatically. In access 97 query named "FMC DETAIL / MEMBER" when export to excel the "/" is renamed to "_" automatically.
I have the attached DB. On the form I have a button which will send the results of the Access Query2 to an Excel spreadsheet-OK, this works and no problem.
But what I would like to do is push the button and it will send the results of the Access Query2 to an existing spreadsheet called caseloadmgt.xls which is located on my desktop - C:Documents and SettingsJim.BlackDesktopcaseloadmgt.xls And the next time I push the button to run the query again I want the new data to start 2 cells below the ending of the first query results in the spreadsheet.
I have tried to follow all the examples I could find and I guess I just don’t know enough about this to make it work. What I need is someone to correct/ or change my code and put the new/corrected code in the DB where it should go
Hi! I know queries can be exported to excel by the option "File > Export".
The fact is that I'm using "order by" options and some functions like "Month", and "Export" options appears disabled, is there a way to export this query to Excel? :confused:
Just wondering, is it possible to have an excel file, which is a export data from an Access db query, and whenever I open this excel file, it'll automaticallly run the query and get the most updated data? If so, What do I need to do in order to link the excel file to the query?
I have a button on a form and when I press it I would like this to happen: Some code will be run that makes a copy of an templatefile (Excel), puts the values that comes from my query into the document and the saves it with a name that comes from two textboxes. I would also like to be able to put in some other text in the document, like two dates that I have on my form. Does anyone know how to do this ???
I've got my application working wonderfully and now have set up some reports in Excel. People want to be able to manipulate the data and I figured this was easiest...the users don't know how to create their own report within Access but are Excel literate.
My workbook has two queries (setup using Microsoft Query) back to the database...the data is returned to the workbook and then sorted within the workbook using Excel formulas.
This works fine all data is present and correct and the results are accurate.
HERE'S THE PROBLEM
My database keeps corrupting. The LDB file isn't being deleted after the last person exits the database. I downloaded the LDBUTILS from Microsoft and found that when a person accesses the database via the Excel workbooks the session is left in a suspect state and this is causing the corruption.
I know it's not a server permissions error because I created the folder and am the owner of the file. I have full control of the files and folder on that part of the server yet my own PC frequently leaves the database in a suspect state.
Does anybody know what I can do to stop this? Is there a piece of code I can add so that when the workbook is closed the database session is also ended cleanly?
In Excel I have a sheet with about 3000 rows. Each row has an id. I need to retrieve a field from an Access database for each id in the Excel file.
I know how to query the Access database from Excel, but I don't know how to set the query to use the id's in the Excel sheet instead of an Access table.
