Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2002
    Location
    Baltimore, MD
    Posts
    2

    Unanswered: Writing to A Recordset (ADO) Using a VARIABLE rst!(VARIABLE) - Help!

    I am attempting to take one table and append another table with the same fields. I wroter the VBA to capture all the field names in temp(count) variables but when I attempted to write the value from one table to the next I am getting an error. Any help on how to use a variable in this manner? Any better ideas on how to matchup each RST! one to one adding new records? Here's the error:

    TYPE DECLARATION CHARACTER DOES NOT MATCH DECLARED DATA TYPE

    This line:

    rst!(temp(count)) = rst2!(temp(count))

    Of this CODE:

    Public Function append_pdptTable()
    Dim rst As New ADODB.Recordset
    Dim rst2 As New ADODB.Recordset
    Dim adoField As ADODB.Field

    Dim temp(50) As Variant
    Dim count As Single
    Dim check As String

    Set rst = New ADODB.Recordset
    Set rst2 = New ADODB.Recordset

    rst.ActiveConnection = CurrentProject.Connection
    rst.Open "Select * from PDPTMAST", CursorType:=adOpenDynamic, LockType:=adLockOptimistic
    rst2.ActiveConnection = CurrentProject.Connection
    rst2.Open "Select * from PDPTMAST_2", CursorType:=adOpenDynamic, LockType:=adLockOptimistic

    count = 0

    For Each adoField In rst.fields
    temp(count) = adoField.Name
    count = count + 1
    Next

    rst.MoveLast
    rst2.MoveFirst

    Do While Not rst2.EOF
    count = 0
    For Each adoField In rst.fields
    rst.AddNew
    rst!(temp(count)) = rst2!(temp(count))
    count = count + 1
    Next
    rst2.MoveNext
    Loop

    rst.Close
    rst2.Close

    Set rst = Nothing
    Set rst2 = Nothing

    End Function

  2. #2
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    have you tried droping the temp, i've not used ADO before but DAO lets you refer to a record by it's location as well as it's name so the name in temp(count) should equal rst(count).name
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  3. #3
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Im clueless about the ado stuff as well but I have used querydefs to do something similar. What I did was:

    Dim Cur_DB As DAO.Database
    Dim Record_Set As DAO.QueryDef
    Set Cur_DB = CurrentDb()

    ' delete qrydef if it exists
    For Each qdf In Cur_DB.QueryDefs
    If qdf.Name = "Combo" Then
    Cur_DB.QueryDefs.Delete "Combo"
    Exit For
    End If
    Next qdf
    Set Record_Set = Cur_DB.CreateQueryDef("Combo", "SELECT [REF_PCRKMS_COMBO_LU_TBL].[trade]" & _
    " FROM REF_PCRKMS_COMBO_LU_TBL" & _
    " where week between " & From_week & to_week & _
    " and [REF_PCRKMS_COMBO_LU_TBL].[trade] is not null " & Service_Contract & _
    Reef_Dry & BTN & Equ & _
    Sh_Concern & Shipper & Sh_Office & Cn_Concern & Consignee & Cn_Office & _
    Line & RCountry & POR & Load & Discharge & Pod & DCountry & _
    " Group By [REF_PCRKMS_COMBO_LU_TBL].[trade]" & _
    " Order by [REF_PCRKMS_COMBO_LU_TBL].[trade]")
    Record_Set.Connect = "ODBC;Description=DataWarehouse;DRIVER=SQL Server;SERVER=scnc044db;UID=LineDept;PWD=reader;Ne twork=DBMSSOCN;"
    Trade_Combo.RowSource = "Select trade from Combo order by trade"


    This basicaly created the querydef and then uses it as the rowsource for a combo, but it could be set up to be used in a docmd.runsql update statment as well.
    hope that helps
    Jim

  4. #4
    Join Date
    Nov 2002
    Location
    Baltimore, MD
    Posts
    2
    Originally posted by m.timoney
    have you tried droping the temp, i've not used ADO before but DAO lets you refer to a record by it's location as well as it's name so the name in temp(count) should equal rst(count).name
    * Yeah that's why i'm getting mixed up because I've done this before using DAO as well. I may just go ahead and do that...I'll post the code once it's done just for anyone who visits this thread for a similar problem. Thanks.

    [Just can't get a grasp on what the FIELD/FIELDS object does in ADO - Seems relatively useless.]

  5. #5
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    if you think of a recordset as a table then the "field" has the same use(I think) and 'fields' allows you to do tricks like count how many there are, i've done simalar things with the database and databases in DAO
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

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