Solved

Creating a table from ADO Recordset

Posted on 2008-10-31
11
1,225 Views
Last Modified: 2013-11-27
Is there a way in code to convert an ADO Recordset into a Table?

I have a query that gives me some information for a report but I want to limit the query to only those customers that are listed in my ADO recordset. I figure that if I can convert the ADO recordset into a query then I can link the tables together to achieve this aim. If there is another way to limit the query results by the recordset then please let me know how to do that instead.

Many thanks

Darren
0
Comment
Question by:Caltor
  • 5
  • 4
  • 2
11 Comments
 
LVL 10

Expert Comment

by:LennyGray
ID: 22851440
you can use a make-table query to create a table each time.
0
 
LVL 3

Author Comment

by:Caltor
ID: 22851462
How do you set an ADO Recordset as the source of a make-table query?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22851470
how did you get the ADO recordset?
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 10

Expert Comment

by:LennyGray
ID: 22851526
You can link the ADO recordset
0
 
LVL 3

Author Comment

by:Caltor
ID: 22851539
VBA code to read from Visual Foxpro OLE DB Provider.

Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=VFPOLEDB; Data Source=\\server\data\comp_t.dbc"
conn.Open
strSQL = "SELECT * FROM ncntr WHERE nc_job='AB01' ORDER BY nc_cntr;"
Set rst = New ADODB.Recordset
With rst
  Set .ActiveConnection = conn
  .CursorType = adOpenForwardOnly
  .LockType = adLockReadOnly
  .Open strSQL
End With

Open in new window

0
 
LVL 3

Author Comment

by:Caltor
ID: 22851549
LennyGray, How do I link the ADO recordset?
0
 
LVL 10

Expert Comment

by:LennyGray
ID: 22851654
In the tables container of your access database, click on:

File -> Get External Data -> Link Tables

You navigate to the SqlServer, Oracle, Access DB or other ODBC data source in the files of type pulldown on the bottom of the dialog that appears.

This will link the table and its structure for your use.

Good Luck!!
Lenny Gray
0
 
LVL 3

Author Comment

by:Caltor
ID: 22851690
Thanks but I don't want to create a linked table as my customers source data is in a different location to mine.
0
 
LVL 10

Expert Comment

by:LennyGray
ID: 22851769
Different logical location or different physical location?

How do you reference the ADO recordset?
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 22851870
you can insert the Ado recordsets to a table.

create a new recordset from the destination table
then you can use

do until rst.eof
with newRs
       .addNew
       !Field1=rst!field1
       !field2=rst!field2
       .update
end with
rst.movenext
loop


0
 
LVL 3

Author Comment

by:Caltor
ID: 22852045
LennyGray, different physical location. I stored the data location in a parameter at the moment and use that to generate the ADO connection string.

capricorn1, I have accepted your solution as the answer. In the meantime I actually wrote my own routine to create and populate a table but it is pretty similar. I will post it below for anyone elses benefit. I was hoping there might be an inbuilt function but nevermind.

Thank you both for your input.

Private Function CreateLocalTable()
 
    Dim rst As ADODB.Recordset
    Dim strSQL As String, strCustomer As String
    
    '' delete existing tCustomers Table
    strSQL = "DROP TABLE tCustomers;"
    CurrentDb.Execute strSQL
        
    '' create the tCustomers table
    strSQL = "CREATE TABLE tCustomers (CustomerAccount TEXT(8));"
    CurrentDb.Execute strSQL
    
    '' create the customers subset recordset
    Set rstUnits = GetCustomersRecordset(Me.cboAreaManager.Value)
    
    With rst
    
        '' go to the first record
        .MoveFirst
        
        '' loop through the units
        Do Until .EOF
            '' insert into tCustomers table
            strCustomer = .Fields("nc_cntr").Value
            strSQL = "INSERT INTO tCustomers (CustomerAccount) VALUES ('" & strCustomer & "');"
            CurrentDb.Execute (strSQL)
            
            '' move to next record
            .MoveNext
        '' end loop
        Loop
        
    End With
    
End Function

Open in new window

0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question