Saving A Query For Export
Jun 14, 2005
Oper sys is XP. Office 2000 version of Access. Extensive experience with Excel but very little with Access.
I'm creating an astronomical database that includes a function that calculates the distance to galaxies in millions of light years. These databases are massive. Total database has about 1.6 million galaxies (100 Meg). My query concerns about 500,000 of these.
Have the original database and done the query on the objects of interest. It includes the cacluations. All perfect.
Now, it is possible to save just the query so that I can copy it onto a CD and take to the observatory for field reference. Query has about 500,000 lines, one for each galaxy, but is only six columns wide. But at 500k lines it cannot be exported into Excel. Or at least I don't know how to do that.
Thanks for your assistance
View Replies
ADVERTISEMENT
Mar 8, 2014
I have some code below that exports a report to PDF.
It works fine, I just want to know if it's possible to export the PDF without saving it? I can't seem to find anything on my search about it.
Code:
'Open Report From Query
DoCmd.OpenReport "rpt_BLT_PrintReport", acViewPreview, Me.Filter
'Output Form to PDF
Dim strUserName As String, strPath As String
strUserName = Environ("username")
strPath = "C:Users" & strUserName & "DocumentsPoliceReport.pdf"
DoCmd.OutputTo acOutputReport, "rpt_BLT_PrintReport", acFormatPDF, strPath, True
'Close Report
DoCmd.Close acReport, "rpt_BLT_PrintReport"
View 4 Replies
View Related
Nov 4, 2013
MS Office 2003..I have 7 queries that I use to export data to one excel workbook. Inside the workbook are 7 worksheets, one for each query. I have it working but I dont know how to save the exports steps like I did in AC2010.
I export the data by right clicking on each query and select export , then I browse to the workbook and because each query is named differently , it names the worksheet. I have to run these 7 queries each month , so I would like to save the steps and then build a macro that would run all seven steps at the push of a button.
I have saved the steps on a PC running MS office 2010 and the macro works great. The problem is this resides on a PC that cant be upgraded and I need to make it work on Access 2003 .
View 1 Replies
View Related
Mar 4, 2006
Hi...
I am relatively new to access.
I have spent a few days coding a database search page consisting of various comboboxes and textboxes. the user can select or enter data in these and when he/she clicks the search button an SQL select statement is generated on the fly and updates an existing query and this is used to query the database. I want to be able to give the user the option to save the query he/she generates and reuse it. How can I prompt him for a name and turn the SQL string strSQL that he has generated into a query and save it for later use?
Any help greatly appreciated..thanks
View 4 Replies
View Related
Apr 28, 2006
Hi there
I've got an access file which was probably created with access 97 or 2000, and inside a query that I know for a fact works with SQL Server 7
I was upgrading to SQL Server 8 and I needed to modify that query because it didn't work with the new version.
Now, when I tried to save the query, it gave me a certain error about invalid JOIN statements.
The query I wrote runs perfectly when I try to execute it manually on the SQL server, but MS Access just won't allow me to save it!
Any ideas how I can make it save my new query without it trying to verify my SQL statement?
Thanks
View 3 Replies
View Related
Apr 28, 2006
Hi there
I've got an access file which was probably created with access 97 or 2000, and inside a query that I know for a fact works with SQL Server 7
I was upgrading to SQL Server 8 and I needed to modify that query because it didn't work with the new version.
Now, when I tried to save the query, it gave me a certain error about invalid JOIN statements.
The query I wrote runs perfectly when I try to execute it manually on the SQL server, but MS Access just won't allow me to save it!
Any ideas how I can make it save my new query without it trying to verify my SQL statement?
Thanks
View 3 Replies
View Related
Jun 23, 2005
Does anyone know how I can save the results of my crosstab query into a new table (like a Make Table query does)?
Any help would be greatly appreciated!
View 2 Replies
View Related
Mar 18, 2008
Ive created a query that asks the user to enter data (month and year) before it can be processed. This is causing a problem for me as i need to produce a chart from this data and access isnt letting me (access doesnt like me asking the user to enter data). So i thought if the data could be stored in a table then the chart could be drawn from the table.is this possible.Below is a screengrab of the design view fro the query.
http://i30.photobucket.com/albums/c329/oohmygod831/screengrab-1.jpg
and this is the sql for the query
SELECT Shift.Shift_ID, Shift.Shift_Type, Sum(Main.Shift) AS SumOfShift, Format([Date],"mmmm") AS Expr3, Year([Date]) AS Expr4
FROM Shift INNER JOIN Main ON Shift.Shift_ID = Main.Shift
WHERE (((Month([date]))=[Month]) AND ((Year([date]))=[Year]))
GROUP BY Shift.Shift_ID, Shift.Shift_Type, Format([Date],"mmmm"), Year([Date]);
View 2 Replies
View Related
Jun 14, 2005
Hi all,
I've got this form working ok but need to add some extra functionality but haven't a clue how to do it!
http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=10175
The uploaded database has two main forms. The first is used to enter customer data, the second runs from a query and shows all those records that have been completed in the first form (frmCustInfo - AgentLog = Yes) but not completed in the second form (frmControlsSource - AnalystLog = No)
Then more data is input into one of the subforms (frmControlChecks). Now in order for me to see the results of the queries connected to two other subforms I have to move off the record and back to it (for the record to be saved and then the queries run)
After moving back to the record, the query results on the right (Information Only) are manually added to the last subform 'Input Two', then the user would move on to the next record and do the same.
-------------------------------------------------------------------------------------------------------------
What I need it to do then is this: When the user tabs off the last field in the subform (frmControlChecks), the queries to the right (two subforms) would run, showing the results and also populating the last subform (frmAnalystInput)
The user would then click AnalystLog and move to the next record.
I hope this is possible without redoing the forms/queries because it's taken ages to get this far
I've also added info in this sample database and some working data if you want to take a look...thanks for any help
View 1 Replies
View Related
Jun 21, 2006
I have created a query of 382 records. After filtering I now have a result set of 32 records, which I want to save as another query.
When I select Save As and rename, I get my original 382 record query with a new name.
How do I save the 32 record result set?
View 1 Replies
View Related
May 21, 2013
I have an simple Access 2001 database with one table in which I want to search multiple fields for entries which match upto four keywords using subqueries.I have entered the following sql code:
SELECT Components.[Component-type], Components.Value
FROM (SELECT Components.[Component-type], Components.Value
FROM (SELECT Components.[Component-type], Components.Value
FROM (SELECT Components.[Component-type], Components.Value
FROM Components
[code]....
and then refuses to save it giving the error: "Invalid bracketing of name 'SELECT Components.[Component-type"If I remove the changes it will save ok. This is only a test query as I will want to add many more fields and it is my first use of sql code.
View 8 Replies
View Related
Aug 20, 2014
I am new to Access and I have get to where the users can make selections in the listboxes, but then I need to save the selection in a query or table to save thier changes.
View 1 Replies
View Related
Jan 28, 2013
I am in the process of making a database and basically I have everything done but this saving, loading and adding/removing part.
Would it be possible for me to allow end-users to run queries that they could save, load, add to the existing table or remove from the existing table?
When I say Add/Remove what I mean is could a user of the database run a search of every customer in the state and then remove everyone in County X or on the other hand could they run a search of every customer in the state and then add every customer in a neighboring state.
Kinda like layers and again I have all of the criteria set up I just need the Save, Load and Add/Remove buttons on the form. I should add that I do not want users to save over the main table I want them to save it as there own text file they can upload later.
View 14 Replies
View Related
Aug 28, 2007
I am trying to create a table to show the parent/child relationships between assemblies, sub-assemblies and detail parts. The table I start with has 2 fields a "Part Number" and an "Indenture".
Please forgive all the periods, couldnt think of how else to seperate the values to depict the tables.
Example:
IND..PN
00....A
01....B
01....C
02....D
03....E
03....F
01....G
00....H
01....I
02....J
I can get the first step to completeing the new table of parent/child relationship like this:
PN....00....01....02....03
.A.....A
.B....."".....B
C......"".....C
D......"".....""......D
E......"".....""......"".....E
F......"".....""......"".....F
G......"".....""......""....G
H......H
I......""......I
J......"".....""......J
Where "" are null values
My problem is making it so that every part number has its own array, aka [family tree], so that for part "F" the table would hold "A,C,D,F"
I though an update query where for example:
*"Zero" is a single field string.
IIF([00]<>null(), Save [00] to Zero, Write Zero to [00])
Would work but have been unable to find any information about passing a field value to a variable for later use or comparison. I would have already done this in excel if the table had less that 65000 records.
Example of final table:
PN....00....01....02....03
.A.....A
.B.....A.....B
C......A.....C
D......A.....C......D
E......A.....C......D.....E
F......A.....C......D.....F
G......A.....C......D....G
H......H
I.......H......I
J.......H......I......J
Any help is greatly appreciated.
BsK
View 1 Replies
View Related
Jun 5, 2013
I have a form with a child that holds a query and for 99.9% of the time remains locked as it's for viewing data only. However, if the user wishes to make a change then they need to press an unlock button first then make the changes.
The problem I have is when closing the form; if this child's query has been unlocked I'm getting a message asking do I wish to save the changes to the layout of the child's query. The answer will always be "No".
I've added the following line of code to all sorts of procedures with no success.
DoCmd.Close acQuery, "LinkTableManager_Frmqry", acSaveNo
how to stop the message appearing when the form is closed.
View 5 Replies
View Related
Feb 26, 2014
I have a query that reports the number of students that are currently enrolled in a given number of schools within an area for that given day that the query is ran. This number can fluctuate greatly day to day, so is there a way to create a table which can hold this information so that I can go back in history and look up what our numbers were on whatever given day (date). (The example below is just a representation of what I actually have.) The School number would be unique, as would the date (I assume), I would like to see all of our school on whatever given date to see what our numbers are or were.
I have an example of the query currently I have set up in access 2007:
School # School Name Qty Date
001 BLUE 300 02/26/2014
002 RED 468 02/26/2014
003 YELLOW 10 02/26/2014
VS
School # School Name Qty Date
001 BLUE 310 02/27/2014
002 RED 400 02/27/2014
003 YELLOW 8 02/27/2014
View 3 Replies
View Related
Nov 20, 2013
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.
View 5 Replies
View Related
May 10, 2007
Hello,
Does anyone knows how can I export the export/import specifications (which file/directory) and how can I import/export the specifications between different versions of Access. Thanks!
View 3 Replies
View Related
May 6, 2015
I have a BE database, that when opened, opens a form for saving the results of a query to a text file on the desktop. It works fine, if the full path is entered.
The problem is, I want this saved on any users' desktop. I did some digging and found the %userprofile% variable, which when used, gives me the error.
I understand this should work in both Windows XP and Windows 7, which are the environments the full DB will operate in. So far the "EXPORT" button on the form has the following for the code:
Code:
Private Sub BTN_Export_Click()
DoCmd.TransferText acExportDelim, , "QRY_ExportPublicComment", "C:UsersMark N. McAllisterDesktopPubComExp.txt"
End Sub
When I tried this:
Code:
Private Sub BTN_Export_Click()
Dim strPath As String
strPath ="%userprofile%desktopPubComExp.txt"
DoCmd.TransferText acExportDelim, , "QRY_ExportPublicComment", strPath
End Sub
the error occurs.
View 10 Replies
View Related
Mar 16, 2006
When I export a query to xml (MS Access) then the name of the query equals
the tagname in xml. Example: when the queryname is ‘qryRegions’, the xml
result is:
<?xml version="1.0" encoding="UTF-8"?>
<dataroot xmlns:od="urn:schemas-microsoft-com:officedata"
generated="2006-03-04T14:32:33">
<qryRegions>
<City>Woonplaats</City>
<ContactName>Naam</ContactName>
<Profession>Beroep</Profession>
<Phone>012-3456789</Phone>
<Province>Flevoland</Province>
</qryRegions>
</dataroot>
What I want to do is selecting one of the childNodes as firstChild e.g.:
<dataroot>
<Province>
<City>Woonplaats</City>
<ContactName>Naam</ContactName>
<Profession>Beroep</Profession>
<Phone>012-3456789</Phone>
</Province>
</dataroot>
I can’t find a way to manage this. Any suggestions??
View 4 Replies
View Related
Dec 3, 2007
Ok forgive me I'm a newb. I curently have a query in access that I'm trying to make a button to automatically send the results of this query to excell. When I run the code (below) it gives me a compile error saying user defined type not found on the highlighted portion
Private Sub ExporttoX_Click()
On Error GoTo Err_ExporttoX_Click
Dim db As DAO.Database
Dim rs As DAO.Recordset
'Set db = DAO.DBEngine.Workspaces(0).OpenDatabase( _
"C:database.mdb")
Set db = CurrentDb
Set rs = db.OpenRecordset("qbpexcel", dbOpenSnapshot)
'Start a new workbook in Excel
Dim oApp As New Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Set oBook = oApp.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
'Add the field names in row 1
Dim i As Integer
Dim iNumCols As Integer
iNumCols = rs.Fields.Count
For i = 1 To iNumCols
oSheet.Cells(1, i).Value = rs.Fields(i - 1).Name
Next
'Add the data starting at cell A2
oSheet.Range("A2").CopyFromRecordset rs
'Format the header row as bold and autofit the columns
With oSheet.Range("a1").Resize(1, iNumCols)
.Font.Bold = True
.EntireColumn.AutoFit
End With
oApp.Visible = True
oApp.UserControl = True
'Close the Database and Recordset
rs.Close
db.Close
End Sub
Any help would be apreaciated
View 1 Replies
View Related
Jan 28, 2008
didn't know whether this would be a macro or module or what
right now I have a query that I right click on and go to export then I change it to Text. I then set it to Fixed Width and then under advanced i set field information i changed the width from 255 to 181 but keep start 1 and field name "Expr1".
i would like a macro or code that would automate this whole process for me.
thanks,
QR
View 2 Replies
View Related
Oct 9, 2007
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.
Any Help would be appreciated.........
View 1 Replies
View Related
Jan 28, 2008
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:
View 2 Replies
View Related
Dec 2, 2004
Hi there,
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 ???
View 14 Replies
View Related
Jul 26, 2005
I just want to export a query table manually to a tab delimited file. I choose text try setting the file extension to .tab as well and I wind up with some sort of fixed width file.
I know this because when I open it in Excel it has the | symbos and so forth.
It's as if it is calling for an export spec, but I'm not aware of needing to do this when exporiting manually.
It seems like I have done this numrous times without this problem.
Any ideas? Thanks
View 1 Replies
View Related
