Understanding ODBC connections...!

[Phil Latio]

Scenario is:
We have an intranet on which a Helpdesk application can be accessed.
Data input into the Helpdesk is stored in a Access 2000 database, held
on a server. This is great, works fine, Users and IT have a warm fuzzy
feeling of contentment.

Turns out Helpdesk application is short of decent reporting
capabilities.
So, I create an Access DB on my machine, via ODBC, link to the tables
within the Helpdesk Access database. I set the ODBC driver to ReadOnly,
so that I don't wreck anything!
I can create my queries and reports, no problem (for me anyhow!).

Problem is, whenever I'm running queries (SELECTs only) using the
linked tables (from the Helpdesk DB) the Users of the intranet helpdesk
start getting errors along the lines of 'must use updateable queries -
MS Jet/Access ODBC errors' etc.

Could someone point me off as how to avoid this scenario. It was my
understanding that if I was using ODBC, I could grab the data into my
linked 'reporting' database and not cause errors within the Helpdesk
data repository.

If anyone would like to fill my obvious lack of knowledge, I would be
most grateful.

Thanks in advance.

Cheers,

Phil

[MGFoster]

Phil Latio wrote:

Scenario is:
We have an intranet on which a Helpdesk application can be accessed.
Data input into the Helpdesk is stored in a Access 2000 database, held
on a server. This is great, works fine, Users and IT have a warm fuzzy
feeling of contentment.

Turns out Helpdesk application is short of decent reporting
capabilities.
So, I create an Access DB on my machine, via ODBC, link to the tables
within the Helpdesk Access database. I set the ODBC driver to ReadOnly,
so that I don't wreck anything!
I can create my queries and reports, no problem (for me anyhow!).

Problem is, whenever I'm running queries (SELECTs only) using the
linked tables (from the Helpdesk DB) the Users of the intranet helpdesk
start getting errors along the lines of 'must use updateable queries -
MS Jet/Access ODBC errors' etc.

Could someone point me off as how to avoid this scenario. It was my
understanding that if I was using ODBC, I could grab the data into my
linked 'reporting' database and not cause errors within the Helpdesk
data repository.

If anyone would like to fill my obvious lack of knowledge, I would be
most grateful.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You don't need to use ODBC to link Access to Access. Are you sure you
really are using ODBC? An ODBC linked table will have "ODBC;" at the
beginning of the connection string (the table's Description property).
An Access to Access connection will have "Database=" at the beginning of
the connection string.

I didn't know you could set a connection string to "read-only." How'd
you do that? Perhaps that's what's causing the problem.
--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQyCcNoechKq OuFEgEQJX/QCgwUKBV4r6h0/nKd0/NF2+Zc3/364AoJ7x
riN215PS741a9aJ foTc0IC/G
=c1NC
-----END PGP SIGNATURE-----

[Phil Latio]

Hi MGFoster

Thanks for the reply - sorry for my delay in responding (I use Google
groups via the web at work!)

I've looked at what I've done and it seems I created a ODBC User DSN
(which I intended to use with Cognos Impromptu).
Within the DSN configuration was a check-box for 'Exclusive' and 'Read
Only'. I checked the 'Read Only' option. I assumed that would make the
data read-only.
For some reason I used this DSN to connect, rather than Access to
Access - Doh!

I've binned the ODBC DSN and now linking Access to Access.
I've just tested this and the Intranet Helpdesk application still
throws up the errors about 'must use updateable query...'
I didn't expect that to happen - I was expecting to be able to use
simple SELECTs querys (on only one table) and not cause problems for
the helpdesk database!

Now that I'm linking Access to Access, is there something I should be
doing to alleviate these problems?

Thanks in advance.

Phil

[MGFoster]

Phil Latio wrote:

Hi MGFoster

Thanks for the reply - sorry for my delay in responding (I use Google
groups via the web at work!)

I've looked at what I've done and it seems I created a ODBC User DSN
(which I intended to use with Cognos Impromptu).
Within the DSN configuration was a check-box for 'Exclusive' and 'Read
Only'. I checked the 'Read Only' option. I assumed that would make the
data read-only.
For some reason I used this DSN to connect, rather than Access to
Access - Doh!

I've binned the ODBC DSN and now linking Access to Access.
I've just tested this and the Intranet Helpdesk application still
throws up the errors about 'must use updateable query...'
I didn't expect that to happen - I was expecting to be able to use
simple SELECTs querys (on only one table) and not cause problems for
the helpdesk database!

Now that I'm linking Access to Access, is there something I should be
doing to alleviate these problems?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I don't know. I thought the ODBC connection may have been throwing up
something that was causing the error. Continue to use the Access to
Access connection, since that is the perferred method.

I found an article in MS KnowledgeBase (http://tinyurl.com/9h264) that
supposedly fixes a similar problem.

Rgds,
--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQyHWboechKq OuFEgEQIpiwCglV A1ccr5qCYg790R2 Fuz3MfpDR0An3U7
xuc93xDBvDITi75 0C7lh0VWW
=D8kE
-----END PGP SIGNATURE-----

[Phil Latio]

Hi

Thanks for the further response - you've prompted me into action here!

I've just checked the Service Pack scenario at work and to be honest,
it's shocking! having only just joined my current company, and I've now
gone poking around the system - lo and behold, we're running MS Office
2000 with NO Service Packs applied! Shocking! Absolutlely
in-excuseable!

So, before I look at fixing my problem, I'll sort out the Service
Packs. Then, I'm in a bit better position ask for assistance!

Many thanks for assistance - I'll post back if I still encounter
problems post-service packing!

Cheers,

Phil
MGFoster wrote:

Phil Latio wrote:

Hi MGFoster

Thanks for the reply - sorry for my delay in responding (I use Google
groups via the web at work!)

I've looked at what I've done and it seems I created a ODBC User DSN
(which I intended to use with Cognos Impromptu).
Within the DSN configuration was a check-box for 'Exclusive' and 'Read
Only'. I checked the 'Read Only' option. I assumed that would make the
data read-only.
For some reason I used this DSN to connect, rather than Access to
Access - Doh!

I've binned the ODBC DSN and now linking Access to Access.
I've just tested this and the Intranet Helpdesk application still
throws up the errors about 'must use updateable query...'
I didn't expect that to happen - I was expecting to be able to use
simple SELECTs querys (on only one table) and not cause problems for
the helpdesk database!

Now that I'm linking Access to Access, is there something I should be
doing to alleviate these problems?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I don't know. I thought the ODBC connection may have been throwing up
something that was causing the error. Continue to use the Access to
Access connection, since that is the perferred method.

I found an article in MS KnowledgeBase (http://tinyurl.com/9h264) that
supposedly fixes a similar problem.

Rgds,
--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQyHWboechKq OuFEgEQIpiwCglV A1ccr5qCYg790R2 Fuz3MfpDR0An3U7
xuc93xDBvDITi75 0C7lh0VWW
=D8kE
-----END PGP SIGNATURE-----