linking to SQL Server db table

Discussion in 'Access Forms Coding' started by colmkav, May 15, 2012.

  1. colmkav

    colmkav Guest

    Hi,

    I may need to convert data stored in an Access database to a SQL
    server db due to space issues.

    Can you pls tell me whether I can simply link to the SQL Server table
    through linked tables and use all the same queries that work on my
    Access tables? What issues do I need to resolve if any?

    thx
    Colm
     
    colmkav, May 15, 2012
    #1
    1. Advertisements

  2. colmkav

    Ron Weiner Guest

    colmkav formulated the question :
    > Hi,
    >
    > I may need to convert data stored in an Access database to a SQL
    > server db due to space issues.
    >
    > Can you pls tell me whether I can simply link to the SQL Server table
    > through linked tables and use all the same queries that work on my
    > Access tables? What issues do I need to resolve if any?
    >
    > thx
    > Colm


    The simple answer is "Yes You Can". However there are many security
    and performance gains that you might avail yourself of as you gain
    experience with Sql. I suspect you will be pleasantly surprised at how
    easy this change is.

    Rdub
     
    Ron Weiner, May 16, 2012
    #2
    1. Advertisements

  3. colmkav

    colmkav Guest

    On May 16, 2:23 am, Ron Weiner <> wrote:
    > colmkav formulated the question :
    >
    > > Hi,

    >
    > > I may need to convert data stored in an Access database to a SQL
    > > server db due to space issues.

    >
    > > Can you pls tell me whether I can simply link to the SQL Server table
    > > through linked tables and use all the same queries that work on my
    > > Access tables? What issues do I need to resolve if any?

    >
    > > thx
    > > Colm

    >
    > The simple answer is "Yes You Can".  However there are many security
    > and performance gains that you might avail yourself of as you gain
    > experience with Sql.  I suspect you will be pleasantly surprised at how
    > easy this change is.
    >
    > Rdub


    Thanks. So you are saying I can just use the same queries I had that
    work on my Access tables and now can work on SQL Server tables?

    I havent programmed in VBA Access for awhile but I recall runing
    stored procedures (writen in PL SQL) when performing some tasks on the
    (Oracle tables). Any reason why I would do this rather than the linked
    tables? Perhaps speed of execution?

    regards
    Colm
     
    colmkav, May 16, 2012
    #3
  4. colmkav

    colmkav Guest

    Thanks.

    You are saying that I can still use all the same Access queries
    without changing them? I previously recall using stored procedures
    consisting of PL SQL on Oracle databases. Any reason I should have
    done that? Perhaps speed of execution? \

    I havent coded in VBA for quite sometime so have forgetten the reasons
    for doing things certain ways.

    cheers
    Colm
     
    colmkav, May 16, 2012
    #4
  5. colmkav

    Ron Weiner Guest

    colmkav formulated on Wednesday :
    > On May 16, 2:23 am, Ron Weiner <> wrote:
    >> colmkav formulated the question :
    >>
    >>> Hi,

    >>
    >>> I may need to convert data stored in an Access database to a SQL
    >>> server db due to space issues.

    >>
    >>> Can you pls tell me whether I can simply link to the SQL Server table
    >>> through linked tables and use all the same queries that work on my
    >>> Access tables? What issues do I need to resolve if any?
    >>> thx
    >>> Colm

    >>
    >> The simple answer is "Yes You Can".  However there are many security
    >> and performance gains that you might avail yourself of as you gain
    >> experience with Sql.  I suspect you will be pleasantly surprised at how
    >> easy this change is.
    >>
    >> Rdub

    >
    > Thanks. So you are saying I can just use the same queries I had that
    > work on my Access tables and now can work on SQL Server tables?
    >
    > I havent programmed in VBA Access for awhile but I recall runing
    > stored procedures (writen in PL SQL) when performing some tasks on the
    > (Oracle tables). Any reason why I would do this rather than the linked
    > tables? Perhaps speed of execution?
    >
    > regards
    > Colm


    Yup, in almost every case the Access queries will work the same way
    they did against Access tables, and you might even see an increase in
    performance with out changing anything.

    However if you want/need to dramatically increase your apps performance
    you will probably want to use Access pass-thru queries written with Sql
    Servers TSql language.

    If you are competent in writing efficient Access queries you'll
    discover that there are some minor syntax and key word differences.
    However once learned, you could see VERY impressive performance
    differences on some queries.

    Also Sql Server is FAR more fault tolerant that a file based Access
    backend database.

    Good luck with your project.

    Ron W
     
    Ron Weiner, May 16, 2012
    #5
  6. colmkav

    Tony Toews Guest

    On Tue, 15 May 2012 05:30:16 -0700 (PDT), colmkav
    <> wrote:

    >Can you pls tell me whether I can simply link to the SQL Server table
    >through linked tables and use all the same queries that work on my
    >Access tables? What issues do I need to resolve if any?


    You've got some very good answers already.

    See my Random Thoughts on SQL Server Upsizing from Microsoft Access
    Tips page at http://www.granite.ab.ca/access/sqlserverupsizing.htm.
    Also see my blog postings on this topic at
    http://msmvps.com/blogs/access/archive/tags/SQL+Server/default.aspx

    There is a tool from the SQL Server group which is better than the
    included SQL Server Upsizing Wizard.
    SQL Server Migration Assistant for Access (SSMA Access)
    http://www.microsoft.com/sqlserver/en/us/product-info/migration-tool.aspx#Access
    --
    Tony Toews, Microsoft Access MVP
    Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
    Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
    For a convenient utility to keep your users FEs and other files
    updated see http://www.autofeupdater.com/
     
    Tony Toews, May 18, 2012
    #6
  7. colmkav

    colmkav Guest

    On May 18, 4:13 am, Tony Toews <> wrote:
    > On Tue, 15 May 2012 05:30:16 -0700 (PDT), colmkav
    >
    > <> wrote:
    > >Can you pls tell me whether I can simply link to the SQL Server table
    > >through linked tables and use all the same queries that work on my
    > >Access tables? What issues do I need to resolve if any?

    >
    > You've got some very good answers already.
    >
    > See my Random Thoughts on SQL Server Upsizing from Microsoft Access
    > Tips page athttp://www.granite.ab.ca/access/sqlserverupsizing.htm.
    > Also see my blog postings on this topic athttp://msmvps.com/blogs/access/archive/tags/SQL+Server/default.aspx
    >
    > There is a  tool from the SQL Server group which is better than the
    > included SQL Server Upsizing Wizard.
    > SQL Server Migration Assistant for Access (SSMA Access)http://www.microsoft.com/sqlserver/en/us/product-info/migration-tool....
    > --
    > Tony Toews, Microsoft Access MVP
    > Tony's Main MS Access pages -http://www.granite.ab.ca/accsmstr.htm
    > Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/
    > For a convenient utility to keep your users FEs and other files
    >   updated seehttp://www.autofeupdater.com/


    thanks for the help!
     
    colmkav, May 21, 2012
    #7
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.