Table from Recordset-VBForums
Results 1 to 12 of 12

Thread: Table from Recordset

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Nov 2000
    Location
    Carolina, Puerto Rico, USA
    Posts
    227

    Table from Recordset

    Is there a way to create a table in Access fom a Recordset?
    NievesJ

  2. #2
    ASP.NET Moderator mendhak's Avatar
    Join Date
    Feb 2002
    Location
    Ulaan Baator GooGoo: Frog
    Posts
    38,174
    How are you getting the recordset in the first place?

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Nov 2000
    Location
    Carolina, Puerto Rico, USA
    Posts
    227
    Well, this way

    Code:
    Select * From TABLE
    Is there a way to create a NEW table fom that recordset without having to create all the fields?
    NievesJ

  4. #4
    Hyperactive Member
    Join Date
    Jan 2003
    Location
    Cape Cod, US
    Posts
    292
    Do you have to use a recordset?
    Instead can you just copy directly into the new table like:

    Code:
    Select * INTO <New_Table> From TABLE

  5. #5
    Hyperactive Member
    Join Date
    Apr 2001
    Location
    N42 29.340 W71 53.215
    Posts
    422

    Create Access table from ADO recordset?

    So, is there a way to dump an ADO recordset into a new Access table?

    Scenario:
    I'm in Access VBA,
    create a recordset from various data sources (doesn't matter what),
    do all kinds of manipulations, adjustments, tweaks, adds, deletes etc.

    Now I want to create a new, local Access table from this.

    This sounds like a reasonable thing to do.

    Thanks, DaveBo
    "The wise man doesn't know all the answers, but he knows where to find them."
    VBForums is one place, but for the really important stuff ... here's a clue 1Tim3:15

  6. #6
    Addicted Member
    Join Date
    Aug 2000
    Posts
    195
    eghhh... I don't think you can do it that way...

    BUT more reasonable to do is:

    1) Use SELECT .... INTO [NewTable] FROM [Bunch of Tables]
    >>> this way you create your new table
    2) Now play with the new table just as you did with th recordset (adjustments, tweaks, etc.)

    How about that

    Brandon.
    Brandon

  7. #7
    Hyperactive Member
    Join Date
    Apr 2001
    Location
    N42 29.340 W71 53.215
    Posts
    422
    Thanks Brandon

    That's certainly one way to do it.
    I do a lot of data manipulation in VBA with recordsets and just thought that if you can move data from a table to a recordset there should be a simple way to move the data from a recordset to a (new) table, retaining all field properties/data types.

    Maybe I'm just missing something, my aim has always been awful. DaveBo
    "The wise man doesn't know all the answers, but he knows where to find them."
    VBForums is one place, but for the really important stuff ... here's a clue 1Tim3:15

  8. #8
    Hyperactive Member
    Join Date
    Apr 2001
    Location
    N42 29.340 W71 53.215
    Posts
    422

    An attempt manually building an Access table from a recordset

    For your enjoyment, critique, correction, improvement, guffaws, slings and arrows, etc.

    Note, the part I left out here, and the main assumption behind my broaching this, is that the recordset is undergoing a LOT of manipulation before being written out. i.e. we're not talking about a simple table copy. Have fun, DaveBo


    Code:
    Sub Test_MakeTableFromADO_Recordset()
        Const NewTableName = "MyNewTable"
        Dim con As ADODB.Connection
        Dim DAO_Type As Integer
        Dim db As Database
        Dim fld As DAO.Field
        Dim FldNo As Integer
        Dim fName As String
        Dim fSize As Long
        Dim fType As Integer
        Dim rst As New ADODB.Recordset
        Dim strQuery As String
        Dim tdfNew As TableDef
        Dim x As Long
    
        Set db = CurrentDb()    ' This is just to get at the TableDefs
    
        Set con = CurrentProject.Connection
        Set rst.ActiveConnection = con
        rst.CursorType = adOpenKeyset
        rst.CursorLocation = adUseClient
        rst.LockType = adLockReadOnly
        rst.Open "Select * from DataTypesSamples" ' Just a test case to get some data into rst
    
    
        ' Create the new table structure & define all fields from the recordset properties
        Delete_Table NewTableName
        Set tdfNew = db.CreateTableDef(NewTableName)  ' Create new TableDef object.
    
        Debug.Print
        For FldNo = 0 To rst.Fields.Count - 1
            fName = rst.Fields(FldNo).Name
            fSize = rst.Fields(FldNo).DefinedSize
            fType = rst.Fields(FldNo).Type
    
            ' When creating the new fields in TableDef need to crossref
            ' the recordset's ADO field type with the DAO type
    
            '?? Note, we're not carrying over field Attributes like dbHyperlinkField
            '?? Also, not accounting for OLE Objects
    
            Select Case fType       ' Convert the ADO type to DAO type
            Case adInteger: DAO_Type = 4            ' dbLong
            Case adUnsignedTinyInt: DAO_Type = 2    ' dbByte
            Case adCurrency: DAO_Type = 5           ' dbCurrency
            Case adDate: DAO_Type = 8               ' dbDate
            Case adNumeric: DAO_Type = 20           ' dbDecimal
            Case adSmallInt: DAO_Type = 3           ' dbInteger
            Case adSingle: DAO_Type = 6             ' dbSingle
            Case adDouble: DAO_Type = 7             ' dbDouble
            Case adLongVarWChar: DAO_Type = 12      ' dbMemo & Hyperlink
            Case adVarWChar: DAO_Type = 10          ' dbText
            Case adBoolean: DAO_Type = 1            ' dbBoolean
            End Select
    
            Debug.Print fName, fType, DAO_Type, fSize ' show the field info
    
            If DAO_Type <> 20 Then
                '?? it doesn't like dbDecimal - Run-time error '3259': "Invalid field data type"
                Set fld = tdfNew.CreateField(fName, DAO_Type, fSize)
                tdfNew.Fields.Append fld
            End If
        Next
    
        db.TableDefs.Append tdfNew  ' Add this new table structure into TableDefs
        Set fld = Nothing
        Set tdfNew = Nothing
        Set db = Nothing
    
    
        ' Load the recordset's data into new table
        For x = 1 To rst.RecordCount
            ' loop on recordset getting each record and building up
            ' an INSERT statement for each. Seems like hard way.
            strQuery = "Insert Into " & NewTableName & " Values("
    
            For FldNo = 0 To rst.Fields.Count - 1
                If IsNull(rst.Fields(FldNo).Value) Then
                    strQuery = strQuery & "NULL,"
                Else
                    Select Case rst.Fields(FldNo).Type
                    Case adNumeric      ' skip this (decimal), since we skipped it above
                        'strQuery = strQuery & rst.Fields(FldNo).Value & ","
                    Case adVarWChar, adDate, adLongVarWChar ' Surround these with single quotes
                        strQuery = strQuery & "'" & rst.Fields(FldNo).Value & "',"
                    Case Else
                        strQuery = strQuery & rst.Fields(FldNo).Value & ","
                    End Select
                End If
            Next
    
            ' Remove last comma and add ")" to close the Values() clause
            strQuery = Left$(strQuery, Len(strQuery) - 1) & ")"
            Debug.Print strQuery
            con.Execute strQuery        ' add the record
            rst.MoveNext
        Next
    
        rst.Close
        Set rst = Nothing
        con.Close
        Set con = Nothing
    End Sub ' Test_MakeTableFromADO_Recordset
    '#######################################################################
    "The wise man doesn't know all the answers, but he knows where to find them."
    VBForums is one place, but for the really important stuff ... here's a clue 1Tim3:15

  9. #9
    Addicted Member
    Join Date
    Aug 2000
    Posts
    195
    Hmmm... ok, I see your point. But I still don't understand why copying the two tables using SQL would not work for you?

    Why are you converting fields from ADO to DAO types, if it is still the same database?... I am confused on this.

    > and a little comment to the code: I would use "Do...Loop Until EOF" instead of For...Next loop, since if your table is very large, the RecordCount may not actually give you all your records! (unless you do MoveLast, then RecordCount, and then MoveFirst). The "Do" loop is so much better for this

    OK, back to your problem... How about doing all the conversion in SQL? I mean changing the datatypes while reading the source table... OK, I better give an example:
    SELECT CDbl(Field1), CInt(Field2), ... INTO [NewTableName] FROM [SourceTableName]

    Would that work?
    Brandon

  10. #10
    Hyperactive Member
    Join Date
    Apr 2001
    Location
    N42 29.340 W71 53.215
    Posts
    422

    Question Slightly different twist

    I've got a corrupt Access 2000 MDB file.
    Getting "'AOIndex' is not an index in this table." whenever I try to open the DB or even try to import from or link to it from another MDB.

    However, I can access the tables in the corrupt DB from VB/VBA via ADODB and read whatever I like into a recordset.

    Anyone know of a simple way to dump that recordset to a local Access table (besides the routine I provided above)?
    "The wise man doesn't know all the answers, but he knows where to find them."
    VBForums is one place, but for the really important stuff ... here's a clue 1Tim3:15

  11. #11
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,044

    Re: Table from Recordset

    Sorry to resurrect this ancient thread, but hey, it's EXACTLY" what I'm trying to do!

    I am trying to create a Table in Access in the "Tables" Tab on my local (Client) PC from a remote Oracle Server database. You can NOT use the SQL "INTO" function with a Pass-Through SQL command if you can't create a table on the Server (which I definitely can NOT).

    Thanks for your persistence, DaveBo! What a headache just to create a snapshot of a remote database!!! I hope it works for me too!
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

  12. #12
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,044

    Re: Table from Recordset

    Thanks DaveBo!

    Your approach works great! I got my test program working! I can now download a Pass-Through Query of an Oracle database directly into an Access Table!

    Your process works great ...

    1) Make a connection to the Oracle data base as a RecordSet.
    2) Create and append an Access Table with the correct exact field structure in the local (Client) Database.
    3) Loop through the returned Oracle Records fetching the desired fields and parsing them into an Access SQL Text String "INSERT INTO test_table VALUES(val1, val2, etc.)". Do a "myDatabase.Execute SQLstring" to load the data values into the local table.
    4) Close everything out and clean house.

    Thanks again!
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.