External Data

[eTom]

I am trying to export data from Access to Excel, so I can use it to populate a spreadsheet that we use as an in-house form so I can save myself the step of manually entering data.

In Excel 2007, I can connect to my database using "Get External Data" but it only lists tables and *some* of my queries. The two that I created specifically for this task aren't listed.

Is there an option or a toggle I have to switch in order for a query to be usable by Excel?

 

[DCrake]

It depends on the type of query you have created. Is it a simple select query? not an action query?

 

[eTom]

I'm not entirely sure. I based it on a query I'd built in VBS (that gets built based on some selected values).

I'm not very proficient with SQL or Access yet, so what I did was copy/pasted the SQL code to a new query and then used the "design" Mode to add in a few fields from other tables. The code now looks like this:

SELECT tblIncidents.*, tblFormulas.FormulaName, tblProducts.PackedSize, tblIncidentDetails.LotNumber, tblIncidentDetails.Quantity, tblEmployees.FirstName, tblCountries.Country, tblProvinces.ProvinceAbbreviation, tblStores.StoreName, tblDistributors.DistributorName
FROM ((tblCountries INNER JOIN ((tblFormulas INNER JOIN tblProducts ON tblFormulas.FormulaID = tblProducts.FormulaID) INNER JOIN ((tblEmployees INNER JOIN (tblDistributors INNER JOIN tblIncidents ON tblDistributors.DistributorID = tblIncidents.DistributorID) ON (tblEmployees.EmployeeID = tblIncidents.EmployeeID) AND (tblEmployees.EmployeeID = tblDistributors.EmployeeID)) INNER JOIN tblIncidentDetails ON tblIncidents.IncidentReportID = tblIncidentDetails.IncidentReportID) ON tblProducts.ItemNumberID = tblIncidentDetails.ItemNumberID) ON (tblCountries.CountryID = tblIncidents.CountryID) AND (tblCountries.CountryID = tblDistributors.CountryID)) INNER JOIN tblProvinces ON (tblProvinces.ProvinceID = tblIncidents.ProvinceID) AND (tblProvinces.ProvinceID = tblDistributors.ProvinceID) AND (tblCountries.CountryID = tblProvinces.CountryID)) INNER JOIN tblStores ON (tblStores.StoreID = tblIncidents.StoreID) AND (tblProvinces.ProvinceID = tblStores.ProvinceID) AND (tblCountries.CountryID = tblStores.CountryID)
WHERE (((tblIncidents.IncidentReportID)=([Forms]![SearchParameters]![SearchInputBox])))
ORDER BY tblIncidents.DateSubmitted DESC;

You'll notice at the end of the SQL that it even still uses the WHERE statement and [SearchInputBox] control from the Visual Basic that I used to search records based on a the value of a text box. I left it in because when you run the query it forces you to input a value (the value being the specific record number that I'm looking for!)