Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Posts
    4

    Unanswered: Using Access As Fontend To Sql Server... Help!

    I'm trying to user Microsoft Access as a frontend to a SQL Server database. I'm having an issue. It is telling me I'm read only.

    The problem is we are trying to figure out if it is a good solution to go to SQL. I have about 70-100 users that access this database and there is about 10 to 20 people accessing it at the same time. Is this a viable solution or should I be looking at another way?

    I'm very limited on what is approved. I can write a VB fontend, but it will not be approved so I have to stick with Access if I can, but the size of the table is getting to be too big. Any suggestions.

  2. #2
    Join Date
    Feb 2004
    Posts
    142
    Access works with SQL Server quite well for most apps. I don't know how you have SQL configured related to permissions so you need to check them on the server.

    A key thing to remember is for Access to be able to Insert or Update records into SQL Server (or other back end) is there must be a unique index that IDs each row.

    The quickest way to tell is when you lnk the table if Access prompts you for a unique index. It will usually see the index if there is one but you can specify it while linking if it does not see it.

    Be real careful about the primary key fields, in some versions of SQL it can be a bit of a pain to change this field's value if RI is maintainted by SQL and you have dependent (child) tables.

    I've used Access/SQL server in environments that either cost or other issues prevent the use of VB etc. I have done both bound and unbound designs with passthrough queries with good results.

    Hope this helps.
    KC

  3. #3
    Join Date
    Feb 2004
    Posts
    4

    THANKS!

    THANKS! That's where I'll start. That is exactly what is happening. Do you know if there are any limits on the connections. I do a lot of connecting in VBA code in the background. Could I be looking at a problem there?


    Originally posted by AZ KC
    Access works with SQL Server quite well for most apps. I don't know how you have SQL configured related to permissions so you need to check them on the server.

    A key thing to remember is for Access to be able to Insert or Update records into SQL Server (or other back end) is there must be a unique index that IDs each row.

    The quickest way to tell is when you lnk the table if Access prompts you for a unique index. It will usually see the index if there is one but you can specify it while linking if it does not see it.

    Be real careful about the primary key fields, in some versions of SQL it can be a bit of a pain to change this field's value if RI is maintainted by SQL and you have dependent (child) tables.

    I've used Access/SQL server in environments that either cost or other issues prevent the use of VB etc. I have done both bound and unbound designs with passthrough queries with good results.

    Hope this helps.

  4. #4
    Join Date
    Feb 2004
    Location
    Washington
    Posts
    49
    I'm using Access as a front end to SQL Server. I think you are receiving the message because Access is not intended to be used in a multi-user environment. In order to eliminate the message, try coping the .adp file to each workstation. I personally do not like this. I'm considering writing a VB front end (but like you, I don't think I can get an approval). There is a topic about this issue on the Microsoft's Tech Net.
    Cathy

  5. #5
    Join Date
    Feb 2004
    Posts
    142

    Re: THANKS!

    Originally posted by jraines
    THANKS! That's where I'll start. That is exactly what is happening. Do you know if there are any limits on the connections. I do a lot of connecting in VBA code in the background. Could I be looking at a problem there?
    You can configure the number of connections on the server, however connections cost money and Access can be a connection hog. It takes at least two for starters. MS charges by the connection and not by seat now.

    This is why I have moved to unbound designs with no linked tables utilizing passthrough queries - I can get 200 - 300 users through 60 connections. It is harder to build but it is cost effective in the long run.
    KC

  6. #6
    Join Date
    Feb 2004
    Location
    Washington
    Posts
    49
    No, this is an actual ACCESS limitation, not a SQL. According to Microsoft, this is by design. It happens when more than one user runs the applicataion. Microsoft suggest copying the application to each workstation. This is a pain. I would also like to know if someone has gotten around it. Evertyhing I've come across says "That's just the way it is."
    Cathy

Posting Permissions

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