Connect Access to SQL Server AND Local ODBC Connection

I have a customer that wants me to create a database and use a back end of
SQL Server (hosted on the Internet for his site). This would normally be
easy to create the front end with an ADP and continue life as normal. But,
in order to connect his accounting program to this program that I am
creating, I also need the ADP/MDB to connect to his local ODBC Link
(Business Vision Connect!) This is the software that is installed on the
local machine to speak to the accounting software. I can get Connect! to
work when it isn't in an ADP. I can get SQL Server to work in an ADP. So,
at this point, I can get one or the other to work...
Does anyone know of a way to get SQL Server to work with an MDB without
asking for the SQLServer Password OR a way to get his Connect (Regular ODBC
System DSN) to work in an ADP? I have also tried creating a file DSN (for
Connect!) which looks like it could work with an ADP, but since the
Connect! is rather old, it doesn't seem to like to be put into a FileDSN.
Any ideas would be great!!!
Thanks,
Shawn
You can connect an mdb to SQL Server by creating linked tables or by
using pass-through queries. You supply the connection string in code.
From the MDB, create a DAO TableDef object for the linked tables, and
QueryDef objects for the pass-through queries (which return read-only
result sets). HTH,
Mary
On Wed, 16 Jun 2004 20:27:05 -0400, "Shawn Oatley"
<notmyemail@.address.com> wrote:

>I have a customer that wants me to create a database and use a back end of
>SQL Server (hosted on the Internet for his site). This would normally be
>easy to create the front end with an ADP and continue life as normal. But,
>in order to connect his accounting program to this program that I am
>creating, I also need the ADP/MDB to connect to his local ODBC Link
>(Business Vision Connect!) This is the software that is installed on the
>local machine to speak to the accounting software. I can get Connect! to
>work when it isn't in an ADP. I can get SQL Server to work in an ADP. So,
>at this point, I can get one or the other to work...
>Does anyone know of a way to get SQL Server to work with an MDB without
>asking for the SQLServer Password OR a way to get his Connect (Regular ODBC
>System DSN) to work in an ADP? I have also tried creating a file DSN (for
>Connect!) which looks like it could work with an ADP, but since the
>Connect! is rather old, it doesn't seem to like to be put into a FileDSN.
>Any ideas would be great!!!
>Thanks,
>Shawn
>
|||Shawn,
Find a way to make the ADP work. Linked tables with mdb are a nightmare. Modifying pass through queries on the fly bloats your database and are much harder to maintain than sprocs. Linked tables create excessive connection/locking which leads to DEADLO
CKS. Linked tables should *not be even considered*when the database is accesses through the internet.
Here is a link to a site with numereous connections string examples. http://www.able-consulting.com/ADO_Conn.htm
"Shawn Oatley" wrote:

> I have a customer that wants me to create a database and use a back end of
> SQL Server (hosted on the Internet for his site). This would normally be
> easy to create the front end with an ADP and continue life as normal. But,
> in order to connect his accounting program to this program that I am
> creating, I also need the ADP/MDB to connect to his local ODBC Link
> (Business Vision Connect!) This is the software that is installed on the
> local machine to speak to the accounting software. I can get Connect! to
> work when it isn't in an ADP. I can get SQL Server to work in an ADP. So,
> at this point, I can get one or the other to work...
> Does anyone know of a way to get SQL Server to work with an MDB without
> asking for the SQLServer Password OR a way to get his Connect (Regular ODBC
> System DSN) to work in an ADP? I have also tried creating a file DSN (for
> Connect!) which looks like it could work with an ADP, but since the
> Connect! is rather old, it doesn't seem to like to be put into a FileDSN.
> Any ideas would be great!!!
> Thanks,
> Shawn
>
>

Connect Access to SQL Server AND Local ODBC Connection

I have a customer that wants me to create a database and use a back end of
SQL Server (hosted on the Internet for his site). This would normally be
easy to create the front end with an ADP and continue life as normal. But,
in order to connect his accounting program to this program that I am
creating, I also need the ADP/MDB to connect to his local ODBC Link
(Business Vision Connect!) This is the software that is installed on the
local machine to speak to the accounting software. I can get Connect! to
work when it isn't in an ADP. I can get SQL Server to work in an ADP. So,
at this point, I can get one or the other to work...
Does anyone know of a way to get SQL Server to work with an MDB without
asking for the SQLServer Password OR a way to get his Connect (Regular ODBC
System DSN) to work in an ADP? I have also tried creating a file DSN (for
Connect!) which looks like it could work with an ADP, but since the
Connect! is rather old, it doesn't seem to like to be put into a FileDSN.
Any ideas would be great!!!
Thanks,
ShawnYou can connect an mdb to SQL Server by creating linked tables or by
using pass-through queries. You supply the connection string in code.
From the MDB, create a DAO TableDef object for the linked tables, and
QueryDef objects for the pass-through queries (which return read-only
result sets). HTH,
Mary
On Wed, 16 Jun 2004 20:27:05 -0400, "Shawn Oatley"
<notmyemail@.address.com> wrote:

>I have a customer that wants me to create a database and use a back end of
>SQL Server (hosted on the Internet for his site). This would normally be
>easy to create the front end with an ADP and continue life as normal. But,
>in order to connect his accounting program to this program that I am
>creating, I also need the ADP/MDB to connect to his local ODBC Link
>(Business Vision Connect!) This is the software that is installed on the
>local machine to speak to the accounting software. I can get Connect! to
>work when it isn't in an ADP. I can get SQL Server to work in an ADP. So,
>at this point, I can get one or the other to work...
>Does anyone know of a way to get SQL Server to work with an MDB without
>asking for the SQLServer Password OR a way to get his Connect (Regular ODBC
>System DSN) to work in an ADP? I have also tried creating a file DSN (for
>Connect!) which looks like it could work with an ADP, but since the
>Connect! is rather old, it doesn't seem to like to be put into a FileDSN.
>Any ideas would be great!!!
>Thanks,
>Shawn
>|||Shawn,
Find a way to make the ADP work. Linked tables with mdb are a nightmare. M
odifying pass through queries on the fly bloats your database and are much h
arder to maintain than sprocs. Linked tables create excessive connection/lo
cking which leads to DEADLO
CKS. Linked tables should *not be even considered*when the database is acce
sses through the internet.
Here is a link to a site with numereous connections string examples. .able-consulting.com/ADO_Conn.htm" target="_blank">http://www
.able-consulting.com/ADO_Conn.htm
"Shawn Oatley" wrote:

> I have a customer that wants me to create a database and use a back end of
> SQL Server (hosted on the Internet for his site). This would normally be
> easy to create the front end with an ADP and continue life as normal. But
,
> in order to connect his accounting program to this program that I am
> creating, I also need the ADP/MDB to connect to his local ODBC Link
> (Business Vision Connect!) This is the software that is installed on the
> local machine to speak to the accounting software. I can get Connect! to
> work when it isn't in an ADP. I can get SQL Server to work in an ADP. So,
> at this point, I can get one or the other to work...
> Does anyone know of a way to get SQL Server to work with an MDB without
> asking for the SQLServer Password OR a way to get his Connect (Regular ODB
C
> System DSN) to work in an ADP? I have also tried creating a file DSN (fo
r
> Connect!) which looks like it could work with an ADP, but since the
> Connect! is rather old, it doesn't seem to like to be put into a FileDSN.
> Any ideas would be great!!!
> Thanks,
> Shawn
>
>

Confused About Permission

I read a few articles on best SQL practices and they kept coming back to using a Least Privileged Account. So I did so and gave that account read only permissions. The articles also said to do updates use Stored Procedures - so I created stored procedures for updating/deleting data.

So here's my problem - I connect to the database using the Least Privileged Account, I use the Stored Procedures, but .NET keeps saying I lack permissions. If I GRANT the Least Privileged Account UPDATE/DELETE permission on the table, the Stored Procedures run perfectly. But isn't that EXACTLY what I'm trying to avoid?

My greatest concern is someone hacks my website and using the Least Privileged Account, they delete all my data using that account. So I don't want to give the Least Privileged Account the Update/Delete privileges.

Thanks a MILLION in advance!

Do you grant EXECUTE permission on the stored procedures to the account?

Once that is done you do not need UPDATE/DELETE on the tables for the account.

|||I granted execute permissions to the stored procedures, but it still fails when I check OFF the Update permission on the table (and still works when I check ON the Update permission). Any other ideas?|||

I figured out my problem (thanks to TATWORTH). I was creating the SP's while logging in as the least privileged account, so therefore the SP was [LeastPrivilegedAccount].[StoredProcedureName] - this required the Least privileged user to have the permissions. Now I created the SP's as [dbo].[StoredProcedureName] and granted the Least privileged user EXECUTE permissions.

It works perfectly! Thanks a MILLION!!1