Roberto Ford Long
rford at terra.com.br
Sun Dec 15 05:26:28 CST 2013
Hi Guss. Did you try to relink that table? Just opening the link table form, mark tables that use that ODBC connection and reassign then again. Regards, Roberto. > -----Mensagem original----- > De: accessd-bounces at databaseadvisors.com [mailto:accessd- > bounces at databaseadvisors.com] Em nome de Guss Ginsburg > Enviada em: sábado, 14 de dezembro de 2013 23:38 > Para: 'Access Developers discussion and problem solving' > Assunto: Re: [AccessD] Linking to SQL Server table from Access - desired > table does not show in list > > 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 > > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com