Solved

Creating a table from ADO Recordset

Posted on 2008-10-31
11
1,240 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

730 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