permission issue connect sql server table from MS Access

[jamest85]

Hi:

I have migrated MS Access table from .mdb to MSsql server, from my computer and my boss' pc can run the new Access application without any problem, but another user just can 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 "textSQLDB" failed.

I am worry about my connection string is not right:

The connection string in before: "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 boss pc can't run also.

So, it must be a permission issue. the sql database is in a shared folder, (the previsou .mdb also in that location, and everybody can run that .mdb before).

How to set the permission, i need a very detailed instruction. (a useful link will be also great).

Thanks in advance.

Jt

[sierra7]

Just a thought . . has your other user got an ODBC driver set up in ODBC Manager on her pc?

S7

[jamest85]

Just a thought . . has your other user got an ODBC driver set up in ODBC Manager on her pc?

S7

Thanks for your post, sierra7.

How to find that the user has or hasn't installed the ODBC driver?

Thank you very much.

Jt

[sierra7]

Thanks for your post, sierra7.

How to find that the user has or hasn't installed the ODBC driver?

Thank you very much.

Jt

There is probably a clever way but I would have to sit at her desk and run the ODBC Data Source Administrators program (Start > Control Panel > Administrative Tools > Data Sources (ODBC)) then inspect the various tabs.

Obviously, check your own pc first so you know what you are looking for !

I believe all the drivers are dll's that live in the \Windows\System32 folder so if you've got one that she hasn't, you'll have to copy it over to her computer
(find it's filename by clicking the 'Drivers' tab and browse, then open Windows explorer to copy/paste etc)
and maybe register it
(Start > Run > type "regsvr32 filename.dll ")

S7

[jamest85]

There is probably a clever way but I would have to sit at her desk and run the ODBC Data Source Administrators program (Start > Control Panel > Administrative Tools > Data Sources (ODBC)) then inspect the various tabs.

Obviously, check your own pc first so you know what you are looking for !

I believe all the drivers are dll's that live in the \Windows\System32 folder so if you've got one that she hasn't, you'll have to copy it over to her computer
(find it's filename by clicking the 'Drivers' tab and browse, then open Windows explorer to copy/paste etc)
and maybe register it
(Start > Run > type "regsvr32 filename.dll ")

S7

Thank you very much for your help,sierra7.

I have checked my pc (at: ODBC Data Source Administrator), from the System DSN tab only has one list:
Name: Vision (which is the name I have connected to the sql db).
Driver: SQL Native Client (which is the only item from the Drivers tab, I am not sure if I selected that before, or somebody else).
I can go through all the way, and test the connection to the sql database successfully.

That means even in my pc, I don't have dirvers available from the Drviers list. However, I can find "sqlsrv32.dll" from C:\WINDOWS\system32. And I will assume that using SQL Native Client driver is fine.

I want to add extra dll to the Drivers list, how can I do it? From the "Drivers" tab, at the bottom of the window says:"To install new drivers, use the drver's setup program". I have no idea where to start. (why the "sqlsrv32.dll" from C:\WINDOWS\system32 doesn't show the Drivers list?)


I am sorry I have so many newbie problems, but it is really new for me.

Thanks.

jt

[sierra7]

Hi again
I am not an expert in this area and at the end of the day you may well be right that you have a permissions problem. Your connection string is obviously OK for you and your boss, so Access is not your problem.

You obviously have a data connector called 'Vision'. Does you colleague have this set-up in ODBC Administrator? If she does then the problem lay elsewhere.

At this point I become unclear of what you meant in your last post.You did not say what the name of the driver dll for Vision was; I'll assume it was "sqlsrv32.dll"

Also, I would not expect all of the drivers that appear on the 'Drivers' tab to be setup with System DNS names, only those you wanted for the names that you create e.g 'Vision'.

Are you implying that you want to set-up a 'Vision' DNS on yor colleagues pc but can not find "sqlsrv32.dll" on her list? This is a Microsoft driver and should have been installed by default with Windows. If "sqlsrv32.dll" can be found in her \Windows\System32 folder but is not appearing on the list then I would try re-registering it (as in my post #4)
(Start > Run > type "regsvr32 sqlsrv32.dll" or "regsvr32 c:\Windows\System32 sqlsrv32.dll")

I did a quick Google on "add odbc mysql" and came accross this article that might help.

http://www.nusphere.com/products/lib...mysql_odbc.htm

You may also want to address your problem to the MySQL forum because I don't think you have an Access problem

Best of luck

S7

[NeoPa]

I'm going to move this thread to the SQL Server / MSSQL forum as it seems that is what this question is about. You will still get the updates if anyone posts, but it becomes visible to members who have more experience in that area.