ODBC connection

[christshis]

Hi Everybody!

I am creating an Access database that will be used by multiple users. This database will be on the network and will be the front end of an oracle database connected to it via ODBC. Do I have to set up the ODBC to oracle only on the machine that hold the database on the network or do I have to set up an ODBC on each user's machine?

Thank you for your help.

 

[pbaldy]

For starters, to avoid corruption each user should have a copy of the front end on their PC. In any case, the ODBC would have to be on each PC. However, presuming Oracle is like SQL Server, I set up a File DSN (not User or System) on the server and use that to link my tables, and then the clients don't need one.

 

[namliam]

Using DBEngine.RegisterDatabase you can setup a (user) DSN to use for the ODBC connection. This way everytime anyone opens your DB anywhere they automaticaly create that DSN and you dont have to do it manually on every machine....

 

[christshis]

ODBC Connecition

Thank you guys for responding. Can you give me more details about the following:

"Using DBEngine.RegisterDatabase". I have never heard of it.

Thank you.

 

[john471]

For starters, to avoid corruption each user should have a copy of the front end on their PC. In any case, the ODBC would have to be on each PC. However, presuming Oracle is like SQL Server, I set up a File DSN (not User or System) on the server and use that to link my tables, and then the clients don't need one.

Hey Paul,

Can you please expand on this at all... I'd specifically like to know if you are saying that creating a File DSN on the network alleviates the need for the ODBC client to be installed on the local PC ???

Bit of background - I have an AC97 DB that uses ODBC to a SQL Server. I have several dozen users at three remote sites. The SQL Server ODBC software is included in our corporate standard PC operating environment, so using this with DSN-Less connections is relatively expedient. However - we are soon moving the BE from SQL Server to Oracle platform. The Oracle ODBC driver is not part of the corporate standard - so I am in fear of having to organise to have it installed on dozens of PCs at remote sites in an out-sourced desktop managed situation If there is a way around this...-->

Thanks for any help.

John.

 

[john471]

P.S Sorry christshis - I hope I haven't hijacked your thread !

 

[namliam]

Thank you guys for responding. Can you give me more details about the following:

"Using DBEngine.RegisterDatabase". I have never heard of it.

Thank you.

Type it in a module, select "RegisterDatabase" and hit F1, much more help there than I can provide (or I can copy paste it for you)

 

[namliam]

Hey Paul,

Can you please expand on this at all... I'd specifically like to know if you are saying that creating a File DSN on the network alleviates the need for the ODBC client to be installed on the local PC ???

Bit of background - I have an AC97 DB that uses ODBC to a SQL Server. I have several dozen users at three remote sites. The SQL Server ODBC software is included in our corporate standard PC operating environment, so using this with DSN-Less connections is relatively expedient. However - we are soon moving the BE from SQL Server to Oracle platform. The Oracle ODBC driver is not part of the corporate standard - so I am in fear of having to organise to have it installed on dozens of PCs at remote sites in an out-sourced desktop managed situation If there is a way around this...-->

Thanks for any help.

John.

I think there is something like an Oracle Instant client. This need not be installed and you can do connections to Oracle databases without even making a DSN... I have never had to rely on it tho as in my workplace it (Oracle client) IS part of the default client installation.

I would have to check a coworker of mine for more details on that instant client, but try googling... If that fails I can ask around for you...

 

[john471]

Thanks mailman,

On your suggestion I've googled Oracle Instant Client, and from what I understand I would still need to install the instant client on each PC, which in our out-sourced desktop and network environment is a *lot* harder than it really should be. So I'm still wondering if PBaldy's solution might help to bypass that hurdle.

Thanks & regards

John.

 

[namliam]

I will try and see what my co-worker has to over in his Invinate wisdom...

 

[john471]

Thanks,

I wait in antici............pation.!

 

[namliam]

He gave me this...

I hope it helps