Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1288
  • Last Modified:

Creating a table from ADO Recordset

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
Caltor
Asked:
Caltor
  • 5
  • 4
  • 2
1 Solution
 
LennyGrayCommented:
you can use a make-table query to create a table each time.
0
 
CaltorAuthor Commented:
How do you set an ADO Recordset as the source of a make-table query?
0
 
Rey Obrero (Capricorn1)Commented:
how did you get the ADO recordset?
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LennyGrayCommented:
You can link the ADO recordset
0
 
CaltorAuthor Commented:
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
 
CaltorAuthor Commented:
LennyGray, How do I link the ADO recordset?
0
 
LennyGrayCommented:
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
 
CaltorAuthor Commented:
Thanks but I don't want to create a linked table as my customers source data is in a different location to mine.
0
 
LennyGrayCommented:
Different logical location or different physical location?

How do you reference the ADO recordset?
0
 
Rey Obrero (Capricorn1)Commented:
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
 
CaltorAuthor Commented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now