Odbc connections and ADP?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DaveK - 12 Jul 2005 13:37 GMT

I have a working .ADP project that uses a large demographic information table
on an SQL2K server. I also need a link to an Oracle database so I can get
the billing details for that demographic information. Is this possible? If
so, could someone point me to some examples? I already have ODBC definitions
set up on the PC, but am stumped as to how to get the links to show on the
ADP project. It seems to work differently from a normal Access database. I
can access the Oracle data by using .mdb databases, but the query against a 2
million row demographic table takes too long. The SQL2K server queries are
great, less than 2 seconds vs. 20 minutes when the data are in a .mdb table.
Now I need to hook up the Oracle part.

Reply to this Message

Norman Yuan - 12 Jul 2005 15:14 GMT

ADP is specifically designed for SQL Server/MSDE. It
"CurrentProject.Connection" object can only be connected to SQL Server. When
you start a ADP project and click menu "File->Connection...", the "Data Link
Properties" dialogbox only allows you to connect to a SQL Server/MSDE.

With that said, you can still access data in data stores of other type from
inside of your ADP, using ADO code, just like you do database programming in
VB.

> I have a working .ADP project that uses a large demographic information table
> on an SQL2K server. I also need a link to an Oracle database so I can get
[quoted text clipped - 6 lines]
> great, less than 2 seconds vs. 20 minutes when the data are in a .mdb table.
> Now I need to hook up the Oracle part.

Reply to this Message

DaveK - 12 Jul 2005 15:40 GMT

I have not done any DB programming in VB so that complicates things. Could I
include the code in a stored procedure instead? Could you point me to any
examples?

Thank you.

Reply to this Message

Sylvain Lafontaine - 12 Jul 2005 16:54 GMT

If you don't want to use ADO, then there is the possibility of setting up
the connection to the Oracle Server directly on the SQL-Server and perform
your queries to Oracle from stored procedures.

See
http://www.microsoft.com/sql/techinfo/tips/administration/addingoracle.asp
for more information.

Signature

Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC

>I have not done any DB programming in VB so that complicates things. Could
>I
> include the code in a stored procedure instead? Could you point me to any
> examples?
>
> Thank you.

Reply to this Message

aaron.kempf@gmail.com - 12 Jul 2005 17:36 GMT

you can right-click LINK in an ADP and LINK to ODBC tables

ADP rocks keep up the good work

Reply to this Message

DaveK - 12 Jul 2005 17:59 GMT

I only see Open and Import. Am using Access 2000 SP3.

From what I see, I can only import a table from Oracle, not connect through
a link, at least in my corporate version. Due to the table sizes, importing
is not an option. Otherwise, I do very much like it.

Reply to this Message

Sylvain Lafontaine - 12 Jul 2005 18:23 GMT

You may a permission problem: these linkings are done on the SQL-Server; so
you must be a member of either the sysadmin or the setupadmin roles.

Other restrictions from Oracle may apply, too.

Signature

Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC

>I only see Open and Import. Am using Access 2000 SP3.
>
[quoted text clipped - 3 lines]
> importing
> is not an option. Otherwise, I do very much like it.

Reply to this Message

DaveK - 12 Jul 2005 19:42 GMT

Security is no problem, I am the DBA here so am dbo on everything. It is
more of an experience thing. Are you saying I need to set up the connection
within a stored proc, rather than have it show up as a visual icon on the
table screen within adp? If so, I sure could use an example, as I have not
done that sort of connection.

Am I getting this confused with linked servers? If so, how would the adp
project know about the tables from that Oracle link?

Sorry for all the questions.

Reply to this Message

Sylvain Lafontaine - 12 Jul 2005 20:13 GMT

I never used a linked table from ADP, so I cannot tell you what appareance
it will take. However, when I right-click on the database window inside the
Access 2003 Project, I clearly see the "Link tables..." option; so I don't
know why you don't see it. I also see in the File --> Get External Data
menu.

However, even if this option in the Access 2003 ADP File menu or context
menu, the linked table process will be done on the SQL-Server, with the same
result as if you had make a call to the sp_addlinkedserver procedure on the
SQL-Server. This process will not take place on the ADP file. Furthermore,
from ADP, you cannot make a connection to the Oracle server and use it
locally in your VBA code because ADP cannot connect directly to an Oracle
server (or to any other ODBC link); only SQL-Server can.

Your ODBC or OLEDB informations must also be set up on the SQL-Server
machine, not on your local machine.

See
http://www.microsoft.com/sql/techinfo/tips/administration/addingoracle.asp
for more information on setting a linked Oracle server on SQL-Server.

Signature

Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC

> Security is no problem, I am the DBA here so am dbo on everything. It is
> more of an experience thing. Are you saying I need to set up the
[quoted text clipped - 8 lines]
>
> Sorry for all the questions.

Reply to this Message

aaron.kempf@gmail.com - 12 Jul 2005 23:26 GMT

yeah Office 2000 doesn't have the LINK functionality in ADP.
I _REALLLLLLLLLLLLLLLLLLLLLLLLLLLLLYYYYYYYYYYYYYYYYYYYYYY_ friggin
reccomend going to Access 2002 or 2003.. I mean.. Sproc design in
2002/2003 is the most wonderful thing in the world.

So go with Sylvains last reccomendation and setup a linked server
between SQL Server and Oracle.

-Aaron

Reply to this Message