Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2007
    Posts
    7

    Unanswered: Need suggestions on issue surrounding Access

    Hello,

    I have a question and want to know if its possible since I am rather new to programming side of things. Here is my situation:

    There is a database here at my work that I have to query via excel to get infomation. I do this via ODBC Connection. I do this via Excel (Microsoft Query). I then am using this information in Infopath which needs the data to be in Access or SQL Server but dont have access directly to the server so have to put in Access.

    1. I would need a macro/code for running the SQL in Excel which queries the data from the SQL Server database.
    2. I would need Access macro/code to automatically import the data from Excel.
    3. Then I would like to automate this on a daily basis so then I would a batch file correct?


    Basically I want the Access Database to be updated automatically everyday from the SQL that I do in Excel which comes from the Server, so that the Infopath form has the most up-to-date information everyday.

    Bascially this is what happens now:

    SQL Server--->Excel Query--->Access--->Infopath

    The main thing is that Infopath has to be used with Access. Yet I have to SQL Query the Server for the data I need.

    How can get the data from the Server to Access the easiest and have this process be automated?

    Is this something that can be accomplished? Or...does anyone have any ideas of how to do this differently (maybe easier) but accomplish the same ends?

    Thank you for any help and insight and hope I wasnt confusing...

    Since this is revolving around Access thought this forum would be appropriate.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I suspect the easiest way to get data from SQL Server to Access, including automating it, would be a DTS package in SQL Server (more info in BOL). Alternatively, you could create a process in Access to import the data, and run that on a schedule.
    Paul

  3. #3
    Join Date
    Jan 2007
    Posts
    7
    Thanks for the reply. Now since I am rather unfamiliar with DTS will that just transfer the data once or keep it updated?

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I said "including automating it" to indicate that it could be run automatically (daily in your case).
    Paul

  5. #5
    Join Date
    Jan 2007
    Posts
    7
    Thanks. I have Microsoft SQL Server 2005 Backward Compatibility Components package via Microsoft downloads now. I have it up on cmd prompt, is there a gui for this?

    I am not sure what do from here though....hmmm. Is this what I was looking for?

    Attached a screenshot.
    Attached Thumbnails Attached Thumbnails untitled2.gif  

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    There is a GUI, in Enterprise Manager (we have 2000, so not sure if it's different).
    Paul

Posting Permissions

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