Return PreStored Query Result Into Recordset Through Excel

Sep 12, 2013

I am trying to run a prestored query in a Access database and popuate the result into a Recordset --- through Excel. So far, I have managed to accomplish that using the code below. However, now I need to update some records in the recordset. The code below does not allow me to do so.

The following message prompted when the code tried to update the record: Run-time error '3251' Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.

This is written for other users who only knows how to create a query in Access. And the users prefer not to import the query results to spreadsheet.

*Someone suggested use Recordset.Open however, that seems to work only with SQL statement but not prestored Queries.

Code:
Sub RunExistingQuery()
'Execute prestored queries in Access
Dim Con As ADODB.Connection

[Code].....

View 2 Replies


ADVERTISEMENT

Excel 2010 :: ADO Recordset Query Quandary Locating Duplicates

Mar 13, 2013

I am having trouble setting up a query for a recordset that will list duplicates for an automated process to clean up. I am using Excel 2010 with Microsoft ActiveX Data Objects Recordset 6.0 Library and Microsoft ActiveX Data Objects 6.1 Library as references.

So far, I have chopped the query down to it's most basic elements and reassembled until it broke. Necessary pieces work well, but when I combine them, I get nowhere.

Here is the working simple query to retrieve a single row:

Code:
dupeSQL = "SELECT PSID FROM [Obstructed$] "

Here is the working single query to retrieve a count:

Code:
dupeSQL = "SELECT COUNT(PSID) As '" & "CountPSID" & "' " _
& "FROM [Obstructed$] "

When combined, I cannot figure out why it won't work:

Code:
dupeSQL = "SELECT [PSID], COUNT(PSID) As '" & "CountPSID" & "' " _
& "FROM [Obstructed$] "

Here is what I am trying to get to:

Code:
dupeSQL = "SELECT [PSID], COUNT(PSID) As '" & "CountPSID" & "' " _
& "FROM [Obstructed$] " _
& "WHERE COUNT(PSID) > 1 " _
& "GROUP BY PSID"

I can even work without the GROUP BY. I have also played with the HAVING clause to try and accommodate the [PSID] field, but have gotten nowhere.

View 1 Replies View Related

VBA Recordset Not Returning Updated Results From Query?

May 9, 2012

I have a function that loops through sheets and opens a query and copys the information to a sheet. I have gone in the access db and updated the query. Yet everytime i call it in excel it returns the old data. I have gone into access and verified what data I should get and I just don't get it.

I pass in the query name and the sheet name into qryName and sheetName name.

Code:
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sQRY As String
Dim strFilePath As String

[Code].....

View 1 Replies View Related

Excel Query - Calculating Result Based On Three Possible Factors?

Jan 9, 2013

I am working with data and I am trying to identify people that have moved departments using the following criteria, a person are classed as a mover if their:-

Manager AND Dept ID have changed

OR Job Code has changed

OR Status has changed

I have been trying to come up with a formula but just can't get one to work?

View 6 Replies View Related

Excel 2007 :: ODBC Connection Query Shows No Result

Mar 25, 2013

Why this sql query doesn't return values? My excel version is 2007.

Code:
if object_id( 'TEMPDB..#TMPDOC') is not null
begin
drop table #TMPDOC
end

create table #TMPDOC (Code varchar(5) NOT NULL)

[Code] ...........

View 6 Replies View Related

Excel 2007 :: Search And Return Result With 2 Criteria In Different Sheet?

Nov 7, 2013

I use the following formula in excel 2007 (and it's working perfectly in 2007) but this formula does not work if I work with the same file in excel 2003 ...w why and what I have to adjust?

=IF(SUMPRODUCT(--(C5='sheet 2'!B:B);--(C13='sheet 2'!C:C);ROW(B:B))=0;"not found";INDEX('sheet 2'!D:D;SUMPRODUCT(--(C5='sheet 2'!B:B);--(C13='sheet 2'!C:C);ROW(B:B));1))

(I'm working with the dutch excel version so it might be that ";" must be ",")

View 6 Replies View Related

Excel 2010 :: Return Result For Appearance Of Text String In Any Cell In Row?

Jan 30, 2014

I deal with a rather large excel database (the range is A1:AV168266) that contains customer information. Some of these various customers are affiliated with a group called "ascend" and I need to be able to filter all my list by every customer affilliated with Ascend. The trouble is that the word "ascend" can appear in 8 different columns and usually when it appears in one column it doesn't appear in the others and their can also be more text in the cell beyond the word "ascend". I want to create a formula that will simply look for the word "ascend" in any cell of a row and return a result I can filter by. I will be putting the formula in column AW and put it in all 170,000 rows of that column, that way I can just filter by a single column.

I've played around with combining COUNTIF with SEARCH and MATCH with SEARCH but nothing seems to work.

View 7 Replies View Related

Pass Parameter From Excel Through MS Query To MS Access Query

Nov 26, 2012

I have an MS Access query that contains a parameter. The parameter is a date field, and I have configured that in the Access query. If I run the query within the MS Access user interface, it prompts me for the paramater value as expected, and runs just fine. However, I want to connect to this query from within Excel as a data source.

I have created a connection to the Access file using ODBC from within Excel. In the MS Query window, I am merely selecting all of the fields resident in the MS Access query, and returning all values. In other words, there is no selection criteria in the MS Query. I have done this many times with Access queries that DO NOT contain a parameter, and everything works fine. However, in this instance, I need to pass a parameter through to MS Access in order for the query to run. At the moment, I get the "Too Few Paramaters...1 expected" error message. This makes sense, because I haven't figured out how to pass the paramater to MS Access.

Is there a way to structure this that does not involve VB code? If so, I'd love to know how. I have tried creating parameters in MS-Query with the same name, but although I get the prompt it doesn't connect with the Access query as the source for the parameter value.

If the solution requires using code, I'm good with VB Code in Excel...is there VB for Excel code that could make this happen?

Failing that, I guess there must be (I've seen a few in my search thus far) Access VB Code that can make this work. I'm very rusty using VB with Access, so this is my least favored solution. However, if this is the only option, keep in mind that I need to pass the paramater ultimately from a user who will initiate the process using Excel.

View 3 Replies View Related

Create SQL Query In VBA And Display Result As Table In Worksheet

Feb 3, 2010

I need the VBA script in excel that will do a SQL Query, for this case I need to select a value where there are 2-3 tags and between certain period then display the result as a table in a worksheet.

Example I have a database with 3 types of tags "Tag A", "Tag B", and "Tag C", and each tag have a value with different timestamp. the database looks like this:

No. Timestamp Tags Value
1 1-Jan-2010 Tag A 18
2 1-Jan-2010 Tag C 20
3 2-Jan-2010 Tag A 20
4 3-Jan-2010 Tag B 17
5 3-Jan-2010 Tag C 19
6 4-Jan-2010 Tag B 18
7 4-Jan-2010 Tag A 20
8 5-Jan-2010 Tag A 22
9 5-Jan-2010 Tag B 18
10 5-JAn-2010 Tag C 20

View 9 Replies View Related

MS Query - Parameters (parameters On A Query But Use A Wildcard To Return All Instances)

Nov 17, 2009

Is it possible to set parameters on a query but use a wildcard to return all instances? I have a query that I want to be able to set multiple parameters on but give the user the ability to select as many or as few parameters as they want to see. 2 of the parameters are number fields and 2 are text fields with no spaces.

View 4 Replies View Related

Sum If Query: Return A Value Where A % Is Used

Apr 19, 2006

I have been using the following formula to return a value where a % is used, is there anyway i can make the sum shorter.

=SUMIF(Q10,'Price Calculator'!$A$5,'Price Calculator'!B5)+SUMIF(Q10,'Price Calculator'!$A$6,'Price Calculator'!B6)+SUMIF(Q10,'Price Calculator'!$A$7,'Price Calculator'!B7)+SUMIF(Q10,'Price Calculator'!$A$8,'Price Calculator'!B8)+SUMIF(Q10,'Price Calculator'!$A$9,'Price Calculator'!B9)+SUMIF(Q10,'Price Calculator'!$A$10,'Price Calculator'!B10)+SUMIF(Q10,'Price Calculator'!$A$11,'Price Calculator'!B11)+SUMIF(Q10,'Price Calculator'!$A$12,'Price Calculator'!B12)+SUMIF(Q10,'Price Calculator'!$A$13,'Price Calculator'!B13)+SUMIF(Q10,'Price Calculator'!$A$14,'Price Calculator'!B14)+SUMIF(Q10,'Price Calculator'!$A$15,'Price Calculator'!B15)+SUMIF(Q10,'Price Calculator'!$A$16,'Price Calculator'!B16)+SUMIF(Q10,'Price Calculator'!$A$17,'Price Calculator'!B17)+SUMIF(Q10,'Price Calculator'!$A$18,'Price Calculator'!B18)+SUMIF(Q10,'Price Calculator'!$A$19,'Price Calculator'!B19)+SUMIF(Q10,'Price Calculator'!$A$20,'Price Calculator'!B20)+SUMIF(Q10,'Price Calculator'!$A$21,'Price Calculator'!$B$21)

Seems like a Pain to be using something this big, just cant seem to get it to work when i try to shorten it

View 2 Replies View Related

Macro Web Query Return Blank

Dec 19, 2006

The actual link from a list on the left side of that web page is "Printable List".
I can't work out why all other web pages work fine with;

Get External Data/Web Query, but this particular web page won't work.

Sub Test_A()

Sheets("test1").Select

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://acttab.com.au/interbet/racing?type=venues", Destination:= _
Range("A3"))
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With

End Sub

View 3 Replies View Related

Database Query Does Not Return Any Data

Mar 15, 2007

I am using the attached code however when my database query does not return any data I dont want the Dim function to perform.

Sub macro3()
Worksheets("Data 1"). Range("I3:BU65536").ClearContents
'declare a variable to hold date
Dim strDate As String
strDate = Format(CDate(Range("startDate").Value), "yyyy-mm-dd") & " " & FormatDateTime(CDate(Range("startDate").Value), vbLongTime)
Sheets("Data 1").Select
Range("A1").Select
With Selection.QueryTable
.Connection = _...........................

View 4 Replies View Related

Microsoft Query - Return All Unless A Parameter Is Entered

Aug 20, 2009

I am querying a database in Microsoft Query and I have it set to pull in jobs that match a customers code in cell A1. I would like it so that if cell A1 is blank, it pulls in jobs for every customer.

View 3 Replies View Related

How To Get Vlookup To Return The 3rd / 4th / 5th Ect Result

Dec 9, 2013

Been working on this problem for a while, I need to have a vlookup (or other function) return locations based off of a concatenate (for unique identifiers). Only issue is that the concatenate formula creates doubles which returns the same location for each vlookup. I have attached an example file.

View 2 Replies View Related

If Statement Return “W” Or “L” Result

Oct 14, 2008

creating a formula that will give me a result of either "W" or "L" depending on the amount in corresponding cell.

View 9 Replies View Related

Look Across Row And Return Result Down Corresponding Column

Dec 16, 2009

I need to figure out a formula that will look across a row and once it finds the criteria specified, it will return a value going down the corresponding column. I have attached a file showing the example.

View 3 Replies View Related

How To Return The Last Result In A List

Aug 5, 2008

I have this formula in C7:

=VLOOKUP(A7,A$20:C$76,3)

The Table Array contains a list of 12 months, and there will be a match to the Lookup Value in A7 each month. I would like C7 to reflect the last completed match in the list (it will be updated each month). So, when June is the last completed month, C7 will return the matching amount from the Table Array for June; then when July is completed, I would like C7 to reflect the matching amount for July.

View 9 Replies View Related

Lookup & Return Corresponding Result

Dec 6, 2006

I have tried searching for this topic and have been unsuccessful. Perhaps my search was too broad. Either way, I am trying to understand how to search/query a specific data entry among several sheets, and then return corresponding values from adjacent cells.

View 3 Replies View Related

Lookup Value & Return Corresponding Result

Dec 30, 2006

I created a drop down menu from a named data range on a seperate spreadsheet in the same workbook. Based on the selection from that drop down menu I want a preassigned number to pull into another cell. I'm not thinking of the correct formula or something, because it's not working. Could it be because it's text? My spreadsheet with the data is as follows:

Bridger to American Fork 051725
Hunter to American Fork 051725
Hunter to Delta 051715
Hunter to Hiawatha 051728
Navajo to Am. Fork w/ front 051725
Navajo to Am. Fork w/o front 051725

The 1st column is the origination/destination and I named that range "freight hauls". Which is what the dropdown menu on another sheet (AP Reconcilliation) consists of. From that, I want the 2nd column number to pull into another cell on the AP Recon sheet. The formula I wrote that isn't working is: =index('AP Reconciliation'!A2:B8,match(A6,'Freight Hauls List'!B2:B7,0),2)

View 3 Replies View Related

Query Worksheet & Return Rows Based On Conditions/Criteria

Jun 1, 2008

I am trying to query a names sheet where each row may contain more than one occurence of a member ID. There are no duplicate rows ( records), because the dates are different for all rows.

1. I want to retrieve all of the rows from the names sheet that match a unique member ID. So far I only get one row.

2. And if mistype the number, it gives me a wrong record .I would like to get an error message that if I don't get a match, that it appears in the somewhere like dialog box or even entry in one of the cells that no name exits.

View 4 Replies View Related

Four Variables - Value To Be Changed To Return Result

Jan 17, 2014

I have in the attached file four variables, with their values in B3:B6, and the results of applying a formula to them in B8.

How to determine which value variable C must be changed to so that the formula returns the result in B10?

Solution.xlsx‎

View 4 Replies View Related

Return A Result IF A Certain Value Is Found In Another Cell

Jul 2, 2009

I want to return a result IF a certain value is found in another cell. I've attached a sample workbook, but here's a description:

On the "Inventory" worksheet, in column H, I want to lookup the quantity in column B from the "Warehouse Response" worksheet. If the Part # in column E of the response sheet is NI1088106, I want THAT value to show under the NI1088106 part number, and I want the quantities for the NI1089106 to show under that part number. Ideally, the result of the formula would show a "0" if no data is found.

Here's the formula I tried beside the NI1088106 data, but it's not giving the desired results: =IF('Warehouse Response'!E:E="NI1088106", VLOOKUP(LEFT(A3,3),data,2,0))

View 3 Replies View Related

Return Result From Match Not In Index?

Nov 10, 2011

What I want to do is, given the data in col. a, I want to supply a number(range) of strings which can be found in column a, whenever a string is found, which exists in given range, I want to write the string from the Key(col. c) in col b instead of the value from the string that matches a value.

See below for example of what I got working but not exactly what I was looking for

Col. A- Data
Orangegreen
Blackwhite
Blueyellow

Col. B - Result

Col. C - Keys
Orange
White
Yellow

I used

Code:
=INDEX(A1:A3,MATCH("*"&C1&"*",A1:A3,0))

This does produce a result, but not entirely what I am after.

This requires only one string to search for, and will in cell B1 result in a value of 'Orangegreen' as I can only supply one valeu to the function. What I want to do is supply the function with a range of values, and have those values outputtet in colb instead af the value of cells where the result is found.

I tried modifying the function to include a range, but that does not produce a valid result.

View 5 Replies View Related

Return 1,2 Or 3 Based On Result Of Formula

Mar 3, 2007

Any other day but today (I've been starring at numbers too long) I could do this.

I need a simple formula that will return:

a. 3 if resut is >= $2.5M
b. 2 if over >= $0.5M
c. 1 if

View 9 Replies View Related

Lookup With Multiple Result Return

Aug 17, 2007

I have an excel worksheet that consists of 4 tabs. Each tabs has approximately 60,000 lines of data. The data consists of user id's and menus that correspond to the user id. There are 131 user id's in total. I would like to be able to return all the menus for one of the user id's. If we need to start with one tab and work from there, that's fine.

View 9 Replies View Related

IF Function To Return No Data As A Result

Nov 29, 2007

I've created a Database using excel which feeds data to a pivot table I created based on it. My objective is to create a new item in this pivot table (Pivot Table > Formulas > Calculated Item) which calculates the average data for the past 6 months.
The formula I used in order to get a result is this:

=IF(((May+June+July+August+September+October)/6)=0;;(May+June+July+August+September+October)/6)

and the problem is that the answer is 0 when there is no data for all 6 months (for example, there is a customer who hasn't baught anything during the past 6 months), the pivot table displays 0, instead of not showing this customer at all. My goal is for the IF function not to return any data if the result is 0, so that the pivot table doesn't show items with no data.
I also tried:
=IF(((May+June+July+August+September+October)/6)=0;"";(May+June+July+August+September+October)/6)

and also using ;" ";

but these formulas return #VALUE! in the pivot table, given that the Data is "SUM OF Sales" and "" is not a number.

How can I get the formula not to show anything at all?

In addition to this, I was wondering if it is possible to make this formula more automated, so that it calculates the average of the past 6 months, taking into account some type of reference month (rather than having to edit the formula each time a new month is analyzed).

View 9 Replies View Related

Loop Return The Result Back In Row

Sep 21, 2009

Col ACol BCol CCol DCol ECol F
row 1811TAX CONSIDERATIONS1TAX CONSIDERATIONS
row 1911TAX CONSIDERATIONS#N/A
row 2011TAX CONSIDERATIONS#N/A
row 2121FLYING FALCON1TAX CONSIDERATIONS
row 2221FLYING FALCON#N/A
row 2321FLYING FALCON#N/A
row 2431IN COMMAND1TAX CONSIDERATIONS
row 2531IN COMMAND#N/A
row 2631IN COMMAND#N/A

I have 5 coulmns of data and in col F I have the following formula in row 18
=VLOOKUP(E18,$C$18:$D$800,2,FALSE)
the results come back " TAX CONSIDERATIONS"
however the same results comes back in row 21 and row 24......how can I modify this lookup to change when column B changes

View 9 Replies View Related

Return Result & Formatting From Lookup

Sep 16, 2009

I have a production planning spreadsheet that is updated every week from a database to a new sheet. This sheet is 'Sheet1', last weeks update becomes 'Sheet2'. Every day people annotate and colour cells for various orders, depending on their place within the planning scheme. It's really only the formats that I need to copy across, I could use the VLOOKUP function for the values - So I suppose you could say I'm looking for a VLOOKUP macro/VBA Code which keeps the original formatting (cell colour etc.)

View 2 Replies View Related

Return Cell Below VLOOKUP Result

May 30, 2006

After using a vlookup to find a value in a cell I want it to return the cell belows information.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved