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.
--
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

"Michael Conroy" <(E-Mail Removed)> wrote in message
news:A7A5B450-E6B3-49E4-9D46-(E-Mail Removed)...
> 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.
> --
> Michael Conroy
> Stamford, CT

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


"Jeff Boyce" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:A7A5B450-E6B3-49E4-9D46-(E-Mail Removed)...
> > 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.
> > --
> > Michael Conroy
> > Stamford, CT
>
>
>

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" <(E-Mail Removed)> wrote in message
news:4C6FA5DF-755E-443C-A37C-(E-Mail Removed)...
> 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
>
>

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?
--
Michael Conroy
Stamford, CT


"Michel Walsh" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:4C6FA5DF-755E-443C-A37C-(E-Mail Removed)...
> > 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
> >
> >
>
>
>

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.

"Michael Conroy" wrote:

> 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?
> --
> Michael Conroy
> Stamford, CT
>
>
> "Michel Walsh" wrote:
>
> > 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" <(E-Mail Removed)> wrote in message
> > news:4C6FA5DF-755E-443C-A37C-(E-Mail Removed)...
> > > 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
> > >
> > >
> >
> >
> >

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" <(E-Mail Removed)> wrote in message
news:0431BB10-1D31-42F4-B900-(E-Mail Removed)...
> 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.
>
> "Michael Conroy" wrote:
>

I ment to type DBA.

"Michel Walsh" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:0431BB10-1D31-42F4-B900-(E-Mail Removed)...
> > 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.
> >
> > "Michael Conroy" wrote:
> >
>
>
>

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


Vanderghast, Access MVP


"dch3" <(E-Mail Removed)> wrote in message
news:B2546733-256C-4FEC-93D2-(E-Mail Removed)...
>I ment to type DBA.
>
> "Michel Walsh" wrote:
>
>> 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
>>
>>

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.
--
Michael Conroy
Stamford, CT


"Michel Walsh" wrote:

> Thanks ! nice to see there is someone out there to even correct my typos!
> :-)
>
>
> Vanderghast, Access MVP
>
>
> "dch3" <(E-Mail Removed)> wrote in message
> news:B2546733-256C-4FEC-93D2-(E-Mail Removed)...
> >I ment to type DBA.
> >
> > "Michel Walsh" wrote:
> >
> >> 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
> >>
> >>
>
>
>