Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2005
    Posts
    4

    Unanswered: How to refer DAO.recordset using DoCmd.RunSQL

    How to refer DAO.recordset using DoCmd.RunSQL ???

    I want to create a table in local .mdb with processed external date connected using DAO as below.
    The RunSQL cannot found rsTmp ???
    Please help.

    '=========================

    Function createNewTableFromExt()
    Dim dbExternal As DAO.Database
    Dim rsTmp As DAO.Recordset
    Dim qdfTmp As QueryDef


    Set dbExternal = OpenDatabase("c:\temp\access\Northwind.mdb")

    Set qdfTmp = dbExternal.CreateQueryDef("", "SELECT * FROM Employees WHERE (((Employees.EmployeeID)>5))")
    Set rsTmp = qdfTmp.OpenRecordset(dbOpenSnapshot)

    ' rsTmp.MoveLast: MsgBox rsTmp.RecordCount 'use for debug make sure rsTmp exist

    ' It work if "Employees" is a local table
    ' dbExternal.Execute "SELECT EmployeeID, LastName, FirstName, Title INTO NewTable FROM Employees WHERE (((EmployeeID)>5))"


    ' Cannot found rsTmp
    dbExternal.Execute "SELECT EmployeeID, LastName, FirstName, Title INTO NewTable FROM rsTmp WHERE (((EmployeeID)>5))"

    End Function

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi marlai

    You can't use a recordset like that - it is a cursor and as such can anly be accessed row by row rather than as a set (like a table or query).
    Depending on how you want to use the data, you may not need to create a table.

    However - two alternatives come to mind to populate the table
    1) Loop through the reocrdset record by record and run an insert statement each pass.
    2) Create a heterogenious (spelling???) query e.g.:
    Code:
     Currentdb.Execute "SELECT * INTO NewTable
    FROM Employees IN 'c:\temp\access\Northwind.mdb'"
    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Dec 2005
    Posts
    4
    Thank you for advice.
    1. I do not want to use loop through, may be it take long time to process.
    2. I cannot direct copy from external because I need to process the data using Recordset, then copy them to table.
    3. I know ===Set qdfTmp = dbExternal.CreateQueryDef("NewQuery", "SELECT * FROM Employees WHERE (((Employees.EmployeeID)>5))")=== can create "NewQuery" into access and can refer it using asses command. Any similar command like this can create table?

    Best Regards.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by marlai
    Thank you for advice.
    1. I do not want to use loop through, may be it take long time to process.
    2. I cannot direct copy from external because I need to process the data using Recordset, then copy them to table.
    3. I know ===Set qdfTmp = dbExternal.CreateQueryDef("NewQuery", "SELECT * FROM Employees WHERE (((Employees.EmployeeID)>5))")=== can create "NewQuery" into access and can refer it using asses command. Any similar command like this can create table?

    Best Regards.
    Hi
    1) Agreed - unless it is a very small amount of data you bet you don't want to.
    2) Why do you need to use a recordset? If you really insist on using one then run the heterogeneous query. Then open a recordset on the new table. Open the recordset for editing and then commit the edits back to the table. Having said that I would (not knowing your precise requirements) probably prefer to do whatever data cleaning you need to do with SQL (i.e. set based) rather than with a cursor (row based).
    3) You can do this in two ways. My preferred method is SQL (however you can get unstuck with some data types). e.g.
    Code:
     dbExternal.Execute "CREATE TABLE TableDDL (MyCol Text(25))"
    Otherwise you can instantiate a NEW TableDef object, set its properties and add fields to it. Once created append to the tabledefs collection. Check out Help for more info

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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