Why clone an MS-Access recordset?
I'm a newbie at VBA and attempting to understand someone else's code. Set rstClone = Me.RecordsetClone rstClone.MoveFirst Why does the recordset have to be cloned? Why can't the code be Me.Recordset.MoveFirst? ...
recordset
How to return a recordset from a function
I'm building a data access layer in Excel VBA and having trouble returning a recordset. The Execute() function in my class is definitely retrieving a row from the database, but doesn't seem to be returning anything. The following function is contained in a class called DataAccessLayer. The class contains functions Connect and Disconnect...
VBA - Create ADODB.Recordset from the contents of a spreadsheet
Hello, I am working on an Excel application that queries a SQL database. The queries can take a long time to run (20-40 min). If I've miss-coded something it can take a long time to error or reach a break point. I can save the results to a sheet fine, it's when I am working with the record sets that things can blow up. Is there a way ...
Recordset manipulation in SSIS
In my SSIS job, I have a need to accumulate a set of rows and commit them all transitionally when processing has completed successfully. If this was pure SQL, I would use a temp table inside a transaction. In SSIS there are a number of issues complicating this. It's difficult to have multiple components share the same transaction and hav...
Access: Reading deleted record buffer
According to the Access (2003) documentation, when you delete a record, it's placed in a buffer, then the BeforeDeleteConfirm event is raised, then the AfterConfirmDelete ad Delete events. In my table if a record is deleted, I need to add one of its field values (a quantity value) to a field in another table. I tried setting the quantit...
Whats the difference between rs.close vs rs = nothing in a RecordSet
I often find it confusing as to when it is appropriate to use: rs.Close opposed to Set rs = Nothing I can understand needing to close a connection to a source, but should I be using both when the variable falls out of scope? Can I just set the variable to Nothing in order to skip the step of Closing the connection? Would this ...
SSIS - How do I see/set the field types in a Recordset?
I'm looking at an inherited SSIS package, and a stored procedure is sending records to a recordset called USER:NEW_RECORDS. It's of type Object, and the value is System.Object. It is then used for inputting that data to a SQL table. We're getting an error, because it seems that the numeric results of the stored procedure are being put...
Looping through recordset with VBA
I am trying to assign salespeople (rsSalespeople) to customers (rsCustomers) in a round-robin fashion in the following manner: Navigate to first Customer, assign the first SalesPerson to the Customer. Move to Next Customer. If rsSalesPersons is not at EOF, move to Next SalesPerson; if rsSalesPersons is at EOF, MoveFirst to loop back to...
WHERE IN Query with two recordsets in Access VBA
Hi All, My first post here, so i hope this is the right area. I am currently trying to compare 2 recordsets, one of which has come from an Excel named range, and the other from a table in the Access database. The code for each is: Set existingUserIDs = db.OpenRecordset("SELECT Username FROM UserData") Set IDsToImport = exceldb.OpenReco...
Why is recordset result being returned in this way for Python database query?
I have searched high and low for an answer to why query results returned in this format and how to convert to a list. data = cursor.fetchall() When I print data, it results in: (('car',), ('boat',), ('plane',), ('truck',)) I want to have the results in a list as ["car", "boat", "plane", "truck"] ...
SSIS - 'Execute SQL' Task and Record Sets
Hi, How can I access a 'RecordSet' within a 'Execute SQL' task when using SSIS? I have looked at the parameter mapping options within the Execute SQL Task Editor and cannot find a type of object to allow me to pass the variable holding my record set to the task. ...
Read XML file from ADO (VB6) Into .Net DataSet
I am trying to assist users in migrating from a VB6 application to a C# application. The VB6 app allows the export of data from an ADO (2.8) recordset via XML, but the C# application fails to read the XML producing the following error: System.Data.DuplicateNameException: A column named 'name' already belongs to this DataTable VB6 Code ...
Visual Basic 6 ADO Update question...
Hi, I've been working with a Legacy application which interacts with a database through ADODB, and most of the changes to records follow a fairly straightforward pattern of: Create a Recordset from a query Make various change to the recordset call .Update on the recordset. What I'm wondering is, with ADODB recordsets, is there anywa...
MS Access ADODB.recordset character limit is 2036!? Can this be increased?
In the following AccessVBA code, I am trying to write a record to a memo field called "Recipient_Display": oRec1.Fields("RECIPIENT_DISPLAY") = Left(sRecipientDisplayNames, Len(sRecipientDisplayNames) - 2) When the string contains 2036 characters, the write completes. Above this number I get the following error: Run-time error'-2147217...
Update Field in ADO Recordset and Re-sort values in Classic ASP
Here's the current situation: I have a recordset of products. The price of these products depends upon other information elsewhere on the page. So, as I loop through the recordset for output, I calculate the price using a function, and display it. This all works wonderfully. Now, for the new wrinkle. I need to be able to sort the pr...
How to add item to ms access List/Combobox programatically in addition to recordset
I have a combobox in Access 2003 whose recordset is obtained from a stored procedure. I need to find a way to add an additional item to the lists programmatically. The stored procedure returns the following StatusID and StatusName list. 1,Open 2,Closed 3,In-Process 4,Under Review I want to add the option "All Cases" with the StatusID...
What's the most efficient way for accessing a single record in an ADO recordset?
I want to access individual records in a classic ADO recordset without enumerating over the entire recordset using .MoveNext. I'm aware of using AbsolutePosition as well as .Filter =. What's the best way? More Details: I'm likely going to be accessing the recordset several times pulling out individual records that match a list of reco...
Un-escaping characters present in recordset that is bound to control in Access 2003
I have some data stored in SQL Server that contains apostrophes. Within the SQL Server table to apostrophe is escaped. For example the phrase "Roberts's thesis" is stored as "Robert''s Thesis". I use Access 2003 as a font end for the application and I use ADO to access the data stored in SQL Server using Stored Procedures. The issue I a...
Proper way to close ADO recordset in Access when passing to calling method?
I am using Access 2003 to access data stored in a SQL Server database via ADO. Most often I obtain the data using stored procedures. I execute the stored procedures via ADO in a separate function that returns a recordset. When returning a recordset from a function where is the best place to close the recordset and release it's hold on me...
how to update a recordset in a subform using vb (I am getting error #3426) in Access 2007
hello, I am trying to update a control in a subform. Actually I am calculating the number of months. here is the code: (which doesn't work!) While (Not Me.Form.Recordset.EOF) months = Round((Me.End - Me.Start) / 30, 0) Form_FinanceSubform.[number of months] = 0 Me.[number of months] = months Me.Form.Recordset.MoveNext Wend the error ...