MSDE: Linked servers through ODBC

[Guest]

Hi,

I know this isn't exactly Access related, but I'll try anyway.

I have an Access database that is growing quite large and slow. It is used
as a reporting layer between an oracle DB and Excel. I currently have an
installation of SQL Server Express that can be used, but I can't figure out
how to create a link to the Oracle server. It is set up as an ODBC source on
the PC, but can also be accessed directly I suppose. Any pointers? Is this
even possible?

(Yes I RTFM - it wasn't much help

/Sune

 

[Stefan Hoffmann]

hi,

I have an Access database that is growing quite large and slow.

Slow is not necessarily a function of size.

It is used
as a reporting layer between an oracle DB and Excel.

Huh? Of what amount of data per report are we talking? 1 Gigabyteor more?

I currently have an
installation of SQL Server Express that can be used, but I can't figure out
how to create a link to the Oracle server.

This will only allow you to store more than 2 Gb of data, to be precise 4Gb.

What kind of data processing are you doing in Access, which can't be
done on the Oracle server?

mfG
--> stefan <--

 

[Guest]

Hi Stefan,

Thank you for your reply!

Slow is not necessarily a function of size.

I know, but in my case the database is both rather large (for MS Access) and
rather slow. I'm hoping to speed up Excel application a bit. Also the Access
project is probably going to be lifted to a full scale SQL server environment
at some point, and I'd like to play around with my options first.

Huh? Of what amount of data per report are we talking? 1 Gigabyteor more?

Nope, not even close. But the linked tables in the Access database brings
the speed down significantly compared to importing the tables from Oracle.
Since the data dosn't need to be real-time, I'd much prefer some form of
stored procedure. I know I can do all this in Access, but again I'd also like
to play a bit with the SQL server for a while.

This will only allow you to store more than 2 Gb of data, to be precise 4Gb.

And that is enough at the moment.

What kind of data processing are you doing in Access, which can't be
done on the Oracle server?

None, I guess. Only the oracle is a live application database, and the
server team will be very reluctant to let me do anything directly on that
database, so I need to move the data elsewhere.

/Sune

 

[DAVID]

Linked tables inside SQL Server are probably turned
off by default.

There was a nasty SQL Server worm which spread by
sending email messages, mostly from the MSDE edition
of SQL Server, partly because people had not kept
their MSDE copies patched and secure.

Partly as a response to that experience, MS went
through an excercise of setting the default values
of all properties in SQL Server to the state where
it is Not possible to communicate without outside
processes.

If you ask in a SQL Server group, people will be
able to point you towards settings required for
ODBC linked tables, and the syntax required.

However, note that, on the same hardware, there is
no reason to expect SQL Server Express to be an
faster than Access/Jet. SQL Server is designed to
handle more connections, it optimises memory use
and disk use differently than Jet does, but that
is not faster.

(david)

 

[Guest]

Hi David,

Thank you for an informative answer!

I have asked in a more specialised group, and hope that will bring me a bit
closer to an answer.

Cheers,

/Sune