This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(102.4, 95%, 19%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMM%3C/text%3E%3C/svg%3E)
Hi Everyone, I don’t know if this is possible. I wish to create a virtual Table/RecordSet, open it and add data into it as a standard ADO RecordSet. After the RecordSet is populated I want to be able to pass the RecordSet’s data to a different form. I have seen an example on how to create Virtual RecordSets, but the OpenRecordSet Method requires either a table or query before the recordset can be used. Any help will be appreciated. Thanks, MRM256
What OpenRecordSet do you mean in case of ADO?
There are some examples related to “In-Memory ADO Recordsets”:
This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Comments have been turned off. Learn more
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(147.20000000000002, 27%, 24%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EV%3C/text%3E%3C/svg%3E)
Yes, it is possible to create a virtual table/recordset in ADO and populate it with data. You can create a disconnected recordset using the ADO Recordset object's CreateObject method, and then populate it with data by adding records and fields manually. Here is some sample code that demonstrates this:
mathematica Copy code Dim rs As ADODB.Recordset Set rs = CreateObject("ADODB.Recordset") rs.CursorLocation = adUseClient rs.Fields.Append "Field1", adVarChar, 50 rs.Fields.Append "Field2", adVarChar, 50 rs.Open
rs.AddNew rs.Fields("Field1").Value = "Value 1" rs.Fields("Field2").Value = "Value 2" rs.Update
'Pass the RecordSet to a different form' Set otherForm.rs = rs In this example, we create a recordset with two fields, open it, add a new record, and populate it with data. You can then pass this recordset to a different form by assigning it to a variable on that form.
I thought it would be prudent to show everyone who is trying to help me the steps my application takes.
Here are the step by step images of my problem.
In the Form_Load Event :
Private Sub Form_Load()
Call Create_rsTMP
rsTMP.Open
End Sub
The Create_rsTMP subroutine:
Public Sub Create_rsTMP()
'Purpose: Create a Temporary RecordSet
On Error GoTo Create_rsTMP_Err
Set rsTMP = New ADODB.Recordset
DoCmd.SetWarnings False
With rsTMP
.Fields.Append "TMPID", adInteger, adFldKeyColumn
.Fields.Append "WKS", adVarChar, 50, adFldMayBeNull
End With
Create_rsTMP_Exit:
On Error Resume Next
DoCmd.SetWarnings True
Exit Sub
Create_rsTMP_Err:
'Log error
Call LogError(Err.Number, _
Err.Description, _
"modSQLs Subroutine Create_rsTMP", _
Date & " " & Time)
Resume Create_rsTMP_Exit:
End Sub
The btnExcel_Click event runs this code:
Private Sub btnExcel_Click()
Dim strExt As String, _
fso As Object
strXLSFile = getFileName
Set fso = CreateObject("Scripting.FileSystemObject")
If Not IsE(strXLSFile) Then
txtExcel.Value = strXLSFile
strExt = fso.GetExtensionName(strXLSFile)
If StrComp(strExt, "xlsx", vbTextCompare) = -1 Then
Msg = strXLSFile & " is not an Excel Workbook." & _
vbCrLf & "Please select an Excel Workbook file."
MsgBox Msg, vbCritical
Exit Sub
End If
Set rsXLS = Return_XLS(strXLSFile) Gets the WorkSheets in the selected EXCEL WorkBook
'Checking for RecordSet data
rsTMP.MoveFirst
Do While Not rsTMP.EOF
Debug.Print rsTMP.Fields("WKS")
rsTMP.MoveNext
Loop
'Set Control.Recordset = Recordset
'Set Me!cboWKS.Open = rsXLS
'Me!cboWKS.Requery
End If
End Sub
The code used to retrieve the Excel work sheets:
Public Function Return_XLS(ByVal strXLSFile As String) As Recordset
'Purpose: Get all work sheets contained in
' the selected Excel WorkBook
'Parameters: strXLSFile As String - Excel Workbook
'Returns: A RecordSet of all worksheets contained
' therein
On Error GoTo Return_XLS_Err
Dim I As Long, aryWS() As Variant
DoCmd.SetWarnings False
Set dbs = CurrentDb
Set rsXLS = ReadSchema(strXLSFile)
I = 1
Do While Not rsXLS.EOF
strWKS = rsXLS.Fields("TABLE_NAME").Value
If Right$(strWKS, 1) = "$" Then
'This is a simple sheet name.
'Remove the trailing "$" and continue.
strWKS = Left$(strWKS, Len(strWKS) - 1)
ElseIf Right$(strWKS, 2) = "$'" Then
'This is a sheet name with spaces
'and/or special characters.
'Remove the right "&'" characters.
strWKS = Left$(strWKS, Len(strWKS) - 2)
'Remove the left single quote character.
strWKS = Right$(strWKS, Len(strWKS) - 1)
End If
‘Populates the rsTMP RecordSet
rsTMP.AddNew
rsTMP.Fields("TMPID") = I
rsTMP.Fields("WKS") = strWKS
I = I + 1
rsXLS.MoveNext
Loop
rsTMP.MoveFirst
Do While Not rsTMP.EOF
Debug.Print rsTMP.Fields("WKS")
rsTMP.MoveNext
Loop ‘Shows the WorkSheets in the rsTMP recordset
Set Return_XLS = rsTMP 'Error occurs
Return_XLS_Exit:
On Error Resume Next
DoCmd.SetWarnings True
rsXLS.Close
Set rsXLS = Nothing
Exit Function
Return_XLS_Err:
'Log error
Call LogError(Err.Number, _
Err.Description, _
"modSQLs Function Return_XLS", _
Date & " " & Time)
Resume Return_XLS_Exit:
End Function
The error at the Set Return_XLS = rsTMP line:
No recordset exists because of this error. Can someone point out what I am missing?
Since the website no longer supports images, text and code samples in the same post I am going to post the error as an image.
Did you find the exact location that generates the error?
Yes, In the Function definition I had to change the line; Return_XLS(ByVal strXLSFile As String) As Recordset to Return_XLS(ByVal strXLSFile As String) As ADODB.Recordset. Both record set type had to match and I missed it.
This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Comments have been turned off. Learn more
TO everyone who replied to my question. Since I spread my subroutines and functions throughout the application in different modules. Each reply fixed a separate part of the application. The supplied answers helped with the RecordSet part. However, another issue has popped up regarding showing the RecordSet results in the Combobox. This will be posted as a separate question. Thank you, MRM256