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
Solved

Creating a table from ADO Recordset

Posted on 2008-10-31
11
1,232 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

861 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