can't connect sql server table from MS Access (1 Viewer)

Hi:

I have migrated MS Access table from .mdb to MSsql server, from my computer and my administrator's pc can run the new Access application without any problem, but another user just can't run this application (before the migration, she runs the .mdb without problem).

error: The Expression After update you entered as the event property setting produced the following error: ODBC-connection to "testSQLDB" failed.

I am worry about my connection string is not right:

The connection string before was: "Provider=Microsoft.Jet.OLEDB.4.0; data source=\\server\shared\Processing\dbase\Processing .mdb"

The connection string after tables moved to sql: "Provider=SQLOLEDB; Data Source=SERVER; Initial Catalog= VisionSQL;Integrated Security=SSPI;"

But if that is wrong, my pc and my administrator's pc can't run also (in fact, I am using the server pc, so I am the owner, administrator he has the role of administrator).

So, it must be a permission issue or missing something, (the previsou .mdb in "Share" location, and everybody can run that .mdb before, the newly installed sql database is in C drive by default).

What will be the most likely problem? Do I need to check if the user's MS Access "Data Source Name" has the database name, or create New Data Source?


Thanks in advance.

Jt

If you use their copy of the database can you access (sorry) the db?

At the moment it sounds like it might be an authentication/permissions issue to the db, Your connection string suggests a trusted connection which means that their Windows/Active Directory credentials will be used to determine the level of access into the Database.

In the first instance, check that her Active Directory account belongs to a group that has permission on that database. For testing purposes you can add her AD account directly to the server and Database, but moving forward, implementing SQL server roles that contain the Active Directory groups and have the relevant permissions on the database Stored procedures, views etc will simplify your administration.

it works, thanks

tehNellie said:

If you use their copy of the database can you access (sorry) the db?

At the moment it sounds like it might be an authentication/permissions issue to the db, Your connection string suggests a trusted connection which means that their Windows/Active Directory credentials will be used to determine the level of access into the Database.

In the first instance, check that her Active Directory account belongs to a group that has permission on that database. For testing purposes you can add her AD account directly to the server and Database, but moving forward, implementing SQL server roles that contain the Active Directory groups and have the relevant permissions on the database Stored procedures, views etc will simplify your administration.

Click to expand...

Hi: TehNellie
Thanks for your post, it works now, the main reason is: I need to set the ODBC setting correct (from each user's pc, in Control panel ->Administrator Tools), but before that I need to go to SQL server to set the permissions or roles for those users.

Cheers.

Jt