Access to SQL server


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Page 1 of 2 12 LastLast
Results 1 to 15 of 30

Thread: Access to SQL server

  1. #1
    Nate Guest

    Access to SQL server


    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. #2
    Ron 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,
    1. It would depend upon the exact nature of the individual SQL statement

    in Access some will, some will not. Neither T-SQL or Access SQL are standard
    compliant. Functions in Access such as 'Trim' do not exist in T-SQL where
    you must use 'RTrim' and / or 'LTrim'. To test the length of a returned
    data value (within the SQL statement) T-SQL uses 'DataLength' while you
    can use 'Len' in Access.
    2. Access in a 5 user environment should pose no problem if you are
    careful as to what locking (optimistic or pessimistic) you are employing,
    and when invoked.
    3. Max size of an Access 97 database is one (1) gig to you can do
    some calculations and see wether your back is against the wall or not.

    Hope this helps


  3. #3
    David Voo 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


    Hi Nate,

    It all depends on how you coded the applcation in Access earlier since we
    already know that software application programming can be a tedious thing
    if you have not do it with OOP concept, the only possible way to reduce the
    efforts to change to SQL Server based down to minimum if you create your
    Access application in OOP way.

    Anyhow, there are some changes n makeup you have to workout especially some
    database connection, if you used ADODB in Access then you will save tons
    of works when come to SQL Server.

    Good luck!

  4. #4
    Rick Dobson Guest

    Re: Access to SQL server


    Hi Nate,

    You can definitely run Access against a SQL Server database without changing
    the SQL code. The trick is to use Access projects and the right version
    of Access for the SQL Server version you are using.

    Access projects let an Access application work with SQL Server tables, views,
    stored procedures, and user-defined functions. In addition, with SQL-DMO,
    you can programmatically administer SQL Server. You can achieve much of
    the same administrative functionality by running T-SQL statements from ADO
    Connection objects. Another option is to run SQL Server stored procedures
    with parameters and ADO Command objects for faster performance and more flexibility.

    When working with SQL Server 7, Access 2000 is sufficient. While you can
    get patches to make Access 2000 work with SQL Server 2000, Access XP delivers
    more native SQL Server 2000. For example, Access XP readily permits working
    with user-defined functions from its UI, but Access 2000 does not know about
    user-defined functions in its UI.

    As far as the number of users and the size of the database you have several
    options. The Microsoft Data Engine (MSDE) is free and ships with
    Office 2000. It handles 5 users without any throttling, but Microsoft says
    performance degrades after 5 users. I have anecdotal reports of applications
    achieving acceptable performance with many more users. Office XP ships with
    an updated version of MSDE that reflects SQL Server 2000 features, but is
    still free to Office owners and requires no client licenses for use. You
    can, of course, run Access projects with any version of SQL Server. Performance
    depends on a wide range of factors, including database design, programming
    conventions, and hardware. 30,000 records is not likely to put a strain
    on any version of SQL Server -- even MSDE.

    My site, www.programmingmsaccess.com, offers numerous code samples illustrating
    techniques for programming SQL Server with Microsoft Access. My most recent
    book, Professional SQL Server Development with Access 2000, has hundreds
    of code samples, design strategies, and development tips for those who want
    to build SQL Server solutions with Access. I wouldn't have written the book
    if I didn't think the topic was important for both Access developers who
    want to move up to SQL Server and SQL Server DBAs who want to bring the power
    or RAD development with Access to their SQL Server databases. Happily, I
    have a growing number of fans for the book and the solutions strategies that
    it demonstrates.

    Rick Dobson
    www.programmingmsaccess.com


    "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. #5
    Tom Kelley 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



  6. #6
    tim Curtin 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




    Hello Nate:
    Some of the queries may need to be changed. The Access driver does a pretty
    good job of converting the sql. If the database is 'Mission Critical' and
    could mean the death of the company if it is corrupt, then get the data out
    of Access and into SQL Server. Access mdb's can be irreparable. Access 2k
    can handle 2gb file size and prior versions 1gb. Nothing can be more expensive
    than lost data. Access is nice for small user and 'department' level systems.
    Think SQL.....

  7. #7
    Patrick 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


    Hi,

    Access 2000 has a "project" mode wich is ADO built.
    This means,you have to modify some code from DAO to ADO.

    But then you can have more then 100 users un a database as big as you want
    (currently I work with 300 MB).

    Patrick

  8. #8
    Dev Guest

    Re: Access to SQL server


    "David Voo" <david@download.com.my> wrote:
    >
    >"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

    >
    >Hi Nate,
    >
    >It all depends on how you coded the applcation in Access earlier since we
    >already know that software application programming can be a tedious thing
    >if you have not do it with OOP concept, the only possible way to reduce

    the
    >efforts to change to SQL Server based down to minimum if you create your
    >Access application in OOP way.
    >
    >Anyhow, there are some changes n makeup you have to workout especially some
    >database connection, if you used ADODB in Access then you will save tons
    >of works when come to SQL Server.
    >
    >Good luck!


    Hi
    If u want to use Access SQL statments on SQL server try using link tables
    Dev

  9. #9
    Arthur Hill, CPA MCSD Guest

    Re: Access to SQL server


    I wrote a system for Ford at the Fairlane Training and Development Center
    in Access 2.0 that had fifteen users and 200,000 records with 120 + tables,
    240 + queries and 100 + reports running on 486 servers at best and 386 workstations
    and it ran the place for five years. Access 2000 on modern workstations
    and servers can handle five users in most circumstances without any strain
    if you design you databases correctly with all the correct indexes and other
    optimization features.



  10. #10
    Jmills Guest

    Re: Access to SQL server


    Yes, you can use MS SQL as your database on an Access front end. You need
    to link the SQL tables into Access. I do this by first creating an System
    DSN in the ODBC manager for my SQL Databae, then link the table using that
    ODBC.

    Yes you can execute the Stored Procedures from Acess. Using DAO. Or ADO
    if you are using Access 2000. This is true for the other Office products
    as well.

    If your stored proc uses Temp tables that you wish Access to see, you will
    need to link those as well. You can do this by running some code in the
    query analizer to create the temp table, and keep it open while you link
    your Access table to the temp one. Remember to keep the temp table open
    all you have to do is keep the connection that created the temp table open,
    as long as your code does't drop the table.

    Access works for a five user enviroment. I've found that problems don't
    start arising until the user count gets to 20 or so. MS states that it can
    handle upwards of 250, but I don't recommend trying that. As always, SQL
    is going to handle multiple users better than access. Also, look at how
    Access is used across you network. Each machine that uses it will pull the
    database across the network to be viewed in the workstation's memory. It
    could degrade your network performance. Using SQL will allow you to pull
    smaller chunks of data. The best performance is SQL with a VB or VC front
    end, if you can. I know its not always possible.

    HTH

    -Joseph




    "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. #11
    Danny J. Lesandrini Guest

    Re: Access to SQL server


    Nate:

    That depends.

    Are you linked to SQL Server through ODBC? If so, then ODBC serves
    as a "translator" to modify the Access SQL to be valid for SQL Server.
    For example, dates in Access are limited by the pound sign (#) whereas
    in SQL Server it's the single Quote ('). ODBC makes this substitution
    for you.

    On the other hand, if you are using ADO with the native SQL Server
    provider, then you will need to write your SQL in native SQL Server
    syntax. Another example of that which catches people is the wildcard
    character. In MS Access, you use an asteriks (*), but in SQL Server
    you need to use the percent sign (%). So, when you issue this SQL
    using ADO from MS Access, it will fail:

    SELECT * FROM Authors WHERE au_FName Like 'D*"

    It should read

    SELECT * FROM Authors WHERE au_FName Like 'D%"

    Stick with ODBC and you don't need to learn these differences, but
    your code will be slower because you are invoking the translator. I
    created a SQL parser to reformat my MS Access SQL into a "pretty"
    form and to convert Access syntax to SQL Server. It's not perfect,
    but it's free.

    Go to my download site and download my DataFast Utility:
    http://24.22.189.23/datafast/datafastutility.asp

    Follow the instructions to install it as an Addin. The zip contains
    Access 97 and 2000 versions.

    HTH

    Danny J. Lesandrini
    Downloads: http://datafastdownloads.cjb.net
    Articles: http://www.swynk.com/friends/Lesandrini/


    "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



  12. #12
    Davis Salks Guest

    Re: Access to SQL server


    In many cases, no problem. However, depending on your querie's features, execution
    could get split-up between the client and the server. Worst case is where
    none of the query runs at the server. It will run, but you will have significant
    performance issues.

  13. #13
    Henry K Guest

    Re: Access to SQL server


    Yes, it is possible to modify Access to run against SQL server w/o modifing
    any code. You will need to move your table to SQL server and re-link them
    back into Access. However, in your case you do not need SQL Server, it will
    be a waste of money. Access works fine with 5 users.

    "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. #14
    James Remsey Guest

    Re: Access to SQL server


    I have an application that I built entirely in MS Access. It was a contract
    management application which stored approximamtely 500 active records (records
    changed on a daily basis) and about 2000 plus inactive or closed records.
    There were about 20 people hitting it daily across the network. Because
    of the number of people using it I had to upsize it to SQL Server. What
    I did was Import the database into SQL Server and then in Access I linked
    to the tables. Linking will also include the the owner in the table name
    when linking. I removed the onwer name from the table names and verified
    all the linked tables were named as before and verified all my field names
    were the same. I just double checked! I had a few changes into of my SQL
    Queries when I combined fields in the query but other than that it woked.
    Ther was also a considerable gain in speed, especially since I had over
    extended Access.

    Basically just link to the SQL Database table from Access!

    Jim Remsey
    development@theimageco.com



    "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. #15
    a Guest

    Re: Access to SQL server


    Don't do it.

    A customer of mine had an access system, moved the DB part to SQL7, everything
    was okay. Upgraded to SQL2000 and had constant locking problems.

    I was so bad they eventually had to back everything out and have a rewrite
    in process.

    "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