ADO Connection problem when not logged in as an Admin

[aedwards via AccessMonster.com]

Hi,

Any idea why the following code would work when logged in as a user with
Admin priviledges, but not work when logged in as a user with only normal
priviledges?

This is from a VBA module in an Access 2003 file. The underlying database is
SQL Server 2005 Express. The tables are linked by ODBC using a File DSN I
setup and saved. The ODBC link works always, Admin user or not. It's only
this VBA code with the ADO connection that breaks when a user is not an Admin.


Dim cnxn As New ADODB.Connection
Dim rsProject As New ADODB.Recordset
cnxn.Open "DRIVER={SQL SERVER}; SERVER=DataServer\SQLEXPRESS;
DATABASE=MyDatabase; USER=MyUser; PASSWORD=MyPassword;"

Thanks.

 

[Sylvain Lafontaine]

It's because you have made some errors in your connection string: USER and
PASSWORD are not valid parameters for the old ODBC SQL Server provider; so I
suppose that ODBC is reverting back to using Integrated Security for
everyone.

Take a look at: http://www.connectionstrings.com/?carrier=sqlserver

In the case of SQL-Server 2005, you can also use the new native provider:
http://www.connectionstrings.com/?carrier=sqlserver2005

Finally, I don't understand why you want to still use an ODBC provider when
using ADO objects. You should use OLEDB instead for a better performance
and using ODBC should be reserved to DAO.

 

[aedwards via AccessMonster.com]

Hey thanks for your help. I didn't know there was a difference between ODBC
ADO and OLEDB ADO. I thought there was just ADO (which by default used OLEDB)
.. I changed the connection string and it's working fine.

It doesn't explain (at least to me) why it was working for Administrators and
not for non-admins, but what the hey, it works, so I'm not complaining.

Thanks again

Aaron

It's because you have made some errors in your connection string: USER and
PASSWORD are not valid parameters for the old ODBC SQL Server provider; so I
suppose that ODBC is reverting back to using Integrated Security for
everyone.

Take a look at: http://www.connectionstrings.com/?carrier=sqlserver

In the case of SQL-Server 2005, you can also use the new native provider:
http://www.connectionstrings.com/?carrier=sqlserver2005

Finally, I don't understand why you want to still use an ODBC provider when
using ADO objects. You should use OLEDB instead for a better performance
and using ODBC should be reserved to DAO.

Hi,

[quoted text clipped - 16 lines]

Thanks.

 

[Sylvain Lafontaine]

I never said that there was a difference between ODBC ADO and OLEDB ADO. In
fact, I don't understand what you mean by that.

ODBC is for DAO and OLEDB is for ADO. ADO doesn't understand ODBC drivers;
however, there is a special OLEDB driver (MSDASQL or Microsoft's OLEDB
Provider for ODBC) that can make the connection between ADO and an ODBC
driver but using this special driver (automatically called by ADO when using
an ODBC connection string) add another level of overhead.

If you use OLEDB than use an OLEDB provider in order to have the best
performance and if you are using SQL-Server 2005 than use its native driver
for the same reason.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Hey thanks for your help. I didn't know there was a difference between
ODBC
ADO and OLEDB ADO. I thought there was just ADO (which by default used
OLEDB)
I changed the connection string and it's working fine.

It doesn't explain (at least to me) why it was working for Administrators
and
not for non-admins, but what the hey, it works, so I'm not complaining.

Thanks again

Aaron

It's because you have made some errors in your connection string: USER and
PASSWORD are not valid parameters for the old ODBC SQL Server provider; so
I
suppose that ODBC is reverting back to using Integrated Security for
everyone.

Take a look at: http://www.connectionstrings.com/?carrier=sqlserver

In the case of SQL-Server 2005, you can also use the new native provider:
http://www.connectionstrings.com/?carrier=sqlserver2005

Finally, I don't understand why you want to still use an ODBC provider
when
using ADO objects. You should use OLEDB instead for a better performance
and using ODBC should be reserved to DAO.

Hi,

[quoted text clipped - 16 lines]

Thanks.