Access to SQL server - Page 2


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Page 2 of 2 FirstFirst 12
Results 16 to 30 of 30

Thread: Access to SQL server

  1. #16
    Alan Guest

    Re: Access to SQL server


    Nate,

    You can use the upsize wizard, use DTS in SQL, use any number of modeling
    tools or simply create the table structure in SQL to match your Access db.
    In any case, so long as the db structure is the same, you will be able to
    link the tables via ODBC, and your MS Access app ought to work just as it
    did previously. From that point you could then adapt your application to
    some of the beneficial features of MS-SQL Server as time allows.

    However, with the usage and data size you have outlined, assuming a reasonably
    prudent database programming style, MS Access is absolutely acceptable and
    robust enough to serve your stated requirements.

    Alan
    astruthers@infogen.net

    "Nate" <nczimm@msn.com> wrote:
    >
    >Can an application which is build is MS Access run against a SQL server

    database
    >without changing the SQL code?
    >
    >Also would you use Access in a five user environment. I don't know the size
    >of the database, it is probably under 100k, but I know that about 2000

    records
    >are added per month and there are probably 30,000 records now. ?
    >
    >Thanks
    >
    >Nate



  2. #17
    Nate Guest

    Re: Access to SQL server


    "Alan" <astruthers@infogen.net> wrote:
    >
    >Nate,
    >
    >You can use the upsize wizard, use DTS in SQL, use any number of modeling
    >tools or simply create the table structure in SQL to match your Access db.
    > In any case, so long as the db structure is the same, you will be able

    to
    >link the tables via ODBC, and your MS Access app ought to work just as it
    >did previously. From that point you could then adapt your application to
    >some of the beneficial features of MS-SQL Server as time allows.
    >
    >However, with the usage and data size you have outlined, assuming a reasonably
    >prudent database programming style, MS Access is absolutely acceptable and
    >robust enough to serve your stated requirements.
    >
    >Alan
    >astruthers@infogen.net
    >
    >"Nate" <nczimm@msn.com> wrote:
    >>
    >>Can an application which is build is MS Access run against a SQL server

    >database
    >>without changing the SQL code?
    >>
    >>Also would you use Access in a five user environment. I don't know the

    size
    >>of the database, it is probably under 100k, but I know that about 2000

    >records
    >>are added per month and there are probably 30,000 records now. ?
    >>
    >>Thanks
    >>
    >>Nate

    >



  3. #18
    CyberDude Guest

    Re: Access to SQL server


    Hi Nate,

    do you mean that you would like to have an Access and SQL database server,
    or would you like to use access as your desktop interface with your SQL server?



  4. #19
    Paul Guest

    Re: Access to SQL server


    "Nate" <nczimm@msn.com> wrote:
    >
    >Can an application which is build is MS Access run against a SQL server

    database
    >without changing the SQL code?
    >
    >Also would you use Access in a five user environment. I don't know the size
    >of the database, it is probably under 100k, but I know that about 2000

    records
    >are added per month and there are probably 30,000 records now. ?
    >
    >Thanks
    >
    >Nate



  5. #20
    Paul Guest

    Re: Access to SQL server


    Nate,
    Use ODBC direct and pass through queries to get the best performance.
    Remember that using ODBC direct and Pass throughs you have to learn the native
    TSQL. I'm an long time Access user and TSQL is a great addition to your skills.
    Avoid linking to SQL server tables if you can help it, and do it all in VBA
    if you can.

    Paul

    "Nate" <nczimm@msn.com> wrote:
    >
    >Can an application which is build is MS Access run against a SQL server

    database
    >without changing the SQL code?
    >
    >Also would you use Access in a five user environment. I don't know the size
    >of the database, it is probably under 100k, but I know that about 2000

    records
    >are added per month and there are probably 30,000 records now. ?
    >
    >Thanks
    >
    >Nate



  6. #21
    ADH Guest

    Re: Access to SQL server


    "Nate" <nczimm@msn.com> wrote:
    >
    >Can an application which is build is MS Access run against a SQL server

    database
    >without changing the SQL code?
    >
    >Also would you use Access in a five user environment. I don't know the size
    >of the database, it is probably under 100k, but I know that about 2000

    records
    >are added per month and there are probably 30,000 records now. ?
    >
    >Thanks
    >
    >Nate


    Nate,
    I read the answers to your (important) question.
    1 Major argument to add: Network !
    As you know when accessing Jet all the data involved moves between server
    and client. This on overloaded or "Busy" networks can be a killing factor.
    SQL Server, when using smart store procedures sends to you only (and only)
    the data you really asked for.
    This by itselves is a firm and main reason to make the migration.
    I am sure you will in some conditions see improvments in response time even
    with a single client.

  7. #22
    Sven Aelterman Guest

    Re: Access to SQL server


    You may have to change the wildcards in your WHERE clause: from * to % and
    from ? to _. The other parts will remain unchanged.

    Using Access in a 5-user environment is not a problem, but just don't go
    and use it over the Internet/intranet. Use a client/server design.

    The size of the database is usually not an issue, as long as you compact
    it from time to time. I have had a 50 MB Access mdb running in a client/server
    environment with 7 users.

    Greets

    Sven.

    "Nate" <nczimm@msn.com> wrote:
    >
    >Can an application which is build is MS Access run against a SQL server

    database
    >without changing the SQL code?
    >
    >Also would you use Access in a five user environment. I don't know the size
    >of the database, it is probably under 100k, but I know that about 2000

    records
    >are added per month and there are probably 30,000 records now. ?
    >
    >Thanks
    >
    >Nate



  8. #23
    Rich Guest

    Re: Access to SQL server


    Nate -

    You can, in fact, use Access to bump up against a SQL Server database - but
    the reference to the SQL server tables is a bit tricky.

    Can you do it without changing your existing code...sure.

    The reason it is tricky is this:

    You need to use ODBC to connect to the SQL db and when you LINK to those
    tables, Access creates the link with a dbo_ prefix. For instance, a CUSTOMERS
    table in SQL Server can be linked from Access, but when it does, it links
    as "DBO_CUSTOMERS".

    These names, however, are just nicknames, if you will, and can be renamed
    to whatever you want. So you could simply rename the link to "CUSTOMERS"
    after the link is made.

    There are some other tricky parts, including differences between how Access
    treats some data formats (i.e,. a NUMERIC field in a table) and how SQL Server
    treats them. Unfortunately, I haven't found a good cross reference as to
    the differences...but they do exist.

    My final comment, however, isn't on the ABILITY to link to SQL Server, but
    more so the RATIONALE. Linking to a SQL Server database isn't going to solve
    other problems, such as performance, scalability, etc. - because you are
    still confined to the limitations of the Access (JET-based) db engine. While
    the data will reside in the SQL Server tables, the processing still occurs
    in Access.

    Furthermore, you can't link to STORED PROCS or several other performance
    enhancing features you gain by using SQL Server.

    As for your final question...regarding volume...yes, Access is PLENTY capable
    of managing 5 users with the limited growth you have mentioned.

    As a general rule, you should probably tend away from Access once your tables
    grow beyond 100,000 rows of data.

    Hope that helps. If you have more questions, please feel free to contact
    me, or the consulting firm I work for, directly.

    Our web site is: www.manleygroup.com and my email address is: rich.anderson@manleygroup.com

    Regards,
    Rich



    "Nate" <nczimm@msn.com> wrote:
    >
    >Can an application which is build is MS Access run against a SQL server

    database
    >without changing the SQL code?
    >
    >Also would you use Access in a five user environment. I don't know the size
    >of the database, it is probably under 100k, but I know that about 2000

    records
    >are added per month and there are probably 30,000 records now. ?
    >
    >Thanks
    >
    >Nate



  9. #24
    Paul Mc Guest

    Re: Access to SQL server



    >My final comment, however, isn't on the ABILITY to link to SQL Server, but
    >more so the RATIONALE. Linking to a SQL Server database isn't going to

    solve
    >other problems, such as performance, scalability, etc. - because you are
    >still confined to the limitations of the Access (JET-based) db engine.

    While
    >the data will reside in the SQL Server tables, the processing still occurs
    >in Access.
    >
    >Furthermore, you can't link to STORED PROCS or several other performance
    >enhancing features you gain by using SQL Server.


    G'day,

    This is not all strictly true. For one thing, you can use pass trough queries
    to explicitly send SQL for server side processing. This includes the ability
    to execute stored procedures (I am not sure why you would try to actually
    Link to an SP).

    Additionally, if you run the profiler and do some testing you will see that
    if you send Jet a query that contains only linked tables from the server,
    and no Jet/Access specific stuff(such as crosstab queries, or functions in
    the select list/where clause) then the query IS sent to the server for execution
    - Jet is smart enough for that, anyway.

    Cheers,
    Paul Mc


  10. #25
    Mukesh Guest

    Re: Access to SQL server


    Hi

    You can use linked tables to access data from the SQL Server, though for
    your kind of application/usage Acess is sufficient for now and may be an
    year.

    Hope this helps

    Take Care


    "Nate" <nczimm@msn.com> wrote:
    >
    >Can an application which is build is MS Access run against a SQL server

    database
    >without changing the SQL code?
    >
    >Also would you use Access in a five user environment. I don't know the size
    >of the database, it is probably under 100k, but I know that about 2000

    records
    >are added per month and there are probably 30,000 records now. ?
    >
    >Thanks
    >
    >Nate



  11. #26
    S. Stewart Guest

    Re: Access to SQL server


    "Nate" <nczimm@msn.com> wrote:
    >
    >Can an application which is build is MS Access run against a SQL server

    database
    >without changing the SQL code?
    >
    >Also would you use Access in a five user environment. I don't know the size
    >of the database, it is probably under 100k, but I know that about 2000

    records
    >are added per month and there are probably 30,000 records now. ?
    >
    >Thanks
    >
    >Nate


    Yes, you can use an Access front end with a SQL Server back end. I am working
    on a project tracking application now that has about 25-35 concurrent users
    at one time, and there has been over 56,000 transactions added in the last
    2 months. Some of the SQL will need to be changed, but most will work in
    both Access and in SQL Server.

    S. Stewart


  12. #27
    Ray Mogarte Guest

    Re: Access to SQL server


    Nate,

    We are currently using Access (developed under VB5) for a four user environment
    and very soon we will add another user. Our access database is already nearing
    one gigabyte. We have one table that has more than 200,000 records. So far
    we have not encountered any problem yet. But still, we do not know as to
    how much more can an Access database handle.

    Regards,
    Ray
    "Nate" <nczimm@msn.com> wrote:
    >
    >Can an application which is build is MS Access run against a SQL server

    database
    >without changing the SQL code?
    >
    >Also would you use Access in a five user environment. I don't know the size
    >of the database, it is probably under 100k, but I know that about 2000

    records
    >are added per month and there are probably 30,000 records now. ?
    >
    >Thanks
    >
    >Nate



  13. #28
    Jo Goos Guest

    Re: Access to SQL server


    With good database design and tuning, theoreticaly I think you can create
    a 2 GB Access database for 100-users. The MSDN Library (http://msdn.microsoft.com/)
    gives you a comparison between SQL Server and Access.

    Practicaly, I experienced a problem (occurred very rarely) when a user connected
    to an Access database. Access writes some connected user information in
    an .ldb file. It was this .ldb file that caused the error because multiple
    users where trying to connect to the database at the same time.

    For a five-user environment, I would recommend Access.

    Kind Regards,
    Jo Goos



    "Nate" <nczimm@msn.com> wrote:
    >
    >Can an application which is build is MS Access run against a SQL server

    database
    >without changing the SQL code?
    >
    >Also would you use Access in a five user environment. I don't know the size
    >of the database, it is probably under 100k, but I know that about 2000

    records
    >are added per month and there are probably 30,000 records now. ?
    >
    >Thanks
    >
    >Nate



  14. #29
    Joe Porschart Guest

    Re: Access to SQL server


    All you have to do is replace the tables locally with links to the tables
    on the SQL Server. Make sure you name the links the same as the local tables
    so you don't have to change the code. You might want to check into pass through
    queries for performance.

    Access works fine with multiple users. The problem with Access, last I
    checked, is that it has a limit on how large it can get. Once it has reached
    that max it stops working. Also, you will notice it grows in size every time
    you use the application which requires constant needs to compact the database.
    I don't recommend using Access for anything more than a tool for development.

    Joe

    "Nate" <nczimm@msn.com> wrote:
    >
    >Can an application which is build is MS Access run against a SQL server

    database
    >without changing the SQL code?
    >
    >Also would you use Access in a five user environment. I don't know the size
    >of the database, it is probably under 100k, but I know that about 2000

    records
    >are added per month and there are probably 30,000 records now. ?
    >
    >Thanks
    >
    >Nate



  15. #30
    Robert Dennis Guest

    Re: Access to SQL server


    You can run MS Access against SQL Server without changing your SQL queries.
    To run MSSQL queries in the MSSQL syntax use Pass Through queries. Also autonumbers
    do not work the same, in MSSQL an "autonumber" field is assigned a value
    by an index after the record is actually inserted.
    Assuming that you have liked tables where everyone uses the same "back end"
    database and has a copy of the "front end" on each machine you can easily
    support 5 simultaneous users. I have found a little performance degredation
    after about 12-15 users. One of the things that will help performance greatly
    is to create forms that dymamically change the source to pull only the record
    that you are working with.

    Robert

    "Nate" <nczimm@msn.com> wrote:
    >
    >Can an application which is build is MS Access run against a SQL server

    database
    >without changing the SQL code?
    >
    >Also would you use Access in a five user environment. I don't know the size
    >of the database, it is probably under 100k, but I know that about 2000

    records
    >are added per month and there are probably 30,000 records now. ?
    >
    >Thanks
    >
    >Nate



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center