Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004

    Unanswered: How do I Output an ADO recordset to MDB table?

    Hi, first post to dbforums, feel free to correct any bad netiquette...

    I have an Access 2000 adp connecting to a sql 7 back end. VBA code runs a stored procedure and returns it to an ADO recordset. I'd like to output the recordset to an Access MDB File.

    I've searched around for a 'quick' solution, but I'm thinking that I will have to roll up my sleeves and use ADOX to build the table from the recordset fields collection, then populate it row by row using .movenext and updates etc.

    So the questions:
    1) Is there a way to create the table AND populate at the same time?
    2) Is there a way to at least move the records en masse?
    3) Is there a completely different and better way to do this?

    (More info: in the previous version of this app, which was mdb based, we used QBF and Docmd.TransferDatabase specifying the db to send to and query that the qbf was based on as the source. In this version of the app it's stored procedures that return the data to a recordset. There doesnt appear to be a way to use TransferDatabase and specify all the required parameters that the SP needs...)

    Thanks in advance!

  2. #2
    Join Date
    Mar 2004

    Re: How do I Output an ADO recordset to MDB table?

    You can Persist the ADO recordset to XML using the built-in MS_Persistence provider, you will the have the table structure as well as the data. From there you can re-import the recordset into your MDB.

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  3. #3
    Join Date
    Mar 2004
    Thanks SCIROCCO, I'll have a look at that method. I've somehow managed to avoid XML so far, maybe it's time to get over it!

    Anyone else have more details? Other suggestions?

  4. #4
    Join Date
    Mar 2004
    Well, I broke down and did it myself. After looking at persisting as xml it seemed just too odd of an approach, so I went with the ADOX/ADODB methods.

    The code runs quickly on small recordsets, haven't tried it on anything over a few hundred rows yet.

    I'd greatly appreciate any suggestions of a better way to import the data then the 1 field at a time approach that I'm using here!

    'This code assumes that you have set a reference to ADODB and to
    'ADOX too.  I tested and ran it from an Access 2000 adp file.
    'This class module called CMDBFile is used to create Access DB files
    'And to transfer an ADO recordset to a table in the file
    Option Compare Database
    Option Explicit
    Dim mcatDB As ADOX.Catalog
    Dim mtblNew As ADOX.Table
    Public Function CreateMDB(MDBPath As String, MDBName As String) 
            _As Boolean
       If Right(MDBPath, 1) <> "\" Then
           MDBPath = MDBPath & "\"
       End If
       Set mcatDB = New ADOX.Catalog
       mcatDB.Create "Provider = Microsoft.Jet.OLEDB.4.0;" & _
                   "Data Source = " & MDBPath & MDBName
    End Function
    Public Function CreateTable(TableName As String, _
            rst As ADODB.Recordset)
    'This function creates and populates a Table in an msaccess database...
    'It is designed to Create JET 4.0 tables.  See Microsoft documentation 
    '   for valid field types to convert this to other providers, esp:
    '   Microsoft Knowledge Base Article - 266302:
    '   PRB: ADOX Error -2147217859 "Type Is Invalid" When You 
    '        Append a Table to a Catalog 
       Dim colField As ADOX.Column
       Dim prpField As ADOX.Property
       Dim strField As String
       Dim x As Long
       Dim y As Long
       Dim lngType As Long
       Dim lngSize As Long
       Dim rstnew  As ADODB.Recordset
       Set mtblNew = New ADOX.Table
       mtblNew.Name = TableName
       'This loads table and column properties, and their defaults!
       Set mtblNew.ParentCatalog = mcatDB  
       With rst
           For x = 0 To rst.Fields.Count - 1
               strField = .Fields(x).Name
               'Clean up field names, remove invalid JET 4.0 field chars...
               strField = Replace(strField, ".", "_")
               strField = Replace(strField, "!", "_")
               strField = Replace(strField, "`", "_")
               strField = Replace(strField, "[", "_")
               strField = Replace(strField, "]", "_")
               Set colField = New ADOX.Column
               colField.Name = strField
               lngType = .Fields(x).Type
               lngSize = .Fields(x).DefinedSize
               'convert text fields to Unicode since that's all that Jet
               '    4 supports.
               'If porting this proc to other db formats, will need a section
               '    for each db type to handle it's special cases.
               '(Jet 3.5, Jet 4.0, SQLOLEDB etc)
               Select Case lngType
                   Case adChar
                       colField.Type = adWChar
                       colField.DefinedSize = lngSize
                   Case adVarChar
                       colField.Type = adVarWChar
                       colField.DefinedSize = lngSize
                   Case adLongVarChar
                       colField.Type = adLongVarWChar
                       colField.DefinedSize = lngSize
                   Case adNumeric
                       colField.Type = adNumeric
                       'adNumeric has Precision and Scale properties...
                       'In testing from a SQL 7.0 rst, sometimes got bad precision
                       '  which is what the 'if' clause handles...
                       If .Fields(x).Precision > 18 Then
                           colField.Precision = 18
                           colField.NumericScale = 2
                           colField.Precision = .Fields(x).Precision
                           colField.NumericScale = .Fields(x).NumericScale
                       End If
                   Case adDBTimeStamp
                       'Timestamp is invalid in Jet 4.0!  paste it as datetime
                       '   (Not tested)
                       colField.Type = adDate
                   Case Else   'The rest don't need other parameters set...
                       colField.Type = lngType
               End Select
               mtblNew.Columns.Append colField
               'Now set any properties that you need...Here I'm allowing 
               '   nulls and zero length strings.
               mtblNew.Columns.Item(colField.Name).Properties("Nullable").Value = True
               mtblNew.Columns.Item(colField.Name).Properties("Jet OLEDB:Allow
    Zero Length").Value = True
           Next x
       End With
       mcatDB.Tables.Append mtblNew
       'now append the data.
       Set rstnew = New ADODB.Recordset
       rstnew.Open "SELECT * FROM " & TableName, _   
            mcatDB.ActiveConnection, _
            adOpenStatic, adLockOptimistic
       Do Until rst.EOF
           For x = 0 To rst.Fields.Count - 1
               rstnew.Fields(x).Value = rst.Fields(x).Value
           Next x
       Set rstnew = Nothing
    End Function
    Last edited by GregLyon; 04-01-04 at 17:40.

Posting Permissions

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