Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2004
    Location
    Dundee, Scotland
    Posts
    107

    Unanswered: SQL Server to MS Access data export

    Hi, I'd be delighted to receive some suggestions re how best to approach the following MS SQL Server data export scenario.

    Its a VB.NET, MS SQL Server 2000 web application project. An ad-hoc reporting requirement is that the end-user can click a button on the web application to receive a link to download a data export in MS Access. I have a prototype working which executes a series of DTS packages to create and populate tables in a blank MS Access database from the SQL Server database

    I found the DTS export wizard helpful in that it makes light work of the numerous lookup tables, but I am looking for suggestions as to how best to export the 8+ data tables?

    In my prototype I currently use a DTS package to export the full contents of each data table but I need to get it to filter the export of each by two parameters. I tried to use the DTS package global variable approach but although this works with a simple query, I appear to need to use nested queries to identify which table rows to include in the export (at which point DTS seems to give up).

    I played around with the linked server functionality today but I don't see how that can help me. I was only able to execute a query on the MS Access database. I was hoping to maybe be able to do a "select * into <table_name> where ... " from MS SQL Server to MS Access.

    I also tried editing the DTS package to call a user defined function (UDF) but DTS didn't seem to want to let me pass parameters via global variables. It only worked if I hard-coded the parameters which would not be satisfactory.

    Should I be looking at doing it via OleDbConnection in VB.NET?

    Many thanks,

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why don't you just give them an access database that is linked to sql server and grant read only access to it?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jul 2004
    Location
    Dundee, Scotland
    Posts
    107
    I'd prefer to refine the snapshot approach.

    While linking MS Access to the central SQL Server is possible technically, its not likely to be used on this specific project.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by yellowmarker
    While linking MS Access to the central SQL Server is possible technically, its not likely to be used on this specific project.
    While "possible"? It's how most sane database applications work.

    Why dont you explain your problem in business terms?

    If you continue down this path, tell me, do the users get to update their local data?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jul 2004
    Location
    Dundee, Scotland
    Posts
    107
    I've got my approach working for the moment...here's more info just for completeness (I'll keep an eye out for any further replies):

    end-users have been using isolated MS Access databases at multiple geographic locations. moving to a central web-application is a priority so that ongoing application maintenance by IT staff is made more efficient.

    the end-users are to type data into the new central web-based application where standard reports are available. end-users need access to the data in MS Access to continue to do ad-hoc reporting. they might download a data export snap-shot (a generated .mdb file) twice a month to their PC, which they'd link to an existing 'front-end' .mdb file on their PC which would contain their ah-hoc queries. end-users would no longer input data into MS Access.

    I made some progress today by using UDFs and multiple DTS packages. As there is only 8 or so data tables it isn't too inconvenient to hard code the UDF parameters in the DTS packages, putting logic in a stored procedure to decide which to call. I'm still gaining efficiency using DTS as it makes the export of many lookup tables quite efficient.

    My only bug-bear is that if a DTS package fails unexpectidly, the temporary .ldb(?) MS Access file that handles user access doesn't dissapear on the web server so manual intervention is required to remove it before the process can be run again.

    Cheers,

Posting Permissions

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