Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2007
    Posts
    2

    Unanswered: Connecting an Access database to SQL Server

    Hi there,

    Not sure where this post is best placed, to be honest... Here or the SQL Server forums?

    Anyway... The problem I'm having is this:
    I'm working with a piece of software that uses an Access 97 database to store test information. I've got a customer that wants to be able to connect this database to his existing SQL Server database, in order to access some of his test results.

    What I need, is a method that every time the user presses "test" in the application, and generates a new record in the Access database, that a corresponding record will appear in the SQL Server database. Ideally, this wouldn't require me to make any changes to the application, just the database(s).

    I'm pretty new to working with databases in this style... Anyone able to offer any help? Even a starting point would be great!

    Thanks very much,
    Neil

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    several posisble options

    one to do it as a batch process.. if you had a flag in the Jet database that could be set once the record has been posted into the SQL Server then do say a nightly/hourly or whatever update setting the flag on completion. this could be done say as a batch file, culling all the records from access and then inserting into the server using either DTS or another access process. if you have a date as part of the test record then you could save a time stamp of when the last update was done and then use that in place of a flag.

    alternatively you could write the record at the same time the 'test' button duplicate that process using a pass through query to the server.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2007
    Posts
    14
    You can just link the tables to the sql database. So the linked access database table will actually be the sql server table. Right mouse click in tables and select link tables. When the users presses test, you just run an append query from your form to the linked table. Of course you need to be connected to the server (e.g. ODBC).

    I use access as a reporting tool for a sql database. Works great, beats all the weird reporting tools available on the market.

    Ben

  4. #4
    Join Date
    Mar 2007
    Posts
    2
    Thanks for the help guys... It's definitely given me some food for thought.

    healdem:
    I think the batch process would do the job, but ideally I'd like to get "live" data into the SQL Server database.
    I'll look into the pass-through query thing though... Thanks.

    BMore:
    This looks like what I'm aiming at, except it seems to be the wrong way round!
    I did the "Link Tables" thing in the Tables tab of my Access Database, and managed to successfully connect to the SQL Server one. However, I need to really get the data the other way, so that the Access stuff shows up in SQL Server. Do you know of any way of doing that?

    Thanks again,
    Neil

  5. #5
    Join Date
    Mar 2007
    Posts
    14
    Neil,
    You can update the linked tables, same as your own access tables. I've done it many times and it works great. Of course your SQL-Server db-administrator needs to allow this (that is the only challenge here).
    Ben

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    my thruppence:
    don't link - the overhead is pointless
    do whatever you want to do locally and issue a DSN-less UPDATE or INSERT to the server.

    izy
    currently using SS 2008R2

Posting Permissions

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