Results 1 to 5 of 5

Thread: change msaccess with ms sql sever 2012

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Aug 2011
    Posts
    17

    change msaccess with ms sql sever 2012

    hi all,

    i want to know what in need to do if i change msaccess db with sql server 2012 ?
    what need to install on the user pc and what need to install on the server?
    also what must be introduced in the installation package? (this for a new and clean installation)

    thx for any sugestion

  2. #2
    Fanatic Member
    Join Date
    Jan 2013
    Posts
    759

    Re: change msaccess with ms sql sever 2012

    If you want to do the whole job in one go, then you'll need SqlServer (database) installed on the server and a suitable client library on the client machines. (These days, I'd be very surprised if Windows didn't come with [at least] one built in).
    Then you'll need to "tweak" your queries - the biggest challenge you'll probably face is Dates; Access uses hashes to delimit Date/Time literals, SqlServer uses regular, single-quotes.
    Code:
    Access: 
        select a, b, c_date from d where c_date >= #2001/01/01# ; 
    
    SqlServer: 
        select a, b, c_date from d where c_date >= '2001/01/01' ;
    Depending on your circumstances, an alternative might be to move just the data, without changing the application.
    You can transfer the data from Access to SqlServer and leave "Linked Tables" in the Access database that "point to" the new SqlServer tables. Your application, which currently talks to the Access database, remains completely unchanged. It might be an option if you're on a tight schedule.

    Regards, Phill W.

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Aug 2011
    Posts
    17

    Re: change msaccess with ms sql sever 2012

    my goal is to improve the "speed" when i work through vpn-connection.
    i presume the sql server database type it is more efficient.

  4. #4
    Fanatic Member
    Join Date
    Jan 2013
    Posts
    759

    Re: change msaccess with ms sql sever 2012

    That depends on what bits are running where.

    If you've got the Access database on your local machine (VPN client) then things are as good as they're going to get (without a bigger, faster machine). I'm guessing that's not the case.

    If you're connecting to a remote machine that has the Access database, then the bottleneck might be the VPN connection, feeding [the image of] all that screen real estate back to your client machine. Nothing to do with the database.

    Finally, it might be that the database itself appears to be slow.
    I say that because, in the vast majority of cases, it's the queries, not the database, that are at fault. Sadly, Access has few, if any, tools to assist you with diagnosing problems like this. That, in itself, is a good enough reason to port this to SqlServer.
    You'll have to review each query and see whether the indexes you've got defined properly support those queries.

    Regards, Phill W.

  5. #5
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: change msaccess with ms sql sever 2012

    Quote Originally Posted by Phill.W View Post
    If you're connecting to a remote machine that has the Access database, then the bottleneck might be the VPN connection, feeding [the image of] all that screen real estate back to your client machine. Nothing to do with the database.
    This sounds like a bizarre statement, but perhaps you are assuming the use of some remote desktop tech, either normal Terminal Services or Citrix piggybacked on top of that, etc. It seems more likely the VPN link is being used to get to a remote file share where the MDB file(s) live. This could indeed be pretty difficult to live with and probably shouldn't even be attempted unless you want to deal with database corruption frequently as a tradeoff against a better architecture. Sort of the cave man's choice.

    The cave man's second choice is a client/server DMBS, MS SQL Server being one of many options. This allows most cave man coding techniques to be used, making it a popular option. The downside is that client/server DBMSs aren't "free" even when the software required doesn't cost any money.

    They usually require a dedicated box in order to achieve decent performance, SQL Server in particular being designed to own the machine it runs on. Then there is the cost of care and feeding on top of that, usually requiring somebody to play DBA which isn't always a trivial matter even though day to day operations are pretty routine.

    If there is a VPN in the picture it almost always implies a WAN connection. In such cases it is preferable to engineer something more sophisticated than connections to a client/server DBMS. We used to have a good infrastructure for this (Remote Data Service) but it has been deprecated for a long time and the required components are being phased out. RDS was cannibalized to favor .Net web services approaches.

    I think most people will just punt and accept the cost of dealing with SQL Server. It might be the most friendly option for the average cave man coder. However it cannot make poor application design and bad queries go away by magic. If you are used to slurping large rowsets back to your client code and walking through and updating Recordset objects directly such a move might not buy you a whole lot.
    Last edited by dilettante; Mar 2nd, 2015 at 12:09 PM.

Posting Permissions

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



Click Here to Expand Forum to Full Width