 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
VB Forum / General / July 2009ADO: Deleting a table after it is loaded into Recordset
Hello Is it possible to delete a table after its data has been successfully loaded into a recordset? Eg. Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=DB1.MDB" Set RS = New Recordset RS.Open "SELECT * FROM TABLE1", Conn, adOpenStatic, adLockOptimistic Conn.Execute "DROP TABLE [Table1]" Above code doesnt work of course. What Im trying to achieve is that I create a temporary table during run-time and fill it with data, now after sorting and doing everything with the data, I want to load it into a Recordset so I can utilize the data. I also want to delete the Table1 as soon as data is loaded into Recordset. How to achieve this? P.S. Im also not sure whether adOpenStatic, adLockOptimistic are right flags to open a recordset. In article <0a9e85b8-ed0b-45f4-a9fa- 4340cfa7febc@c36g2000yqn.googlegroups.com>, itzfaraz@gmail.com says... > Hello > > Is it possible to delete a table after its data has been successfully > loaded into a recordset? No. AFAIK, ADO requires the table to still exist underneath the recordset while you are using it. You can't drop the table until you close the recordset (meaning you're done using the data). You could copy the data from a recordset into some kind of array or UDT, and then close the recordset, though.  Signature/~\ The ASCII \ / Ribbon Campaign X Against HTML / \ Email! Remove the ns_ from if replying by e-mail (but keep posts in the newsgroups if possible). On Jul 1, 5:17 pm, David Kerber <ns_dkerber@ns_WarrenRogersAssociates.com> wrote: > In article <0a9e85b8-ed0b-45f4-a9fa- > 4340cfa7f...@c36g2000yqn.googlegroups.com>, itzfa...@gmail.com says... [quoted text clipped - 18 lines] > Remove the ns_ from if replying by e-mail (but keep posts in the > newsgroups if possible). Hmm.. If I make an array of UDT which is supposed to cold 1000s of rows from a recordset, what would be the fastest way of copying the data into the array ? The For-Next will take very long time. Can the CopyMem API be used here ? Im not very familiar of CopyMem, can u or anyone show it how ? In article <2b15af74-9eb6-42c4-93eb-0bee8661ec10 @d10g2000vbm.googlegroups.com>, itzfaraz@gmail.com says... > On Jul 1, 5:17 pm, David Kerber > <ns_dkerber@ns_WarrenRogersAssociates.com> wrote: [quoted text clipped - 26 lines] > CopyMem API be used here ? Im not very familiar of CopyMem, can u or > anyone show it how ? How many records and fields do you have in this recordset? Loading an array from a recordset in a loop isn't all that slow; I do it all the time for a few hundred to a few thousand records or so. Usually it's the database query that takes the most time. Signature/~\ The ASCII \ / Ribbon Campaign X Against HTML / \ Email! Remove the ns_ from if replying by e-mail (but keep posts in the newsgroups if possible). >On Jul 1, 5:17 pm, David Kerber ><ns_dkerber@ns_WarrenRogersAssociates.com> wrote: [quoted text clipped - 26 lines] >CopyMem API be used here ? Im not very familiar of CopyMem, can u or >anyone show it how ? http://www.w3schools.com/ado/met_rs_getrows.asp Works for me. :-) Webbiz Hmm.. That GetRows thing is good :-) Well i have about 1500-3000 rows. GetRows brings it into array, but I did prefer keeping it in Recordset.. it helps me handling data well. Isn't there any possibility that to copy or clone the original recordset into another recordset and then close the 1st one and delete table ? can Streams be any help in this ? > On Wed, 1 Jul 2009 06:43:02 -0700 (PDT), Faraz Azhar > [quoted text clipped - 35 lines] > > Webbiz In article <26be86a5-e04d-4453-8117-3d82448ccc43 @k8g2000yqn.googlegroups.com>, itzfaraz@gmail.com says... > Hmm.. That GetRows thing is good :-) That's a new one on me; I'll have to look into it for some of my stuff... > Well i have about 1500-3000 rows. GetRows brings it into array, but I > did prefer keeping it in Recordset.. it helps me handling data well. > > Isn't there any possibility that to copy or clone the original > recordset into another recordset and then close the 1st one and delete > table ? Only by putting the data into another table, and then querying it from there. Any ADO or DAO recordset has to be backed by a table or view. Signature/~\ The ASCII \ / Ribbon Campaign X Against HTML / \ Email! Remove the ns_ from if replying by e-mail (but keep posts in the newsgroups if possible). >Hmm.. That GetRows thing is good :-) > [quoted text clipped - 44 lines] >> >> Webbiz I think the big question here is "why"? Why would you want to delete the table after creating the recordset from it? If you still have use of the data itself that came from the table in question, then just keep the table until you know for sure you will never need that data again. If you're just going to kill off the whole database once you get the data you want, then there isn't much use for just a recordset anyway, and therefore use GetRows() and play with the data in the array, which will likely be faster for most things. :-) Webbiz > On Wed, 1 Jul 2009 10:26:24 -0700 (PDT), Faraz Azhar > [quoted text clipped - 63 lines] > > Webbiz The reason why Im doing all this is that I have 4 different tables in my database. Now those four tables have very different data but the common fields in them are Date, ID, Type, Description. So I build a temporary table (Not CREATE TEMPORARY TABLE because theyre not supported by Jet), just to get all that data into one table so I can sort them date-wise. After sorted, I put the data into recordset, then I dont need the table. Now alternate approach would be to load every table's data into a control or array and then use coding to sort it. Well I did bit testing and I found the sql/database technique more suitable and hassle-free. > The reason why Im doing all this is that I have 4 different tables in > my database. Now those four tables have very different data but the > common fields in them are Date, ID, Type, Description. So I build a > temporary table ... Why building a dedicated table at all - why not defining a View instead, which Joins and Sorts all your Data from your 4 "real tables" together - (or instead of a view-definition, why not just use such a larger SQL-Statement directly). This way you can retrieve an ADO-Rs without any temporary steps "in one go". > ...just to get all that data into one table so I can sort them date-wise. > After sorted, I put the data into recordset, then I dont need the table. As just said, that all should be possible with only one correctly formulated SQL-Select (Join... Order By)-Statement, which is then able, to deliver your Rs directly. Maybe just post short Schema-descriptions of your 4 tables - and what you currently expect/create in that (IMO avoidable) Temp- Table (which Fields from your other tables - and what sort-order). Olaf > > The reason why Im doing all this is that I have 4 different tables in > > my database. Now those four tables have very different data but the [quoted text clipped - 20 lines] > > Olaf My purpose of creating table for this was because I couldnt figure out the right SQL query to run. I tried a lot but couldnt make it up. Here's my situation: I have an inventory management (plus various other financial records) database. The inventory portion consists of following tables: * ProductList * Purchases * PurchaseDetails * Sales * SalesDetails The ProductList comprises of 100s of products, storing the bar codes, names, descriptions and cost data. The inventory is being managed on Perpetual Inventory system (weighted average cost). That is, I need to store the current Quantity, Total Cost, and Unit Cost (Total / Quantity), and Selling Price of each product of course. The Purchases table lists all the inventory purchased day to day. It basically stores the purchase order list. It has PurchaseOrder number, Date, reference number for PurchaseDetails table, then other details such as payment on cash/credit, etc. Now each purchase order can consist of more than one inventory item. So obviously I need to store separately, the list of items purchased in each purchase order. Purchase order numbers, date and etc are stored in Purchases table. The PurchaseDetails table lists the details of each order. It has PurchaseOrderNumber, ID (which is autonumber, used for PurchaseDetails), inventory type, purchase cost, quantity purchased, discounts, etc. Now whenever a purchase order is raised, it updates the current status of inventory ProductList table. It updates the quantity in hand, total cost, and then unit cost. Same way the Sales portion works. You can see that the ProductList table defines the inventory, barcodes and current status. The Purchases records new inventory purchased, and PurchaseDetails is a related as a child of Purchases, holds details. Same way, Sales records the sales made, and SalesDetails records the inventory sold and is a child table of Sales. OK now the ProductList only holds the current status of inventory. My objective here is to build a recordset, which enlists all the purchases and all the sales, of a particular barcode, into one place.. which I can sort date-wise. Therefore I'll have a complete inventory register for a particular barcode/item. I dont need to create an actual table for each inventory, as there are hundreds of inventory item and we're going to expand further more.. its just going to create stress on database. I cant figure out the SQL query to run which gives me the solution to my problem. I'll repeat in top-down manner.. for example i want to view the the inventory register of a particular barcode (eg. 1003). The inventory register does not exist in database, it will be built on runtime using details extracted from purchases and sales made in respect of 1003 inventory item. Now I need to the following fields: Date, voucher ID (purchase order / sales order), Quantity, TotalAmount, UnitCost. The Date and VoucherID fields are from Purchases/Sales register. The other details of Quantity, TotalAmount, UnitCost are from the PurchasesDetails/ SalesDetails registers. Now besides extracting data, I am looking for particular barcode 1003, which is also the field in the -Details registers. When I get both the purchases and sales data accumulated under the desired fields of inventory register, I want to be able to distinguish which line item is purchases and which is sales. So I want to add another column to the inventory register which shows this. I call it Type field. It should store "P" or "S" to mark which is which. Its a pretty confusing SQL query. I may somehow be able to annex the two purchases and sales register into one recordset, but then how do I add another field into it (Type field) which marks which is sale or purchase. :) how do u like it? i ended up creating a table at runtime and appending data from purchases and sales into that table. then after showing it to user, i delete the table. thats it. > I cant figure out the SQL query to run which gives me the > solution to my problem. > I'll repeat in top-down manner.. for example i want to view > the the inventory register of a particular barcode (eg. 1003). > The inventory register does not exist in database, it will be built > on runtime using details extracted from purchases and sales > made in respect of 1003 inventory item. > Now I need to the following fields: Date, voucher ID > (purchase order / sales order), Quantity, TotalAmount, > UnitCost. The Date and VoucherID fields are from > Purchases/Sales register. > The other details of Quantity, TotalAmount, UnitCost are > from the PurchasesDetails/SalesDetails registers. Then I'd start with defining two views, which join together the two related tables ...or maybe three tables, in case the ProductList is needed too, to join everything nicely, but create two different views first in either case ... One view (vw_P_PD) that joins Purchases and PurchasesDetails and another view (vw_S_SD) which joins Sales and SalesDetails. Within that two View-Definitions you can already add a Field called InvType with the content of either 'P' or 'S'... vw_P_PD: (Select Date, VoucherID, ..., ... 'P' As InvType From Purchases Inner Join PurchasesDetails On ...) vw_S_SD: (Select Date, VoucherID, ..., ... 'S' As InvType From Sales Inner Join SalesDetails On ...) > Now besides extracting data, I am looking for particular > barcode 1003, which is also the field in the -Details > registers. After defining these two Views, you should be able, to do something like that now: Select * From ( Select * From vw_P_PD Where Barcode = 1033 Union All Select * From vw_S_SD Where Barcode = 1033 ) Order By YourOrderFieldList Of course that is maybe somewhat "oversimplified" - but defining some views beforehand, which hide most of the (intermediate) Statement-complexity already, will ease your final query to a larger extent. HTH - if the suggestions do not help you, you should post a link to a somewhat "reduced" Demo-*.mdb, which contains a smaller demo-subset - and the queries (the code) you already tried against that smaller table-structure. Always a good idea, to post concrete examples - these are usually much shorter (and easier to read and understand) than all these longer descriptions in "plain-text", especially when the problem is a bit more complex - just post some (demo-) data you currently have (reduced to the principle) - and the data-results you want to see finally in your recordset - and a smaller code-example for what you currently use to achieve that - or alternatively the currently non-satisfying SQL-Statements which fail to deliver the desired result. Olaf > > I cant figure out the SQL query to run which gives me the > > solution to my problem. [quoted text clipped - 61 lines] > > Olaf Alright. I will post a blank smaller version of my database online, give me a day .. Im out of city currently. Then we'll try to come up with the solution. Thanks a million ! > > > I cant figure out the SQL query to run which gives me the > > > solution to my problem. [quoted text clipped - 65 lines] > give me a day .. Im out of city currently. Then we'll try to come up > with the solution. Thanks a million ! Ok .. Ive put my database here: http://cid-8cc77f39e2f65a74.skydrive.live.com/self.aspx/.Public/DB%20SQL%20Probl em.zip Ive also put my code in a word file to show you how Ive been accomplishing this. > On Jul 2, 9:40 pm, "Schmidt" <s...@online.de> wrote: > > [quoted text clipped - 24 lines] > > ) > > Order By YourOrderFieldList > Ok .. Ive put my database here: http://cid-8cc77f39e2f65a74.skydrive.live.com/self.aspx/.Public/DB%20SQL%20Probl em.zip > Ive also put my code in a word file to show you how Ive been > accomplishing this. Ok - the above advise is perfectly doable as shown below... '***Into a Form (add a VB-DataGrid, named DataGrid1 - '*** and a reference to ADO to your project) '***Then click the Form Option Explicit Private Cnn As ADODB.Connection Private Sub Form_Load() Dim SQL As String Caption = "Click the Form" Set Cnn = New Connection Cnn.CursorLocation = adUseClient Cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\db1.mdb" 'our first view-definition SQL = "SELECT Date As dDate, 'P' As Type, VoucherID As Voucher, " & _ "Quantity, UnitCost, Amount As Total, ProductCode " & _ "FROM Purchases INNER JOIN PurchasesDetails " & _ "ON Purchases.VoucherID = PurchasesDetails.PO" CreateOrOverwrite_View "vw_P_PD", SQL 'define the second view now SQL = "SELECT Date As dDate, 'S' As Type, VoucherID As Voucher, " & _ "Quantity, UnitCost, Amount As Total, ProductCode " & _ "FROM Sales INNER JOIN SalesDetails " & _ "ON Sales.VoucherID = SalesDetails.SO" CreateOrOverwrite_View "vw_S_SD", SQL End Sub Private Sub Form_Click() Dim T As Single T = Timer 'let's time our results Set DataGrid1.DataSource = GetRsForProductCode(1003) 'visualize it Caption = Format$((Timer - T) * 10, "0.00msec") 'timing-output End Sub Private Function GetRsForProductCode(ByVal ProductCode&) As Recordset Dim SQL As String With New Command 'a Select using the Union-Operator and a Command-Object SQL = "Select * From " & _ " (Select * From vw_P_PD Where ProductCode=@PrCode) " & _ " Union All " & _ " (Select * From vw_S_SD Where ProductCode=@PrCode) " & _ "Order By dDate" Set .ActiveConnection = Cnn .CommandText = SQL .Parameters("@PrCode") = ProductCode Set GetRsForProductCode = .Execute End With End Function Private Sub CreateOrOverwrite_View(ViewName As String, SQL As String) Dim ErrNr As Long, ErrDesc As String On Error Resume Next Cnn.Execute "Create View [" & ViewName & "] As " & SQL If Err.Number = &H80040E14 Then 'view already exists Cnn.Execute "Drop View [" & ViewName & "]" Err.Clear Cnn.Execute "Create View [" & ViewName & "] As " & SQL End If If Err Then ErrNr = Err.Number: ErrDesc = Err.Description On Error GoTo 0 Err.Raise ErrNr, , ErrDesc End If End Sub Olaf > Private Sub Form_Click() > Dim T As Single [quoted text clipped - 4 lines] > Caption = Format$((Timer - T) * 10, "0.00msec") 'timing-output > End Sub Sorry, forgot to adapt the line: Caption = Format$((Timer - T) * 10, "0.00msec") 'timing-output back to: Caption = Format$((Timer - T) * 1000, "0.00msec") 'timing-output (... had it running in a small loop (1 to 100) before in my tests...) But that's only the timing - the rest of the functionality is not touched by that of course. Olaf > > On Jul 2, 9:40 pm, "Schmidt" <s...@online.de> wrote: > [quoted text clipped - 105 lines] > > Olaf Hey this works ! :) :) this is wonderful ! Thanks a million my friend. I wasn't aware of this type of SQL commands. One more thing. Once we have created the two views, we dont need to create/overwrite them again and again, do we? They just sit nicely in the MSACCESS database, I just need to fetch the recordset for particular code. This is great! Thanks a million! > Hey this works ! :) :) this is wonderful ! Thanks a million my friend. Hah, finally you came back... <g> > I wasn't aware of this type of SQL commands. > > One more thing. Once we have created the two views, > we dont need to create/overwrite them again and again, > do we? No, we don't... the included CreateOrOverWriteView-method is useful nonetheless, in case you couple it to your own "DB-Schema-Change-Routines" - which you could trigger dynamically, depending on a comparison of the expected "DB-VersionNumber" your App currently wants to use - and in case you introduced something like a DBVersion-Field (contained in a small table "DBVersions"). Easy, to enhance your DB-Schema dynamically then (in case you need some additional Fields somewhere in your Tables and Views. Olaf > > Hey this works ! :) :) this is wonderful ! Thanks a million my friend. > [quoted text clipped - 19 lines] > > Olaf Alirght. good stuff sir, thanks again :) Faraz Azhar <itzfaraz@gmail.com>'s wild thoughts were released on Tue, 30 Jun 2009 15:10:03 -0700 (PDT) bearing the following fruit: >Hello > [quoted text clipped - 7 lines] >RS.Open "SELECT * FROM TABLE1", Conn, adOpenStatic, adLockOptimistic >Conn.Execute "DROP TABLE [Table1]" Can't say I've tried it. Have you tried diconnection the recordset before doing the delete? >Above code doesnt work of course. What Im trying to achieve is that I >create a temporary table during run-time and fill it with data, Can't you use, you know, like, a temporary table? J > now >after sorting and doing everything with the data, I want to load it [quoted text clipped - 4 lines] >P.S. Im also not sure whether adOpenStatic, adLockOptimistic are right >flags to open a recordset. -- Jan Hyde > Can't say I've tried it. Have you tried diconnection the > recordset before doing the delete? Well I need to be connected to the database all the time. Lots of stuff is going on that connection every minute. I cannot disconnect it. > >Above code doesnt work of course. What Im trying to achieve is that I > >create a temporary table during run-time and fill it with data, > > Can't you use, you know, like, a temporary table? Temporary tables arent supported by MSACCESS/Jet conenctions. Only for sql servers or soemthing... Faraz Azhar <itzfaraz@gmail.com>'s wild thoughts were released on Wed, 1 Jul 2009 08:08:16 -0700 (PDT) bearing the following fruit: >> Can't say I've tried it. Have you tried diconnection the >> recordset before doing the delete? > >Well I need to be connected to the database all the time. Lots of >stuff is going on that connection every minute. I cannot disconnect >it. 'disconnected recordset' I never said disconect completely from the database and you can't be doing anything database related with the recordset because you are deleting the underlying table, >> >Above code doesnt work of course. What Im trying to achieve is that I >> >create a temporary table during run-time and fill it with data, [quoted text clipped - 3 lines] >Temporary tables arent supported by MSACCESS/Jet conenctions. Only for >sql servers or soemthing... Bleah, access ;-) -- Jan Hyde > Hello > [quoted text clipped - 17 lines] > P.S. Im also not sure whether adOpenStatic, adLockOptimistic are right > flags to open a recordset. I'm probably missing something. But Jan was correct. Simply fetch the recordset then disconnect it. "How To Create ADO Disconnected Recordsets in VBA/C++/Java" http://support.microsoft.com/kb/184397 If you want to save this data simply save to a file in either ADTG or XML format. Look up the ADO Recordset.Save method. You can retrieve the data anytime with the .Open method. Your reply to Jan was peculiar as you said "I need to be connected to the database all the time"? Perhaps, but your recordset doesn't. You might also look into building your process for creating a temporary table into a Query that returns the data in a Recordset and thus cut-out the middleman. (But of course I have no idea if that is possible with your problem domain.) -ralph > You might also look into building your process for > creating a temporary table into a Query that returns the > data in a Recordset and thus cut-out the middleman. > (But of course I have no idea if that is possible with your > problem domain.) It is possible - I've already posted a solution for that, based on a sample-database Faraz was providing in his last post (2009-07-05). Olaf > > You might also look into building your process for > > creating a temporary table into a Query that returns the [quoted text clipped - 7 lines] > > Olaf I thought as much (that it was possible and that you had posted a solution), but I was too lazy to examine the whole thread to make sure you all were still talking about the same thing. <g> All the OP needs to do is add Set GetRsForProductCode.ActiveConnection = Nothing and he is done. -ralph |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2010 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.