Dynamic Connection

[Michael Conroy]

For the first time I am being asked to use Access to connect to other
databases rather than my normal split on a share. Assuming I can ping the
Oracle and SQLServer databases and that I have a valid account with username
and password, how do I get the information into my database? I am pretty sure
we are beyond linked tables so any advice would be greatly appreciated.
Thanks.

 

[Jeff Boyce]

Actually, you'd still be working with "linked" tables.

It's just that you'd need to set up ODBC connections (Data Source Name) so
Access knows how to do the translation.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Michael Conroy]

Jeff,
Thanks for answering. I know a DNS connection has to be made, but I need to
know what to do afterwards. How do you link a table to an Oracle table? Do I
create a table with no data in it in Access and get the field names of the
corresponding Oracle table and make those the field names of my empty table?
Will the DNS connection keep that updated. Do I run a make table query or can
I list an Oracle table as the source of a query? Basically, how do I point
Access at the Oracle table?

--
Michael Conroy
Stamford, CT

Actually, you'd still be working with "linked" tables.

It's just that you'd need to set up ODBC connections (Data Source Name) so
Access knows how to do the translation.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Michel Walsh]

In the Get External Data... Link Table... you get a Dialog box. Change the
Files of Type to ODBC Databases(). A wizard will take charge of your DNS (or
allow you to make one) and specify what you want in the other db. The end
result *is* a linked table (or linked tables).


Vanderghast, Access MVP

 

[Michael Conroy]

Michel,
Yes, that got me a step closer. However, after steping through the wizard, I
find myself unsure which driver to select. I guessed "Access for Oracle"
which was the only one there with Oracle in it, only to have it tell me the
Oracle client is not installed. The IT department is supposed to come by and
do that; I am not allowed. Once that happens, are you saying I should get a
list of available tables on that server and that I can select the ones I need
data from? So a discussion with the DBA is probably a good idea.
For the SQLserver connection, any recommendation on which driver is
preferable and is the connection basically the same?

 

[dch3]

I've never linked to an Oracle database (but given the project I'm working on
that might happen soon), but I have linked to SQLServer. It really is very
straight forward. You'll be able to access everything that the user ID that
you're using has access to. Its very much like linking to tables in another
*.mdb file, its just that getting there is different.

 

[Michel Walsh]

Same no-experience about Oracle here, but for MS SQL Server, that is,
indeed, quite straight forward.

Bringing the mda may be a good idea, it may re-assure him / her !

Vanderghast, Access MVP

 

[dch3]

I ment to type DBA.

Same no-experience about Oracle here, but for MS SQL Server, that is,
indeed, quite straight forward.

Bringing the mda may be a good idea, it may re-assure him / her !

Vanderghast, Access MVP

 

[Michel Walsh]

Thanks ! nice to see there is someone out there to even correct my typos!



Vanderghast, Access MVP

 

[Michael Conroy]

Thank you both for the input. Now I am really curious about how these
connections are made. Once you get this under your belt you can really go
anywhere and do anything with Access. Looking forward to it.