Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Location
    Latvia
    Posts
    2

    Red face Unanswered: Export SQL server query data to Access database

    HI!

    First, i am sory about my english.
    I have such situation. In my project as database server we have SQL 2000 and as client applications we use Access Project. (adp)

    In this project we have one form where users select data from different tables from server. As result form generate SQL query.
    For example one very simple generated query look like:

    SELECT val.uuk,
    case when ac.record_id is null then '' when ac.flag_code=1 then ac.code + ISNULL(val.mark,'') else '' end AS Code,
    case when ac.record_id is null then '' when ac.flag_name=1 then ac.name end + case when ac.flag_unit = 1 then ' ('+ac.measure+')' else '' end AS CName,
    val.v1, val.v2, val.v3, val.v4, r1.Nos AS Nos1
    FROM #RawVal153 val
    LEFT OUTER JOIN (SELECT a.record_id, a.code, a.name, a.measure, b.flag_code, b.flag_unit, b.flag_name
    FROM MetaDataBase.dbo.me_attrib_classif_codes a
    JOIN MetaDataBase.dbo.me_attrib_classificators b ON (a.classif_id = b.classif_id)) ac ON ((ac.record_id = val.record_id))
    LEFT OUTER JOIN (SELECT resp.uuk, resp.Nos
    FROM mi_respondents resp
    INNER JOIN mi_resp_quests rq ON (resp.resp_id=rq.resp_id)
    INNER JOIN mi_survey_instances i ON (rq.instance_id=i.instance_id)
    WHERE (i.version_id = 67 and i.period = '2001')) r1 ON (r1.uuk = val.uuk)
    ORDER BY val.uuk,
    case when ac.record_id is null then '' when ac.flag_code=1 then ac.code + ISNULL(val.mark,'') else '' end,
    case when ac.record_id is null then '' when ac.flag_name=1 then ac.name end + case when ac.flag_unit = 1 then ' ('+ac.measure+')' else '' end


    And the fragment from application Where we use this query is:

    Dim comXL As ADODB.Command
    Dim RecV As ADODB.Recordset
    .... 'There ir code, where create TMP tables and generate final SQL query
    Set comXL = New ADODB.Command
    comXL.CommandTimeout = 0
    comXL.CommandType = adCmdText
    comXL.CommandText = "Generated SQL query"
    comXL.ActiveConnection = Get_Conn() ' function which returns connection variable to SQL server
    Set recV = comXL.Execute()

    There is no problem to export this recordset to Excel - all is very simple and quite fast. And i don't need to know anything about columns count and data types in my recordset.
    But now we need to export this recordset to Access database (*.mdb)
    And I need help. May be someone have any ideas or experience in such situation.

    Thanx.

  2. #2
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    OK
    when you say exporting to Excel I doubt that you are creating an XLS file - I assume you are exporting to text - if so excel will read that without a hitch - but I do not believe there is an option to export to MDB format per se - try create table method from within Access to create the table in you access project.

    Does that help?

  3. #3
    Join Date
    May 2003
    Location
    Latvia
    Posts
    2
    For export to Excel we do not create Excel file. We open Excel application and copy directly data from recordset to Excel. Excel active sheet object have such method as Excel.ActiveSheet.CopyFromRecordset

    For export to Excel I use such approach (Vb code):
    'RecV is my generated SQl query ADODB.recordset

    Dim xl As Excel.Application
    Dim ws As Excel.Worksheet
    Dim wb As Excel.Workbook

    Set xl = New Excel.Application
    Set wb = xl.Workbooks.Add
    Set ws = wb.ActiveSheet
    With ws.Range("A2") 'Starting from second row I export to Excel "recV" recordset
    .CurrentRegion.Clear
    .CopyFromRecordset recV 'Here All data from recordset are copied to EXCEL
    End With
    xl.Visible = True

    In such way I directly copy recordset data to new excel file

    For same recordset export to Access I tried such solution:
    1) I create Access database, using DAO
    2) I create table in Access database (*.mdb) using database.tabledefs collection
    3) In created table I create fields from recV.fields collection
    Here is one problem, that recV is ADO.recordset and for Access I use DAO. So problem is converting field types from ADO.field types to DAO.field types.
    4) I copy record by record from ADO recordset to DAO recordset. And this operation is quite fast. At the beginnig I have doubt that exactly this operation will be very time-consuming. But performance is acceptable for us.

    In such way I resolve my problem. So, if anyone else have such problems - try my approach.

    Best wishes to all.

  4. #4
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    That is a good solution.

Posting Permissions

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