Link SQL Server to MS Access frontend

Hi All,
I have recently migrated from Access to SQL Server. I have come across a strange problem. I have got a table in my database whihc is not linked to any other table, but does include data and is used from time to time for reports. It is more used as a sort of lookup table. Now I had the frontend in Access and I have retained it. So I connect all the SQL Server tables via ODBC to Access. The problem with this table is for some reason it wont allow to add new records on Access side. You can just view the contents, but cannot add anything to it e.g a new record, etc. I tried giving explicit permissions of Select, Insert, update, etc. but still no result. There are other look up tables as well, that have the same problem. Can anybody tell me why this is so and is there any way to fix it?

Regards:
PrathmeshHi All,
I have recently migrated from Access to SQL Server. I have come across a strange problem. I have got a table in my database whihc is not linked to any other table, but does include data and is used from time to time for reports. It is more used as a sort of lookup table. Now I had the frontend in Access and I have retained it. So I connect all the SQL Server tables via ODBC to Access. The problem with this table is for some reason it wont allow to add new records on Access side. You can just view the contents, but cannot add anything to it e.g a new record, etc. I tried giving explicit permissions of Select, Insert, update, etc. but still no result. There are other look up tables as well, that have the same problem. Can anybody tell me why this is so and is there any way to fix it?

Regards:
Prathmesh

Most likely the issue is that there is no primary key that Access can identify. By default, access will open an ODBC record set in update mode (you can add, delete, or modify the records that you see). But if access can not identify a primary key (or if none exists), then access will open the recordset in read only mode.

As a side note, sometimes even if you have a primary key, Access will "guess" the wrong primary key. In that case all the records will appear as "#DELETED" in the recordset view.

You may want to consider an Access Data Project (ADP) which uses ADO (not ODBC) to connect to a SQL back end. Access is a wonderful tool and a fine way to generate reports, but it does have some limitations.

Regards,

hmscott|||Just a couple of things you might want to look at:

1. When you linked the tables from SQL Server into the MSAccess application did you check the Save Password box?
2. Is the login you're using or does your loginID from LDAP show in the Security Manager in SQL Server and do you also have it in the database for Users with permissions to the Public role? (it usually defaults to this)
3. Sometimes I found that I had to go into the Roles (Public) and check (green check mark) all the user created tables (checking ALL columns in that row - including the RFI column.)
4. When you linked the SQL Server tables, were you prompted to identify a primary key in the linking table? (this goes back to hmscott's email)
5. Are all the DSN's the exact same name for anyone accessing the tables. If you have a different DSN Name for other users, you will have some problems.
6. How is your security set up in SQL Server (Mixed Authentication?)
7. You can also try creating a TimeStamp field on the tables but I doubt this would be an issue as it's usually a problem with having Memo (Text) fields in the table.
8. When you created the DSN for ODBC, did you create this as a System DSN so any user's who logs in to your machine would have permissions? Or are you using a User or File created DSN?

In regards to re-writing this as an ADP, I actually like the method of linking in the tables or writing the code with a "cnn" connection. Our forms are all unbound in the MSAccess application and utilize Write/Update/Delete functions. When a customer is retrieved from the search menu, the SQL Statement fires (which spikes the server a little), returns the values to the unbound form, and then closes the SQL Connection. If something is updated, a checkbox (called: DataUpdated on the form) is marked as true to indicate that the Update function needs to be called (or you could have a specific "Save" button on the form.) This method has worked great for us to make updates to the SQL Server tables without having to worry about someone having the MSAccess ADP or MDE file open and on a specific record (where they left it and went to lunch.) This makes it more difficult for SQL Server to update the new/updated field names in the table and sometimes ends up hanging up or takes an extremely long time and we have to find the user's in the database and have them close out so SQL Server can complete the update. Thus I've become a fan of writing unbound forms which has worked almost flawlessly (and very fast) for us even with external users connecting via Citrix or Remote Desktop Connection. (my thoughts on this is that it's all on how you write your code and also making sure your hardware is sufficient). Some people say MSAccess isn't an enterprise solution but if the code's written correctly, I believe you can make it act like an enterprise solution.

I'm attaching a program which automatically updates/creates DSN's for users on their machine from SQL Server (assuming that the user's login has permissions to do this.) You will need to delete the current dbo_XXXX linked tables in the attached Mdb and add in 1 linked table to the database you want a DSN created for. This makes it easy to create DSN's for other users, making sure the DSN name is the same as all you need to do is open the MDB and the DSN is created. It's not a fancy application but it does do the job.

I hope any of this helps.|||Hi hmscott and pkstormy,
hmscott you were spot on. The lookup table did not have any primary key. Yesterday late night i was trying to get this running and by chance hit upon this solution and in the morning saw your reply. Tks for the "deleted" reference, it is a good thing to keep in mind.

pkstormy, tks for all the ideas. I will record them and use them for future troubleshooting. As for the database, tks a lot for that too. I will definately go through it and try to incorporate those suggestions.

Initially the frontend-backend was Access but the functionality in my database outgrew Access so we decided to move to SQL Server. The emphasis now is move all the 4-5 systems that we have in the organization to SQL Server. Once that is done, then upgrading the frontend, based on all the suggestions, is on the agenda. Thanks for the help guys. Much appreciated.

Cheers.
Prathmesh

Limts of SQL 2005 Enterprise

I know this might seem like a strange question, but we currently have a
database that very few people have heard of TurboPower FlashFiler
(http://sourceforge.net/projects/tpflashfiler/). We have had issues with it
as we approach 100 users. We have installed SQL 2005 Enterprise for the
goal of moving the database from TurboPower FlashFiler to SQL. But now the
developer has introduced that thought of going to NexusDB (www.nexusdb.com)
instead of SQL. The question from managment was asked "How many users can
access SQL?" They want an acutal number. Is there any articles of
performance test that test the upper limits of SQL or anything like a case
study where a company had 10,000 users accessing a database, etc.
Thanks,
MarkHave a look here:
Performance and Scalability: SQL Server 2005 Enterprise Edition
Benchmarks
http://www.microsoft.com/sql/prodinfo/compare/benchmarks.mspx
_________________________________________________________
Vera Noest
MCSE, CCEA, Microsoft MVP - Terminal Server
SQL troubleshooting: http://sql.veranoest.net
___ please respond in newsgroup, NOT by private email ___
"M.Siler" <John.Doe@.NoSpam.com> wrote on 23 aug 2007 in
microsoft.public.sqlserver.server:
> I know this might seem like a strange question, but we currently
> have a database that very few people have heard of TurboPower
> FlashFiler (http://sourceforge.net/projects/tpflashfiler/). We
> have had issues with it as we approach 100 users. We have
> installed SQL 2005 Enterprise for the goal of moving the
> database from TurboPower FlashFiler to SQL. But now the
> developer has introduced that thought of going to NexusDB
> (www.nexusdb.com) instead of SQL. The question from managment
> was asked "How many users can access SQL?" They want an acutal
> number. Is there any articles of performance test that test
> the upper limits of SQL or anything like a case study where a
> company had 10,000 users accessing a database, etc.
> Thanks,
> Mark|||"M.Siler" <John.Doe@.NoSpam.com> wrote in message
news:eUvz8cc5HHA.5424@.TK2MSFTNGP02.phx.gbl...
>I know this might seem like a strange question, but we currently have a
>database that very few people have heard of TurboPower FlashFiler
>(http://sourceforge.net/projects/tpflashfiler/). We have had issues with it
>as we approach 100 users. We have installed SQL 2005 Enterprise for the
>goal of moving the database from TurboPower FlashFiler to SQL. But now the
>developer has introduced that thought of going to NexusDB (www.nexusdb.com)
>instead of SQL. The question from managment was asked "How many users can
>access SQL?" They want an acutal number. Is there any articles of
>performance test that test the upper limits of SQL or anything like a case
>study where a company had 10,000 users accessing a database, etc.
> Thanks,
> Mark
>
Tell management that there is no fixed limit on the number of users. There
is plenty of evidence for SQL Server supporting thousands of users and multi
terabytes of data. See:
http://www.microsoft.com/SQL/BIGDATA/default.mspx
http://www.tpc.org/
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On Thu, 23 Aug 2007 16:50:26 -0400, "M.Siler" <John.Doe@.NoSpam.com>
wrote:
>I know this might seem like a strange question, but we currently have a
>database that very few people have heard of TurboPower FlashFiler
>(http://sourceforge.net/projects/tpflashfiler/). We have had issues with it
>as we approach 100 users. We have installed SQL 2005 Enterprise for the
>goal of moving the database from TurboPower FlashFiler to SQL. But now the
>developer has introduced that thought of going to NexusDB (www.nexusdb.com)
>instead of SQL. The question from managment was asked "How many users can
>access SQL?" They want an acutal number. Is there any articles of
>performance test that test the upper limits of SQL or anything like a case
>study where a company had 10,000 users accessing a database, etc.
Even a modest server should support 100+ rich clients if the software
and data model are halfway decent. OTOH, a bad app and a bad database
can bog with one.
On the third hand, via a middle tier, you can support thousands of
sessions at the same time, I've been there in many shops.
Nexusdb? Doesn't sound like it's even meant to be a server.
Josh|||> Nexusdb? Doesn't sound like it's even meant to be a server.
Sorry just to clarify that :)
Our biggest site that we support *ourselves* is an Australian based a debt
collection agency. They have established their own offshore call center and
developed their own software on the basis of NexusDB. At the moment this
software runs with approximately 200 concurrent users spread over Australia,
India and the UK with the central database server located in
Brisbane/Australia and is thus online 24x7. If you need we can arrange for
them to provide reference/testimonial for NexusDB.
NexusDB was designed for small to medium sized environments. It clearly has
its limits in scalability and SQL Server surely has many benefits that
NexusDB doesn't have, still we think it has several advantages over MS-SQL:
* cost: the normal NexusDB Server is royalty free and comes as
developer licenses. So after the initial costs there is no further
cost, not for you and not for your clients/customers no matter how
many applications you ship to how many clients/users you want.
* support: we are a small company that knows very well that we
depend on our customers being happy. In the case of problems you're
can talk directly to the developers of the system instead of
support reps that have less knowledge than yourself and just work
off their flow diagram. If you need it we optionally provide service
level agreements with guaranteed response times
* footprint: NexusDB server is a 4.5 mb executable including SQL
engine and that's all you need.
* resources: NexusDB server runs very well on any hardware running
WinNT4 starting from 256MB memory.
* server installation: NexusDB server is very easy to install. Copy
the exceutable and a premade ini-config and start
* zero admin: NexusDB server doesn't need a database administrator as
everything is readily available in the clear and simple user interface
* best of both worlds: table/cursor based and SQL access in one.
NexusDB allows to mix and match and thus use the best approach for
every single one of your needs.
* no runtime libraries: a NexusDB client doesn't need any additional
runtime files or database client installation. All you need is the
executable
* SQL2003 compliance: NexusDB sticks as close possible to the SQL2003
standard instead of using its own proprietary language. This makes
integration of other databases and tools much easier
* another big point in above case is that converting from a table based
flashfiler based application is very easy and should be doable in
just a few days or less (including testing). You will almost
certainly see a big improvement in speed and stability and possibly
save you from developing a completely new system right now
* full source: YES, you get the full source code of server and client
with your license. down to the last line.
There are a lot more advantages from a purely technical point of view
covering things like full extensibility, customizable data compression &
encryption and great flexibility. get more info on www.nexusdb.com if you
like.