I have just been working with some temporary recordsets in access 2000.
After working with the recrodsets each is closed and set to nothing but this leads to bloating of around 20Mb on a 70Mb backend database.
So I (in messing around) I added "DoEvents" after closing each temporary recordset and the bloating reduced to just 8Kb.
Is it normal practice to force the closure of temporary recordsets before moving to the next step using DoEvents, because this certainly seems to be suggested by this result?
I have a small (8MB) database that we use to monitor vehicle panels. The user enters a panel ID and it calls an image from a folder.
I have the paths stored in a table and use image1.picture = path
However, every time the user is calling a panel to see it, the database is increasing in size, by the size of the image file (300kb). So in 15 minutes the Db has grown from 8MB to 180MB.
I need to know the best way to insert data into the backend without cause major bloating of the backend. I am currently inserting about 20k records at a time using an INSERT INTO statement. The backend went from 83mb to 511mb trying to insert the last 20k records.
:eek: Trying to access a field in a table using a select case, can anyone tell me what the problem is. I get a "No Current Record Error" when I run the code
Select Case intTMO_RATE Case 5 strNew_PromoID = rst.Fields("5YR").Value Case 7 strNew_PromoID = rst.Fields("7YR").Value Case 10 strNew_PromoID = rst.Fields("10YR").Value Case 15 strNew_PromoID = rst.Fields("15YR").Value Case 20 strNew_PromoID = rst.Fields("20YR").Value End Select
Hello, I'm going to try to explain this as well as I can. I am also attaching a DB for reference. I am using a version of the Code:Private Sub lstAllCenterNames_AfterUpdate() ' Find the record that matches the control.Dim rs As ObjectSet rs = Me.Recordset.Cloners.FindFirst "[Center] = '" & Me![lstAllCenterNames] & "'"If Not rs.EOF Then Me.Bookmark = rs.BookmarkEnd Sub When you look at the DB, the form1 works fine, but is not pulling all of the data that i want. The frmMain is pulling from a query which i have setup to combine 2 columns from 2 different tables and list them into 1 single column and lists in the form all the data that i want to show. When using the query2 as the record source for the form, I can't get the record selector to update when i select new items. Just a note, the tables in the real DB are actually linked to an oracle DB so I am not able to make any changes to table structure. I have just copied only the information needed for this sample DB. Let me know if more explanation is needed.
Can someone point me in a direction to get some good information on how and when to use recordsets? Ive gotten by without having to use them for the most part up until now.. but the inevitable has happened and i figure i might as well learn to use them
for the most part i know When to use them, but its getting into the how and where to use it for the most part.
simple examples or even perhaps a tutorial would be great! thanks guys
Hi all, Anyone know how to open 2 recordsets eg recordsets of 2 tables at same time, and also how to update 2 different fields at once,eg in one line, at the moment I can only open a single recordset and I have 2 docmd.runsql "update etc" commands
Dim TableName As String Dim dbname As String dbname = "c:studentswithdisabilities" TableName = "InputStudent"
Dim CustDB As Database, CustTable As Recordset Set CustDB = DBEngine.Workspaces(0).OpenDatabase(dbname) Set CustTable = CustDB.OpenRecordset(TableName, dbOpenTable)
DoCmd.RunSQL "UPDATE InputStudent SET InputStudent.photoBalance = InputStudent!photoBalance-[Forms]![photocopy]![txttop] WHERE [InputStudent]![TNumber]=[Forms]![photocopy]![ComboTnum];"
DoCmd.RunSQL "UPDATE PhotocopyTable SET PhotocopyTable.RemainingBalance = [PhotocopyTable]![StartingBalance]-[PhotocopyTable]![TopupAmount];"
When I run this code using the (Add Record) click event on a button, it performs the first update but it doesnt perform the second until I add the next data record, Any help much appreciated
Here is the code adding a new recordset to the table from a form : - ----------------------------------------------------------------------------------- Private Sub Command21_Click()
Dim Db As DAO.Database Dim Rs As DAO.Recordset
On Error GoTo Err_Command21_Click
Set Db = CurrentDb Set Rs = Db.OpenRecordset("tbl_ProjResou", dbOpenDynaset) ' Create a new record. Rs.AddNew Rs![ProjectNr] = Forms!frm_Projects![ProjectNr] Rs![ResourceNr] = Me.ResourceNr Rs![Hours] = Me.Hours Rs![reserve] = Me.reserve Rs![assign] = Me.assign ' Save the record. Rs.Update Rs.Close MsgBox Err.Description DoCmd.Close
Exit_Command21_Click: Set Rs = Nothing Set Db = Nothing Exit Sub Err_Command21_Click: ' An unexpected error occurred, display the normal error message. MsgBox "Die Ausgewählte Ressource existiert Schon, bitte neue Ressource auswählen" ' Set the Response argument to suppress an error message and undo ' changes. End Sub ---------------------------------------------------------------------------------------- With the code above, I can add only one new record at a time.
In the form I have two unbound fields which are date fields and are entered by the user. I want the date difference between these dates in number of Months (obviously rounded up) and use the above code to add so many number of new recordsets to the table. For example : if date difference is 12 months then the code should add 12 new records to the table.
As I am new to VBA, I dont know how to customize the above code for the above condition. Could anyone pls help me...Please
Greetings, I have a pretty remedial question, basically I'd like to know how to get a form and its fields to render the change when you call a RecordSet.MoveNext or a MoveLast, basically mimicking the recordset control at the bottom of the form?
I have two tables in my database. one is linked to a Client.txt and the other is tblNewClient. the idea is that this database is a portable version of the actual database, meaning that it will be operating on its own without a connection to the actual back end.
Now the problem is when a new customer is added, i want it to add itself to the tblNewClient. but when i go into my Find CUSTOMERS FORM i need to be able to search threw both tables.
i want to creat somthing like a union query but in vba. I figure i need to have 2 recordsets in vba. one for each table and then i need to have a 3rd recordset wich will hold all the info. im not sure how to copy a whole recordset and set to another table.
Im thinking of somthing like
set RsAll = RsClient & RsNewClient
This obviously does not work but it is what i want to accomplish ,and i want the Rsall to be an actual table and not a public Recordset
Hey all, I was thiking can i make a blank query and then on my filtered form. Pass the data from the selected fields(VIA a ceckbox) and then display a report based on that query? Then have the query cleared for the next time?? Is this possible? Thanks in advance for any help!
Hi there - on one of my forms i have a text box where the user can write the contents of a letter. When a button is pressed, a report is run, and the text from the form is entered into the report -
this all works fine - but when the text reaches a certain length, it goes all unreadable. SO i assume this use of a temp variable (as its not being stored in a table) comes with a text limit? Is there anyway to extend this?
Im have a table that contains contract that are active or not (tblContract) and a table that contains my every day data for each active contract (tblFldDat). Every day i need to populate my tblFldDat with active contract number. that will result in a nice continuous form that i will be able to fill every day. i need to do that every day (meaning.. repopulate from active contract). BTW... the contract may change from active to inactive everyday. Thats why i need to redo everyday.
My question is...how do i populate a set of records from a list of contract ?
i hope its clear enough cause..i realy dont know how to explain it in another way :(
I am working on searching my customer number in my billing table and finding the appropriate customer and then pass the customer number onto my shipping table to find the corresponding shipping data to go with my billing data. My problem is this :
When I find my customer in my Billing table ( searching through field0 = CustomerNumber)and then select it, it won't pass the customer number to continue to search field0 in the shipping table for customers that have a # in any of the cell values of the recordset.
Is there a work around for special characters in cell data ie) #
Any sugestions or comments would be greatly appreciated.
hi, i'm trying to locate a record by id using a recordset clone. when i debug the code, i get a "type mismatch" error... what am i doing wrong??/ here is the code:
Code:Dim rsCur As RecordsetDim strRecord As StringDim Bookmarkstr As StringstrRecord = Me.List26.ValueSet rsCur = Me.RecordsetClonersCur.Find "id =" & strRecord Bookmarkstr = rsCur.BookmarkMe.Bookmark = Bookmarkstr
Need a little help with a record selector. I ask it to find a record and bookmark it. No problem. If record doesnt exist I get the value from a control and run a Insert Into command into my table creating a new record with that case #.
Now how do I modify the code below to make the new record just inserted into the bookmarked record. See sample code below. I'm not too practiced when it comes to this recordset business.
Any help is appreciated
Private Sub FindTheRecord() ' Find the record that matches the control. Dim rs As Object Dim Answer As String Dim aSQL As String
Set rs = Me.Recordset.Clone rs.FindFirst "[CaseNo] = " & Str(Nz(Me![CaseNo])) If rs.NoMatch Then Answer = MsgBox("No Matching Case Number Found." & vbCrLf & "Would you like to start a new" & vbCrLf & "record using this case number?", vbYesNo) If Answer = 6 Then DoCmd.SetWarnings False aSQL = "Insert Into Main ([CaseNo])Values ([Forms]![frmMain]![CaseNo]);" DoCmd.RunSQL aSQL DoCmd.SetWarnings True
DoCmd.GoToRecord acDataForm, "frmMain", acLast Code: Original - Code ' does not recognize the last record just added to the table, is there a command I can use here? ' does not recognize the last record just added to the table, is there a command I can use here?
Else MsgBox "Action Cancelled" CaseNum = "" CaseNumYear = "" DoCmd.GoToControl "CaseNum" End If Else Me.Bookmark = rs.Bookmark Call EnableControls End If End Sub
I am endeavoring to develop a set of tables and forms to control a fireworks display. Here is my problem Master form hold display data (IE Location and date) and customer info (no problems here) the sub form holds the the products and how they are fired. This is my problem This form shows several fields Auto number for key field, number for child link (Display ID), number for data, number for data, text and the next is also number (This field needs to be consecutively numbered for each display) therefor on setting the form property to open as new form (Display)the first row of the record set begins with the first shot. How can I code the procedure and what property of the control box should be used.
I have a form based on an underlying query in a .mdb database. I want to be able to navigate backwards and forwards in the form using .movenext and .moveprevious etc.
Which sort of recordset should I use? I've seen discussions about ADO and DAO, and have come across the term "forward-only", which I'd not been aware of before.
Having made the choice, what is the correct syntax for opening the appropriate recordset, and also for .movenext and .moveprevious etc?
Is it possible to have a form that will filter my data to what i need, and place in a temperary table and then be able to display it in a report??? So I already have a filtered form, however I would like to be able to creat reports on the fly. So I will not need all my fields from my table everytime I filter. So If I create a report I can choose the fields that I need, however this is done will all records in my table and I would like to only use the data I have filters. What way would be best to accomplish this?? I already have the ability to load the report wizard by a command button. I just dont know the best way to use my filtered data with it. Any help would be great. thanks
Hellooo Hi gurus I have a smallish problem I have two tables that I need to join togther - normally no problem
I have one table with 1 event on it- easy however if I have more than 1 event on it I have another table that opens up and I add multiple evnets to it main id number 12345 with 1 event on it and 23456 may have 20 events on it on my other table (with multiples on it) i have this autonumbered (this is great unique id - now i need to make a temp table to include boths sets of data in one file
1 event table - easy multi event tabel I want it to get the main refernce fromt he first table (using 23456 as the example) and have it list these in the table - main refer 23456-multi table unique ref number 23456-1 , 23456-2
so my table should have 12345 23456-1 23456-2 23456-etc
I have been using append qry to make tables - any pointers would be great
Here's the scenario: I create a temp table structure, which works fine. I create a recordset from SQL pulling data from a DB2 connection. This works fine. I want to put the entire recordset result into the temp table.
I have a working means of doing this, but it is very inefficient and leaves the user staring at an hourglass for a minute or two.
What I am currently doing is iterating through each record of the recordset and appending it to the temp table. Desired results, yes, but takes way too much time.
Is there any way to simply 'dump' an entire dataset into the table, instead of on a record-by-record basis?
Here's some code from how I am currently doing things:
Set cmdP1 = New ADODB.Command Set cmdP1.ActiveConnection = cnnP1 cmdP1.CommandText = "SELECT DISTINCT distribution_id FROM " & sDB & " ORDER BY distribution_id" Set rstP1 = cmdP1.Execute
Do Until rstP1.EOF With rst_Temp .AddNew .Fields!distribution_id = rstP1!distribution_id .Update End With rstP1.MoveNext Loop
I'm trying to create a simple 1 field temp table to populate a combo box with the name of the current user and the word "Company." However, after the user closes the form (or as soon as the Temp table is no longer necessary) I would like to delete the table. I can create the table, the fields, add the data, and populate the combo box just fine, but I'm having problems deleting the table after I'm done. I keep getting the error:
Run-Time Error 3211: The database engine could not lock table 'Temp' because it is already in use by another person or process.
here's my code:
Code:Option Compare DatabaseDim dbRoofing As DAO.Database Private Sub Form_Close()dbRoofing.TableDefs.Delete "Temp" 'where i get caught when i close the formEnd Sub Private Sub Form_Open(Cancel As Integer)Set dbRoofing = CurrentDb Dim tblTemp As TableDefDim rcdTemp As DAO.Recordset Set tblTemp = dbRoofing.CreateTableDef("Temp")tblTemp.Fields.Append tblTemp.CreateField("Owner", dbText)dbRoofing.TableDefs.Append tblTemp Set rcdTemp = dbRoofing.OpenRecordset("Temp", dbOpenDynaset)With rcdTemp.AddNew!Owner = CurrentUser.Update.AddNew!Owner = "Company".Update.CloseEnd WithOwner.RowSource = "SELECT Temp.Owner FROM Temp"End Sub
I'm trying to use a RecordSet to pull data out of an Access DB. I need to compare a field in the DB to a text box in my form. This is what I have so far.
Dim Hardware As Database Dim Records As Recordset Dim strSQL As String Set Hardware = OpenDatabase("c:hardware.mdb") Serial.SetFocus strSQL = "SELECT * FROM Computers WHERE Computers.Serial = " & Serial.Text
Set Records = Hardware.OpenRecordset(strSQL)
When I try to run this I get a data type mismatch. Some explanation. Computers is a table, Computers.Serial is a field in that table. The "Serial" in Serial.Text is a field in my form. You may ask why I'm using the "&" operator instead of just putting it all in the string. The reason is that I cannot figure out how to get VB to treat "Serial.text" as a variable and not a string literal. When I try to put it in single quotes (') I get an error saying the OpenRecordSet call requires an argument, and I'm not providing one, meaning I'm quoting it wrong somehow. Any ideas?
Is there a common answer or design mistake that would cause a form to return a different (much higher) record set than that if the query is ran by itself. The query is the control source for the form.
I'm trying to create a log in form for my database using a tutorial I found here. [URL] ..... I got it working fine, but when I split my database into a front end and back end file I could no longer retrieve the record set for this code. I receive the error "Item not found in this collection" on the highlighted line of code below. I'm at a loss as to how I would retrieve the record set with a split end database. The code used in the log on button is below:
Private Sub submitButton_Click() Dim dbs As Database Dim rstUserPwd As Recordset Dim bFoundMatch As Boolean Set dbs = CurrentDb
