Linking an Access Database to an SQL Database Table

[LOgle via AccessMonster.com]

I want to link a Demographics table to my table in Access that gives me Pt
Information. I want the table I link to fill in the from the Demographics
table to my Access Table stuff like PtName, MRNUM etc. Can I do this and how
do I do this. I am using Access 2003.

Thank you!

 

[Ed B]

Hi LOgle,

I'm not sure I fully understand the problem from your description, but if
the Demographics table is in a SQLServer database, and you only need to read
its data in your Access application, your best bet is probably to have one of
your SQLServer gurus/dbadmins create a View (that's what a query is called in
SQLServer) of the data that you might need.

Then File...Get External Data...Link Tables using the ODBC driver to the SQL
Server where the new View resides. You'll have to be granted SELECT (i.e.
Read) permission for the View form the SQL Server side. Depending on the SQL
Server security settings, you may need to have a password to enter if a new
ODBC link is needed, or the dbadmin may have to add each databse user as a
SQLServer user who can then be authenticated via NT login.

Once the link has been established , you can refer to the data in the linked
'table' just as you would any other table in a query, etc.

Hope that helps,

Ed

 

[LOgle via AccessMonster.com]

I want to link a Demographics table to my table in Access that gives me Pt
Information. I want the table I link to fill in the from the Demographics
table to my Access Table stuff like PtName, MRNUM etc. Can I do this and how
do I do this. I am using Access 2003.

Thank you!

I actually was able to link the tables I needed but when I try to createa
query linking the external tables and my access tables no data shows up in my
query. I am using a Charting Table, DemographicsTable (both are the linked
tables) then the Inovent Table and Tank log Table both are access tables I
have them linked as follows :

Charting to Demographics by AcctID "Only Include Rows where the joined fields
from both tables are equal"
then from Demographics to Inovent by MR# "Only Include Rows where the joined
fields from both tables are equal"
then the Inovent and Tank Log are joined as they normally are by the PtNumber
"Only Include Rows where the joined fields from both tables are equal" with a
one to many relationship.

I am not the greatest at relationship joining so I know this is probably the
problem and I usually let the system do it and this is actually what was done
but no data shows up. Can you suggest another way to join these tables. The
fields I mentioned are the only ones they have in common. Thanks!!

 

[Tom Ellison]

Dear LOgle:

One thing at a time, and slowly, please!

The Demographics table and the Pt Information table have what column(s) in
common? I recommend you TEST the relationship with some scratch queries.
Based on the relationship you BELIEVE exists, are there rows in the
Demographics table that are for a Point (is that what the relationship is
about) that is NOT in the Pt Information table? I would check this out
before trying to create a relationship that may not exist. Use LEFT and
RIGHT JOINs to determine whether there are missing relationships. Observe
this and work out how to fix it, if possible.

With work like this, if you do not identify the requirements and test the
data one step at a time, you'll leap forward to a point where failure may
indicate any one of a large number of possibilities. The work needs to be
done in small steps so that failure indicates only one or a very small
number of possible sources of problems, so you can isolate and defeat them
one at a time. This doesn't have to take a great deal of time and, in any
case, almost always takes less time and effort than taking a big step and
getting confused and discouraged.

Work on the problem one relationship at a time. Slow and systematic wins
the day. Identify your assumptions and test each one carefully before
proceeding. When you bring in external data, be very suspect.

Tom Ellison

 

[LOgle via AccessMonster.com]

I actually did test these db out linking them one at a time. I finally got
my data to show on half of the query where information from the 2 linked
databases are or I can get it to give me data from the 2 access databases.
The joining field that links the 2 linked to the 2 Access is the Med Rec
Number. These numbers are unique but in the linked db the number comes up
for example as 024107872 and 24107872 in the AccessDB. Could this be causing
the problem? I am trying to create a query that shows me everything from all
4 tables where the MRNUm in Demo and the MRNum in Ino match.

Thanks!

Dear LOgle:

One thing at a time, and slowly, please!

The Demographics table and the Pt Information table have what column(s) in
common? I recommend you TEST the relationship with some scratch queries.
Based on the relationship you BELIEVE exists, are there rows in the
Demographics table that are for a Point (is that what the relationship is
about) that is NOT in the Pt Information table? I would check this out
before trying to create a relationship that may not exist. Use LEFT and
RIGHT JOINs to determine whether there are missing relationships. Observe
this and work out how to fix it, if possible.

With work like this, if you do not identify the requirements and test the
data one step at a time, you'll leap forward to a point where failure may
indicate any one of a large number of possibilities. The work needs to be
done in small steps so that failure indicates only one or a very small
number of possible sources of problems, so you can isolate and defeat them
one at a time. This doesn't have to take a great deal of time and, in any
case, almost always takes less time and effort than taking a big step and
getting confused and discouraged.

Work on the problem one relationship at a time. Slow and systematic wins
the day. Identify your assumptions and test each one carefully before
proceeding. When you bring in external data, be very suspect.

Tom Ellison
[quoted text clipped - 31 lines]

The
fields I mentioned are the only ones they have in common. Thanks!!

 

[Tom Ellison]

Dear LOgle:

The leading zero leads me to believe the Med Rec Number is character data.
When you create the join, it may be that the other, possibly numeric value,
is being converted to a string, and without the leading zero. If you
convert the one that does have the leading zero to a long integer, then join
them, this may fix the problem.

Tom Ellison

I actually did test these db out linking them one at a time. I finally got
my data to show on half of the query where information from the 2 linked
databases are or I can get it to give me data from the 2 access databases.
The joining field that links the 2 linked to the 2 Access is the Med Rec
Number. These numbers are unique but in the linked db the number comes up
for example as 024107872 and 24107872 in the AccessDB. Could this be
causing
the problem? I am trying to create a query that shows me everything from
all
4 tables where the MRNUm in Demo and the MRNum in Ino match.

Thanks!

Dear LOgle:

One thing at a time, and slowly, please!

The Demographics table and the Pt Information table have what column(s) in
common? I recommend you TEST the relationship with some scratch queries.
Based on the relationship you BELIEVE exists, are there rows in the
Demographics table that are for a Point (is that what the relationship is
about) that is NOT in the Pt Information table? I would check this out
before trying to create a relationship that may not exist. Use LEFT and
RIGHT JOINs to determine whether there are missing relationships. Observe
this and work out how to fix it, if possible.

With work like this, if you do not identify the requirements and test the
data one step at a time, you'll leap forward to a point where failure may
indicate any one of a large number of possibilities. The work needs to be
done in small steps so that failure indicates only one or a very small
number of possible sources of problems, so you can isolate and defeat them
one at a time. This doesn't have to take a great deal of time and, in any
case, almost always takes less time and effort than taking a big step and
getting confused and discouraged.

Work on the problem one relationship at a time. Slow and systematic wins
the day. Identify your assumptions and test each one carefully before
proceeding. When you bring in external data, be very suspect.

Tom Ellison

I want to link a Demographics table to my table in Access that gives me
Pt
Information. I want the table I link to fill in the from the
Demographics

[quoted text clipped - 31 lines]

The
fields I mentioned are the only ones they have in common. Thanks!!