Results 1 to 4 of 4
  1. #1
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    SQL Projects V ODBC (Access 2000)

    We have a SQL 2005 database with approx 100 tables inc assorted summary tables that are built overnight to speed up interroigation.
    We need to build a secure front end that will be used on a Citrix server and allow remote Access.
    (At best 20 or 30 of these will be needed for the remote Access, at least initially)
    Rather than use web forms it has been decided (not my decision) to do this using an Access front end.
    When users log in they will need to see just data specific to them.
    They cannot have the front end on their remote machines.
    I need to decide on the best strategy for this.
    Do we use an Access Project and put all the work load onto the server (this means having all the queries on the server)
    Or use an ODBC table linked front end.
    Populating temporary tables with users data seems out because there is ONLY the one copy and they would become very data laden., although
    at least we could have local data. An alternative is to use Procs and Queries with views.
    I am developing both options at the moment to look at development time and also speed.
    However I am open to experienced and educated suggestions
    Andrew

  2. #2
    Lounger
    Join Date
    Nov 2006
    Location
    Denver, Colorado, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Projects V ODBC (Access 2000)

    First off, you sound frustrated that you have to use Access but don't dispare. Access is an excelent product for your purposes.

    Due to problems I had with Access 2000 projects combined with the fact that an Access application can connect to multiple data sources, I would recommend using an Access Application, ODBC and DAO.

    The most simplistic way to handle logging in is to create a start-up form that pulls data from one of the SQL Server tables. I use a version table because my applications check the front-end version against the back-end version before letting the user do anything. If the versions don't match the user is warned and the application closes. If the versions match the version form is used as a splash form while other initializations are performed. If you are using SQL Server security, and not integrated security, the SQL Server security form will open and prompt the user for a user name and password.

    You can control user access to the tables from within SQL Server. Access will not allow the user any access that is not permitted by SQL Server. By default, if you attempt to do something that is not allowed, Access will present an error message. If you would like to avoid Access error messages you can create some Passthrough Queries that call the functions within SQL Server to return user access priveleges. If the user doen't have access to do something you can cut them off before any forms or reports are opened. The databases I create have a Passthrough query named qryAction. Whenever I need to call a SQL Server stored procedure, funciton or even return a recordset from a parameterized stored procedure I use DAO code to reset the SQL string property and the ReturnsRecords properties (in that order) in qryAction and then open qryAction.

    For simple data entry and report writing linked tables work real well. However, you will eventually run into a situation where you are trying to run a very complex query and Access will appear to hang. In that case, change the Access query into a Stored Procedure or a View and have Access open the Stored Procedure or View as a Passthrough Query. You can create the Passthrough Query as a persisted query object in Access and handle the WHERE clause in Access or you can create the action query that I described above and pass parameters to a parameterized query or function in SQL Server.

    One thing I would definately avoid is using an Access front-end with all unbound forms and populating those forms with ADO code. The VB code is long and confusing and slow, with an emphisis on slow. I performed a test on a database that was written with unbound forms verses changing it to use linked tables and bound forms. The form displayed about 100 fields in several tabs and was written to display only one record at a time by setting the record source (or query in the VB code) at the time the form opened. This form was a detail form that opened based on the user's selection in a find form. I don't remember the exact times now but there was a definite advantage to using the linked tables and allowing Access to handle most of the work.

    And one last thing. Make sure the SQL Server tables all have some sort of unique ID that Access can see. The combination of Access and SQL Server seems to be more forgiving than with other database back-ends but you will run into issues where all of your records in linked tables display #Deleted in every field if you don't have an unique ID.

    Good luck on your project,

    Patrick.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL Projects V ODBC (Access 2000)

    We have clients running this setup of our last generation software and it works for them. Creating a project would have meant a different set of code to maintain, so we used ODBC links. Each user will get a copy of the front end when they log into Citrix. It isn't quite the same as multiple users logging into the same front end. You will have to figure out how to copy the workgroup file to each user's Citrix folder as well, assuming you are using Access security on the front end.
    Charlotte

  4. #4
    Lounger
    Join Date
    Nov 2006
    Location
    Denver, Colorado, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Projects V ODBC (Access 2000)

    I have successfully allowed users to share a single mdw file. I have also written a couple update scripts. One of them allows a single user to update their own front-end file from a master location and the other one allows me to update every Citrix user after I put an updated front-end on the server. If you are interested in these scripts, let me know and I'll post them as attachments.

    Patrick

Posting Permissions

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