I have a routine in Excel, which pulls data in from a SQL Server database into two ADO Recordsets using two seperate queries.
These recordsets contain only 1 field, being customer ID, each with in excess of 65,000 records. I need to compare these two recordsets to establish how many customer IDs are present in one, but not the other and vice versa. Is there some clever way of looping I could use to achieve this The only other thing I can think of is a local query within my module on the two datasets, but is this possible
Cheers for any help!
Keith
ADO Recordsets
Ajay_1981
Hi KeithyBoy,
You could write a function that does the search but it would be better to get the relevant data from SQL Server so you wouldn't need to. Is there any way you could query the tables in SQL Server, or create a view, or stored procedure in SQL Server to return what you need
hazz
Hello again KeithyBoy,
Is your IT department a bit slow to respond as well is it. I completely understand it's like that here, I just feel that your having to do a lot of work by looping over the recordsets when you could simplify the problem using SQL queries at the server. If you want I'll write you a stored procedure script that will do what you need. I need information on table names, the fields, and the relationships between the tables so I can recreate them here. If you don't want that then thats ok.
There is no way to run a query over your two recordsets because there needs to be a relationship between the data. And recordsets are very much individual snapshots of data. I've never heard or seen code that joins two recordsets and queries them. You could drop the data into local tables and run the queries you need on these. Could be more hassle than it's worth.
The other option is just to loop over one recordset (a) and filter the other recordset (b) with the value of the current record in (a). If there are no records then store the value in (a), once you do that repeat the process for (b) filtering on (a) if there are no records and the value hasn't been previously stored then store the value in (b).
You really need to go with the store procedure in my opinion. It just makes sense.
Edit: There might be another solution, I'll try it out and post back.
Kirk Evans
Well, I'm coming at this from a DAO point of view, because that's what I've got the most experience with, but I don't see why you couldn't do what you need in VBA.
Assuming you have to do all your work locally using nothing more than snapshots of the tables, I'd do something like this:
1) Build an SQL query to select ID and the text 'In Table1' into a temporary table from all records where [Table1]![ID] isn't found in [Table2]![ID]
2) Build another query to do the same for records in Table2.
3) Merge the 2 tables and process the resulting records as necessary
At least with DAO, it's a snap to create new queries using the QueryDef class, and to access the resulting data using RecordSet objects.
Greenies
Just read your blog. Superb approach, which will doubtless prove useful! I've saved it to my favourites
I love this programming lark!
Dmitry Medvedev
Cheers for that! Is there a private messaging facility on the forum which I can use to send you my email
The only thing I can think of that may put a spanner in the works is the number of records in my recordset objects exceeds an Excel spreadsheet. In any case, I'd be very interested purely for increasing my knowledge!
Thanks again, chap!
zenzai
I found a solution. I don't know why I didn't think of this before, because I've done it in other routines. I've simply written some SQL that is a query inside a query. The main query has a condition in the WHERE clause: Not In(). Within the Not In statement is the second query (in blue), see below:
Cmd1.ActiveConnection = Conn1
Cmd1.CommandText = _
"SELECT tblCustomers.CustomerID " & _
"FROM tblOrders INNER JOIN tblCustomers ON tblOrders.CustomerID = tblCustomers.CustomerID " & _
"WHERE tblOrders.OrderDate >= '" & Format(DateAdd("m", -1, StartDate), "yyyy-mm-dd") & "' And " & _
"tblOrders.OrderDate < '" & Format(DateAdd("m", -1, EndDate), "yyyy-mm-dd") & "' And " & _
"tblOrders.OrderTotal >= 3 And tblCustomers.CustomerID Not In(SELECT tblCustomers.CustomerID " & _
"FROM tblOrders INNER JOIN tblCustomers ON " & _
"tblOrders.CustomerID = tblCustomers.CustomerID " & _
"WHERE tblOrders.OrderDate >= '" & Format(StartDate, "yyyy-mm-dd") & "' And " & _
"tblOrders.OrderDate < '" & Format(EndDate, "yyyy-mm-dd") & "' And " & _
"tblOrders.OrderTotal >= 3 " & _
"GROUP BY tblCustomers.CustomerID) " & _
"GROUP BY tblCustomers.CustomerID"
Cmd1.CommandTimeout = 100
Cmd1.Parameters.Refresh
I love it when a plan comes together as Hannibal says! LOL!
I'll take a look at your blog when I get a mo! Once again, thanks for all the help on here!!
Teradar
Hi man,
No worries about the stored procedures, your quite right, you don't know who's watching. I don't get much oppertunity to write stored procs so when there's an oppertunity I go for it.
I treat recordsets like they are a table/view, you can filter and search them but you can't do anything relational with them at all. .NET is different, there is something new coming out called LINQ that lets you write SQL statements as code.... its very smart.
I do have a rather nice solution for you thats similar to dumping the data to Access. Excel worksheets can be treated like database tables and you can sort of link two worksheets together as though they are related database tables. Thats what I've done and I've got a sample for you, not a complete solution but something you can adapt. It would be worth while emailing you the whole spreadsheet.
So if your wanting it I'll need your email though if you don't mind. You'll like this solution, very very fast, very slick...
you can use it to strut your stuff in IT.
mmonte
If only!! Of all the wonderful things you can do with VBA, I would have thought that you could locally query two recordsets. They are tables of data after all. I don't know much about recordset objects, but I get the impression you can't really do much with them.
Thanks awfully for the offer of writing a procedure! I'm not entirely confortable divulging information about my company's database on a public forum though, hope you don't mind. It it were up to me, then fine, but it could be construed as gross misconduct if I were found out.
I may try your idea of writing the recordsets to Access tables and running queries on them from there, but still using code to automate it.
Will let you know how I get on!
Cheers again!
riemerg
Hey man,
Oh you have access to the tables... that helps. Generally SQL Server tables are protected and access to the data is through stored procedures, if I'd known that it would of changed everything. Basically your query there would be the stored procedure. No matter you got it working, nice one.
I likes the programming too...
Divac
OK, I'm trying the Access approach and writing the recordsets to tables in an access database. Below is the code I'm using to write the contents of the recordset to the table:
Do Until RS1.EOF
DoCmd.RunSQL ("Insert into tblCurrMthActives(CustomerID) Select " & RS1!CustomerID & "")
RS1.MoveNext
Loop
It works fine, but is taking ages!!! In the end, I stopped it running and it only got to the 6000th record. There are over 100k records. Is there any way I can do this differently
Regards!
Keith
Dorfer
I could get our IT department to write a stored procedure, but that would take time and I was hoping for a quick solution. I'm not proficient enough in SQL to write something like this. It would involve writing two queries (equivalent to the two recordsets I have) and then a further query on those two queries. How would that look in a single SQL statement
I'll keep looking for a solution and if I get anywhere, i will of course update this thread with the solution for the benefit of others who may have the same problem in future.
Ta!
stswordman
KeithyBoy!!!!
Check out my blog! I've done you a post. You would be limited to 65,000 odd ID's per recordset yes. Could be a no go limitation, so maybe Duckthings recommendation might be better, I'd check it out.