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
>
>