[AccessD] Linking to SQL Server table from Access - desired table does not show in list

Guss Ginsburg guss at beechnutconsulting.com
Sat Dec 14 19:37:48 CST 2013


The error I get is "ODBC--call failed", but not until I try to open the
table to view the records.  When I do the link, it indicates the connection
test passed successfully.  I will redefine it to create an error log table,
and that info may help.

Thanks  for the suggestions.

Sincerely yours,
 
Guss Ginsburg

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Randy Anthony
Sent: Saturday, December 14, 2013 4:05 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Linking to SQL Server table from Access - desired
table does not show in list

I glossed over your last sentence.  If the ODBC call failed before
displaying your table, that's the issue.  Technically, if you get the ODBC
call fail notice, you can't open the table at all.  Pay particular attention
to the error message, is it creating an error log table?  That's probably
why the table has the #Name error in the columns.  If you're linking, then
you shouldn't have any problems with data size, however this seems to
indicate that you haven't solved the ODBC connection yet.  One thing to
check for the ODBC connection is that you have the appropriate SQL checked,
depending on your environment, you may have SQL Server, SQL Server Native
Client 10.0 and/or SQL Server Native Client 11.0.

HTH.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Guss Ginsburg
Sent: Saturday, December 14, 2013 4:28 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Linking to SQL Server table from Access - desired
table does not show in list

I am linking only.  I am using SQL Server because I have so many records,
but the queries I need to run will return quite manageable recordsets.



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Randy Anthony
Sent: Saturday, December 14, 2013 2:09 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Linking to SQL Server table from Access - desired
table does not show in list

Ok, yw, that was easy.  If the DB/table you're linking to has 15 million
records, methinks you've ran afoul of the max size limits of Access, which
is 2GB.  Are you linking to or importing from?

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Guss Ginsburg
Sent: Saturday, December 14, 2013 2:15 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Linking to SQL Server table from Access - desired
table does not show in list

I checked that, and the user id is already set up like you said it should.
I just discovered another issue, but let me give a little background...  I
originally installed the SQL Server environment a couple of years ago, and
set up a test database, imported some data into a table, and when I click
from Access on the ODBC choices, then on machine data sources, there is one
called SQLServerDataSource, which I may have created back then.  That one
shows the table from the test db I created 2 years ago.  I cannot see that
table if I go via the File Data Source.  I cannot see the table I just
imported by going any of the paths I have tried so far.  

Upon further checking, I looked into the definition of the above data source
and saw that it was pointing to the test database from 2 years ago.  I
changed the selection to the new db, and am linking to it now.  I also
linked to a test file from two years ago.  I can view the old test file just
fine, but the new one only has 63 records (there should be about 15
million), and each cell contains #Name?.  I also get a message that the ODBC
call failed before displaying the 63 records.

Thanks for your help so far.  Any other thoughts are certainly welcome.

Sincerely yours,
 
Guss Ginsburg

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Randy Anthony
Sent: Saturday, December 14, 2013 12:30 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Linking to SQL Server table from Access - desired
table does not show in list

No prob.  This assumes you're connecting to your SQL end with Windows Auth
and you have dbo permissions on the server.  Open SSMS and click
Security>Logins.  Right click your login account and select Properties.
Click User Mapping in the left hand pane, locate the DB, check it and check
db_owner on the bottom.  Click Ok.  

Now when you open your link to server and get the ODBC portion, you should
be able to see the DB and the tables therein.

HTH.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Guss Ginsburg
Sent: Saturday, December 14, 2013 12:36 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Linking to SQL Server table from Access - desired
table does not show in list

Randy,
Thanks.  Yes, you have described the environment I am in.  I am pretty new
to using this, and SQL databases, so I will need some specific help walking
through this.  I see there are permission options for the SQL Server, and
for each database.  And I believe I want to grant some kind of permission to
schema dbo (is that right?), but don’t know where I am stepping now.  Can
you give me the specific steps to add the permissions needed?

Sincerely yours,
 
Guss Ginsburg

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Randy Anthony
Sent: Saturday, December 14, 2013 10:49 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Linking to SQL Server table from Access - desired
table does not show in list

Guss,
Are you importing/viewing the data via SSMS?  Then when you open Access and
try to link you can't see the tables to pick from?  If so, you need to add
permissions to the SQL side so that your ODBC connection will allow you to
see/select the tables.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Guss Ginsburg
Sent: Saturday, December 14, 2013 11:02 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Linking to SQL Server table from Access - desired table
does not show in list

i imported the table into SQL Server, and it shows up on my computer in the
database, but when i try to link to it, the list of tables that comes up are
only system tables, and the data i am looking for is not shown. Did i leave
out a step in the process of creating the db or importing the file?  

 

Sincerely yours,

 

Guss Ginsburg

 


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list