Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527

    Unanswered: Transfering data from Access to SQL Server

    I've been asked to transfer a database from Access to SQL Server. I have no experience of Access - are there any issues I should look out for and are there simple tools I could use. I was planning on just recreating each table in turn on SQL Server and then just using whatever equivalent there is to BCP / bulk copy to transfer the data.

    Would the indexes be similar to those on SQL Server? is the data accessed via standard SQL? does it have stored procs?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Access can talk to virtually any datastore, but the default datastore (ie if you don't specify a different datastore), is JET. JET is a SQL db, but like all SQL db's it has its own wrinkles, however its arguably less compliant with ANSI SQL. it doesn't have things like triggers, stored procedures and the like.

    quick and dirty technique would be to use the 'upsizing wizard' which does a fair job of transferring the data and from Access/JET to SQL server.; but like all such tools it deson't do a perfect job.

    you don't mention whether the new app will remain in Access, ie you are going to use Access as the front end and SQL Server as a backend. if that ia the case then to leverage the power of the server backend it will almost certainly need rewriting to take advantage of unbound forms and recordsets. if you are 'thunking' of rewriting the access app then get a copy of the Access Developer Handbook for your target version of Access, its a two tome £50ish book but invaluable in my books.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by healdem
    it doesn't have things like triggers, stored procedures and the like.
    That's not actually true, however I have yet to see a JET sproc in production.

    rjabarov (sp?) who posts in the SQL Server forum has posted his own script for upsizing from Access to SQL Server. You could pm him or search the SQL Server forum.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Many thanks for all the info. If it's just data transfer they need then it sounds reasonably simple but if I need to adjust all the forms to match then things may get more complicated - from what I gather there can be issues with (bound) forms either pulling too many rows or causing null errors etc. The fact I'm having to guess what a bound form is will probably rule me out the bidding we'll see.

    I'll look for that script.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    bound controls means that access does the interacting with the data itself, meaning you have less to do coding wise, but it can make the application slow and ineffiecient.

    unbound controls, requires you to do a lot more work, especailly the db interaction, it does mean yuou have explicit control of what is going on. there is precious little difference in my mind and experience between a well crafted Access app and a well crafted vb, c+, .NET app.

    however many people fall into the trap of thinking just bolting on a serbver back end will automatically make an Access application much faster. I'd argue that often what you get is the worst of both worlds, the access app still tries to handle data as if it was a JET db and you have the server overhead, the netwrok overhead...

    I think you need to clearly understand what it is the client wants.

    there is nothign stopping you having a server back end in a bound application.. it just won't be especailly responsive or efficient
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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