I'm building a lab environment into one of my projects. I'm testing the distribution of values over a largish number of attempts to create a unique value.I have a table called LAB_UniqueIDTest, with two fields:
LAB_ID - the string value being tested, and
LAB_UsageCount - the number of times the value has been created.
I'm trying to open the table using the following code:
Code:
' Initialize access to the LAB_UniqueIDTest table
Set rs = New ADODB.Recordset
rs.Open "LAB_UniqueIDTest", _
CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
I have similar snippets of code all over the application - either with a literal (as here) or as a string parameter.I do not, ever, use the options parameter of rs.open.I get error message "Run time error -2147217900 (80040e14) Invalid SQL statement - expected DELETE, INSERT, PROCEDURE, SELECT or UPDATE
My guess is it's because somehow the Open procedure is trying to interpret the tablename as a SQL statement. But how come it isn't this obtuse at other times?
I opened a 2007 Access db in Access 2010. This DB worked perfectly in Access 2007, but when I opened it in 2010 my recordset.recordcount no longer works.
This is what I have:
rsGetInst was previously defined Dim intInstCount as integer Dim rsGetInst As New ADODB.Recordset rsGetInst.CursorLocation = adUseClient rsGetInst.CursorType = adOpenDynamic rsGetInst.LockType = adLockOptimistic
rsGetInst.open "Select * from tblInstruction where CustID = " & intCustomerID intInstCount = rsGetInst.recordcount
At this point a get a "type Mismatch" error, and it is happening in all my recordsets recordcount.
Is there a command or reference that needs to be change when using Access 2010. When I compared the references the only difference is that in 2007 we reference Microsoft Access 12.0 Object Library and in 2010 is Microsoft Access 14.0 Object Library.
I can't make edits with ADODB recordset bound to my form.
Access 2010 linking to SQL Server 2008.
Simple form bound to a single table.
Connection string works fine.
Code is as below (cursor etc is set using enums btw).
Private Sub Form_Open(Cancel As Integer) Dim rst As ADODB.Recordset If g1OpenRecordset(rst, "tblName", rrOpenKeyset, rrLockOptimistic, False) = False Then Cancel = True Exit Sub
ADODB.Recordset (0x800A0BCD) Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
Code: <% function combomaker(elSQL, elFieldNameID, elFieldName, elSelected, elConn) ' This function creates a generic combo box with values from a table dim elRS set elRS = server.CreateObject("ADODB.Recordset") set elRS = elConn.Execute(elSQL) elRS.MoveFirst
do while not elRS.eof response.Write "<option value='" & elRS.fields(elFieldNameID) & "'" if elSelected <> "" then if cstr(elRS.Fields(elFieldNameID)) = elSelected then response.Write " selected " end if else if elRS.BOF then response.Write " selected " end if end if response.Write ">" & elRS.fields(elFieldName) & "</option>" & vbCrLf elRS.MoveNext loop elRS.close set elRS = nothing end function %>
I have a form that shows records from ADODB recordset.When I try to apply filter to the underlying recordset it works all right but the form doesn't reflect the changes. It shows same rows as before filtering. In debug I can see that the recordset contains only filtered records. Me.Refresh (Recalc, Requery) doesn't work.
Code is as follows:
Dim rs As New ADODB.Recordset rs.Open sql, conn, adOpenStatic, adLockOptimistic Set Me.Recordset = rs
Sub combo_AfterUpdate() Me.Recordset.Filter = "CompanyNo = 123" End Sub
The form is in Continuous forms mode. I cant use DAO because the data comes from SQL server user-defined function.
Simple example is I have say 3 recordsets open (they are opened once as they are refered to many times) - they are open early in the form (in this example 3 price lists that are applicable for a customer) (if the syntax of the select is slightly wrong I'm just showing to 'prove' the concept).
Dim db As Database Dim rs1 As Recordset Dim rs2 As Recordset Dim rs3 As Recordset Set db = CurrentDb() Set rs1 = db.OpenRecordset("SELECT * FROM Prices WHERE (((Prices.ID)=1));") Set rs2 = db.OpenRecordset("SELECT * FROM Prices WHERE (((Prices.ID)=2));") Set rs3 = db.OpenRecordset("SELECT * FROM Prices WHERE (((Prices.ID)=3));")
What I want to do is have a central function that I can pass a list I wish to process/do something with aka. The 3 recordsets are the same except for the where criteria.
private sub GETPRICEFROMLIST(pricelist as long)
dim rs as recordset
set rs = Recordset("rs" & pricelist) .....
I put the above to show what I'm trying to do but of course that doesn't work There won't be any updates to these recordsets only reading of data.
I am currently developing a calendar and am trying to open a recordset based off of a SQL string. When I deleted the Where part of the SQL statement, the code ran fine. So I am pretty sure that the problem lies within the Where part of the code. I use this code to filter my query based on txtTaskTypeID but if the value is null then the query is suppose to return all values. I keep getting Run-time error"3061: Too few parameters. Expected 1".
I try to use collection of a ADODB.recordset eg rsTarget,Essentially I want to assign the fields (columns) in this rsTarget with respective data. I tried to use of
two sets. (fld as adodb.field) Set fld = rsTarget.Fields.Item(count) Set fld = rsTarget.Fields.Item(Column(count))
Then I try to use following to assign RsTarget!fld.name = Data1
It failed stating that there is no fld.name. Using column set, it state that there is no column function. How to do it right?
When attempting to open a recordset, I get "Run-time error '3709': The connection cannot be used to perform this operation. It is either closed or invalid in this context."
Any suggestions would be greatly appreciated.
Public Function GetTickets(ID, NbrTickets)
Dim rsTicket As ADODB.Recordset Dim rstAPs As Object Dim strQry As String
Dim cnnDB As ADODB.Connection Set cnnDB = CurrentProject.Connection
'Determine how many tickets have already been assigned... strQry = "SELECT count([ticket number]) as nbr FROM Tickets " _ & "WHERE [Tickets]![MailList ID] = " & ID & ";"
MsgBox (strQry)
Set rsTicket = New ADODB.Recordset rsTicket.Open strQry
With rstTicket .MoveFirst AssignedTiks = rstTicket!nbr 'This is the query result End With
MsgBox ("Number of assigned tickets is " & AssignedTiks)
If AssignedTiks = NbrTickets Then 'nothing MsgBox ("Nothing") Else If AssignedTiks > NbrTickets Then MsgBox ("Remove Tickets") 'Remove Tickets Else 'Add Tickets MsgBox ("Add Tickets") End If End If
I am using the following code to create a ADO recordset. The table in which the SQL refers to is in a front end database where the table is linked to the backend. I have just recently experimenting with ADO and need some help. The following code give me and error "Method 'Open' of '_object' failed. Is there a different way to crate a ADO recordset when the table is located in the backend. Please help
Private Sub Form_Load() Dim mysql As String Dim conn As ADODB.Connection Dim rs As ADODB.Recordset Dim PersonnelInfo As String
mysql = "SELECT tbl_Personnel.last, tbl_Personnel.RANK, tbl_Personnel.SECTION " & _ "FROM tbl_Personnel " & _ "WHERE (((tbl_Personnel.RANK)='lt') AND ((tbl_Personnel.SECTION)=[Forms]![sos Personnel]![SchShiftFind]));"
Set rs = New ADODB.Recordset Set conn = CurrentProject.Connection
I have a a form which I use to populate query parameters and send the result to MS Word, where a table is created from the query. The form contains 2 controls: 1) a combo box containing the month number and 2) a text box containing a year value. The form also contains a command button that opens the query into a recordset and then creates the Word table.
The query (qryCostData) contains 2 fields that reference these form parameters. The query works when:
I open the query directly (query window) while the form is open w/ the month and year parameters selected I open the query directly (query window) when the form is closed but the month and year parameters are hard-coded into the query I open the query using the command button on the form when the month and year paramters are hard-coded into the query
The query does not work when I open the query using the command button on the form and the query containes references to the month and year parameters from the form. The following error is returned on the following command:
Invalid SQL statement; expected DELETE, INSERT, PROCEDURE, SELECT, or UPDATE
Any ideas on why it works using the form command button when the parameters are hard-coded but not when referencing the form fields? If I open the form and select the parameters and then open the query through the query window, it works fine.
I have looked through the threads and have not found an answer to my question, so I post it hoping there is an answer as well as documenting useful information for other individuals.
The following code is what I am using to 'pull' data in order to print a certificate. It functions the way I designed it (verified by debug.prints and msgboxes). My question is how do I pass data to a defined report (certificate) based upon the results of a built recordset. When the report opens, the values come up as "#Name?". I'm guessing that the issue is syntax, but I just don't know. Here is the code I have so far (I've even commented it for y'all)...
Looking forward to your comments...
-BT.
Dim RSAgg As Recordset Dim RSsrc As Recordset Dim DB As Database Dim strAgg As String Dim strSQL As String Dim strCert As String
'If there is no week number set, drop out If Not IsNumeric(txtWeekNum) Then MsgBox "You Must Specify a Week Number.", vbInformation + vbOKOnly, "Required Input" Exit Sub End If
' tblAggDesc contains the field names ' that have scores I want to evaluate. ' If someone makes a perfect score, ' then they get a certificate. Fields are ' a1, a2, a3, b1, b2, b3, c1, c2, c3. strAgg = "SELECT tblAggDesc.AggCourse, tblAggDesc.AggDesc FROM tblAggDesc;"
Set DB = CurrentDb() Set RSAgg = DB.OpenRecordset(strAgg)
RSAgg.MoveFirst Do While Not RSAgg.EOF If (Right(RSAgg!AggCourse, 1) > 1) Then 'rounds 2 & 3 contain additional information that is printed on certificate strSQL = "SELECT tblScores.HEDR, tblRoster.Fname, tblRoster.Lname, tblScores.WeekNo, tblScores." & RSAgg!AggCourse & ", tblScores." & RSAgg!AggCourse & "X AS AggCourseX " & _ "FROM tblRoster LEFT JOIN tblScores ON tblRoster.HEDR = tblScores.HEDR " & _ "WHERE (((tblScores.WeekNo)=" & [txtWeekNum] & ") AND ((tblScores." & RSAgg!AggCourse & ")=100));" Else strSQL = "SELECT tblScores.HEDR, tblRoster.Fname, tblRoster.Lname, tblScores.WeekNo, tblScores." & RSAgg!AggCourse & " " & _ "FROM tblRoster LEFT JOIN tblScores ON tblRoster.HEDR = tblScores.HEDR " & _ "WHERE (((tblScores.WeekNo)=" & [txtWeekNum] & ") AND ((tblScores." & RSAgg!AggCourse & ")=100));" End If
Set RSsrc = DB.OpenRecordset(strSQL, dbOpenDynaset) If Not (RSsrc.BOF And RSsrc.EOF) Then RSsrc.MoveFirst Do While Not RSsrc.EOF If (Right(RSAgg!AggCourse, 1) > 1) Then strScore = RSsrc!AggCourseX & "X" Else strScore = "" End If 'MsgBox RSsrc!Fname & " " & RSsrc!Lname & " " & RSsrc!WeekNo & " " & RSAgg!AggCourse & " " & strScore & " " & RSAgg!AggDesc strCert = "Fname='" & RSsrc!Fname & "' AND Lname='" & RSsrc!Lname & "'" & " AND WeekNo='" & RSsrc!WeekNo & "' AND XCount='" & strScore & "' AND AggDesc='" & RSAgg!AggDesc & "'" 'Debug.Print strCert
' this is the point that I have problems. ' I want to pass RSsrc!Fname, ' RSsrc!Lname, RSsrc!WeekNo, ' RSsrc!AggCourseX, RSAgg!AggDesc to ' the report. DoCmd.OpenReport "rptCleanTarget", acViewPreview, , strCert
Special situation: The SQL Server Linked Server across the country is linked to a Read Only Oracle DB. This data pull works perfectly and populates the Subform.
The problem is that Oracle can take 3 to 6 seconds to retrieve the single record depending on the network traffic through a small pipe.
The code below shows the RecordSource for the SubForm. clicking on a list box supplies the value. Then 3 to 6 seconds later, the subform populates.
The actual Recordset for this Recordsource is needed to conduct Validation on each field. Normally this would be on SQL Server, I might just create a Recordset Oject and run this SQL statement again in 1 milisecond. In this case, it will probably take an additional 3 to 6 seconds. Avoiding another lengthy round-trip to Oracle would be prefered.
Goal: How does one grab, clone, or other wise reference the existing recordset for the SubForm?
Note: Immediate Window - One single field can be returned quickly
There are 48 fields that need validation - is there a way to reference the entire recordset?
Immediate Window during Break Mode: ? me.fsubsrNavSHLBHL("NavSH_QQ") NESE ' this is the correct value for the current recordsource
Set a breakpoint right after the line: fsubsrNavSHLBHL.Form.RecordSource = "Select * from vsrNavigatorSHLBHL where Well_ID =" & txtNavWellID.Value
Immediate Window: ? me.fsubsrNavSHLBHL.Form.RecordSource Select * from vsrNavigatorSHLBHL where Well_ID =91229
I want to write a email where there are 2 or 3 different ordernumbers for same email, i want to include the email in the mail part as single column table. how to do it? also can i use result of one recordset for other recordset?
Hi everyone, I'm just a newbie in DB programming and I'm having a trouble:
I have a database located in my IIS server: "c:inetpubwwwrootwebapplication1dbs1.mdb".
I'm also developing a VB6 app in another computer which will access to my IIS server to get data in my database. I'm using ADODB, but I don't know how to establish the connection as well as to get the data from the server (remote machine).
Can anyone in here help me by showing an example in VB6 code? Thank you very much, any sugestion is welcome Reply With Quote
The company I work for remains on the 2.6 version of the various MDAC components. Unfortunately, upgrading is not an option that I can control. So....
When I open a recordset which DOES contain records, I often return a RecordCount of -1. EOF is false, so that is working, and I am able to use that to get around part of my problem. Where I need the count after iterating through the recordset, I am able to use an iCounter variable to get that. I have tried using MoveLast, MoveFirst, etc. before calling for RecordCount, but that does not help.
In many instances having the count BEFORE going through the recordset would be very helpful. Has anyone else ever encountered this problem? Anyone devise a workaround?
I am trying to set up an unbound form inn access 2003 to link to a PostgreSQL database. Being a newbie to this sort of stuff, I am having a few problems. Using Access 2003 VBA Programmers Guide, I have found some code to help in what I am trying to do but I cannot get my head around the connection settings.
The code under the form General Declarations is: Code:Dim rsTest As ADODB.RecordsetDim cnTest As ADODB.ConnectionDim boolAddNewMode As BooleanPrivate Sub pPopulateFields()' Check to be sure the recordset is not in a BOF or EOF state.' If it is then do nothingIf Not rsTest.BOF And Not rsTest.EOF Then' rsTest.Resync adAffectCurrentMe.job_number = rsTest.Fields("job_number")Me.customer_ref = rsTest.Fields("customer_ref")Me.pen_contract = rsTest.Fields("pen_contract")End If' Set focus to the Exit controlMe.cmdExit.SetFocus' Reset the text boxes and save/cancel buttons to a' locked or disabled state.Me.job_number.Locked = TrueMe.customer_ref.Locked = TrueMe.pen_contract.Locked = TrueMe.cmdCancel.Enabled = FalseMe.cmdSave.Enabled = False' Reset the boolAddNewMode flagboolAddNewMode = FalseEnd SubCode under on Open event of form:Private Sub Form_Open(Cancel As Integer)' For simplicity, we will use the built-in connection of the ADP.' If we were creating an MDB or connecting to a different SQL Server,' then the full connection string would need to be suppliedSet cnTest = Server.CreateObject("ADODB.Connection")cnTest.Open "FILEDSN=PostgreSQL"' Create the recordset and move to the first recordSet rsTest = New ADODB.RecordsetrsTest.Open "Digi Ticket", cnTest, adOpenDynamic, adLockOptimisticrsTest.MoveFirst' Populate the text boxes on the form with datapPopulateFieldsEnd Sub I want to the form to pickup data using a filedsn called PostgreSQL from a table called 'public_digipen_tickets' and populate a form called Digi Ticket.
I have a form with a query set up as the record source. If there are records in this query, the form will display. When the user clicks a button, if there is another record in this query, the form displays the next record. When it gets to the EOF, it should close this form, but EOF is not being recognized (I run debug and it says it's false when the button is clicked)? I get run-time error '3201' after I click the button twice.
I know for a fact that there are 2 records in this query. So once I click the button on the 2nd record, the form should close.
Code: Private Sub SelectTblMyMedButton_Click() Dim db As DAO.Database Dim rs As DAO.Recordset Set db = CurrentDb() Set rs = db.OpenRecordset("qryMedDataSelect", dbOpenDynaset)
I have learned the basic looping technique for going through a record set from the following link. I need to know if my logic is on the right track. URL...
My question is a followup to a thread that was opened on this forum: URL....I want to do the following:
1) Use record set looping technique to fix a variety of incorrect naming conventions to a standard format 2) Update the table (or create a new table) from the updated record set values. (Is my logic going to update the table selected in the code I used to dimension the record set or will I need to do something else to make the changes available for other tasks after record set is closed ? After the naming conventions are fixed this data will be available for excel automation that I am working on and posting questions on another thread in this forum. HAHA I'm going code BANANAS) 3) rs.fields![fleetlocation] is used so many times, how can I make this a variable (what do I dimension the variable as?) 4) Use an AND statment with an if statment (how to do this with correct syntax)
Code: sub loopandfix() on error goto errorhandler: strSQL = "tblUnionQueryResults" 'table was created from a union query but has inconsistant naming conventions for the fleet location name
I have access application which was running fine recently but for some reason everytime I run it now it gives an error on the adodb connection string saying compile error.
Its doing this on every adodb connection I have in my database.
I have tried registering the dao an ado dlls but still the problem persists.
I have a routine which connects to MySQL database with ADODB connection. This worked in 2003 and 2007 runtime but throws a 430 error on 2010 runtime. It works in full access 2010. Has there been a change of policy to restrict this type of connection in the 2010 runtime? If I use a linked ODBC table instead of ADODB the runtime fails with "Runtime Error". It still works in full access.
I am trying to open a child recordset but keep getting Error 424 "Object required".
Dim rstEmails As DAO.Recordset2, DBS As DAO.Database, rstFile As DAO.Recordset2 Set DBS = CurrentDb Set rstEmails = DBS.OpenRecordset("Beldenemails") Set rstFile = rstEmails.Fields("email").Value 'Error 424 here End Sub
Is there a possibility to use a shorter code for extracting one field from one record, than opening a recordset, getting the field, closing it and setting the recset to "Nothing" again? I'm doing this multiple times in my code and it seems a bit too much for what it's supposed to do.
Code: sql = "SELECT tblAdFlgDaten.* FROM tblAdFlgDaten WHERE (((tblAdFlgDaten.AuftrID)=" & ABAuftrID & _ ") And ((tblAdFlgDaten.Schritt)=2))" Set RSnap = dbase.OpenRecordset(sql, dbOpenSnapshot) If IsNull(RSnap.Fields(2)) Then ... ... End If RSnap.Close Set RSnap = Nothing
