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
LVL 3
CaltorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.