Solved

Creating a table from ADO Recordset

Posted on 2008-10-31
11
1,197 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
Comment Utility
you can use a make-table query to create a table each time.
0
 
LVL 3

Author Comment

by:Caltor
Comment Utility
How do you set an ADO Recordset as the source of a make-table query?
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
how did you get the ADO recordset?
0
 
LVL 10

Expert Comment

by:LennyGray
Comment Utility
You can link the ADO recordset
0
 
LVL 3

Author Comment

by:Caltor
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 3

Author Comment

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

Expert Comment

by:LennyGray
Comment Utility
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
Comment Utility
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
Comment Utility
Different logical location or different physical location?

How do you reference the ADO recordset?
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
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
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

771 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now