No Indexes - Linking to MS Access

Hello,

I am using MS Access 2000 as a front end to a SQL Server 2000 db. When I link to the SQL Server db using a DSN, the SQL Server table pk's and indexes are not coming over with the link. The pk's and indexes do exist in SQL Server. I haven't had to link to these tables in over a year, but they used to link just fine.

Do you have a suggestion as to where to look at this problem?

Thanks you - Darryle

Please define the problem. Are you trying to “design” SQL Server tables form Access? If there are indices in SQL Server, it will take advantage of them when you send queries. You can link to the sysindexes table in your database, and you can query what indices you have in SQL Server for that database.

Zlatko

|||

It has been a while since I have done this type of linking. But it seemed that when I used to link to SQL Server from Access, the linking process would automatically figure out what the primary key was and also see the other indexes on the SQL table. I have an old Acccess front end, when I look at the link tables in design mode (I know I can't change it, just looking) I see the pk's and the indexes.

It seems that now when I link a table from Access, it asks me to pick the pk column, which I do. Once I have linked and I look at the linked table in design mode in Access, I see the pk but none of the indexes show. These are the same tables that had linked to before. Is this a problem? Will the indexes set up in SQL Server be used in Access even though I don't see them?

Thanks for you help and I'll be glad to give you any further information - Darryle

|||

Your question is entirely related to Access and not to SQL Server. Unfortunately I don’t see a thread dedicated to Access programming. You may want to try http://asp.net.

Consider “pass through” queries. They send a native SQL statement to the linked store, and receive the final result – all the processing is done at the server. In that case you don’t care about indices being replicated. Normal Access queries perform the processing in Access, and they need indices but the approach of using SQL Server only for storage and not for processing is near useless.

sql

No Indexes - Linking to MS Access

Hello,

I am using MS Access 2000 as a front end to a SQL Server 2000 db. When I link to the SQL Server db using a DSN, the SQL Server table pk's and indexes are not coming over with the link. The pk's and indexes do exist in SQL Server. I haven't had to link to these tables in over a year, but they used to link just fine.

Do you have a suggestion as to where to look at this problem?

Thanks you - Darryle

Please define the problem. Are you trying to “design” SQL Server tables form Access? If there are indices in SQL Server, it will take advantage of them when you send queries. You can link to the sysindexes table in your database, and you can query what indices you have in SQL Server for that database.

Zlatko

|||

It has been a while since I have done this type of linking. But it seemed that when I used to link to SQL Server from Access, the linking process would automatically figure out what the primary key was and also see the other indexes on the SQL table. I have an old Acccess front end, when I look at the link tables in design mode (I know I can't change it, just looking) I see the pk's and the indexes.

It seems that now when I link a table from Access, it asks me to pick the pk column, which I do. Once I have linked and I look at the linked table in design mode in Access, I see the pk but none of the indexes show. These are the same tables that had linked to before. Is this a problem? Will the indexes set up in SQL Server be used in Access even though I don't see them?

Thanks for you help and I'll be glad to give you any further information - Darryle

|||

Your question is entirely related to Access and not to SQL Server. Unfortunately I don’t see a thread dedicated to Access programming. You may want to try http://asp.net.

Consider “pass through” queries. They send a native SQL statement to the linked store, and receive the final result – all the processing is done at the server. In that case you don’t care about indices being replicated. Normal Access queries perform the processing in Access, and they need indices but the approach of using SQL Server only for storage and not for processing is near useless.

No Failover and client crashes ODBC DSN Setting for Failover in connection String

We have set up Mirroring with a witness server and everything works fine when we failover from the SQL Management console.

However, if we failover when our Maccola client is connected, the client blows up - clearly because it can no longer connect to the database.

The ODBC DSN used by the Maccola client shows a checkbox for the 'select a failover server' but the checkbox is grayed out.

Also the summary of settings for the DSN at the end of the wizard reveals that the failover to server (y/N) option is set to N.

The default setting for this DSN is 'populate the remaining values by querying the server' but it doesn't appear to be getting the settings for failover from the server or any other interactive DSN settings either. The server is clearly set for mirroring.

Another suspicious item is that the DSN cannot connect to the server with SA permissions, even though the server is set to mixed security and we use the correct authentication.

Is it possible that the client MACHINE is not authenticating with the domain or sql server properly. We are logged into the client with the domain account that is the SQL admin account on the sql server box.

We should be able to interact with the sql server settings through the ODBC DSN on the client shoulnd't we?

Are we missing a service pack on the client?

Thanks,

Kimball

Have you referred to Maccola vendor in this case about the compatibility, I would guess this is to with that drivers.

No Failover and client crashes ODBC DSN Setting for Failover in connection String

We have set up Mirroring with a witness server and everything works fine when we failover from the SQL Management console.

However, if we failover when our Maccola client is connected, the client blows up - clearly because it can no longer connect to the database.

The ODBC DSN used by the Maccola client shows a checkbox for the 'select a failover server' but the checkbox is grayed out.

Also the summary of settings for the DSN at the end of the wizard reveals that the failover to server (y/N) option is set to N.

The default setting for this DSN is 'populate the remaining values by querying the server' but it doesn't appear to be getting the settings for failover from the server or any other interactive DSN settings either. The server is clearly set for mirroring.

Another suspicious item is that the DSN cannot connect to the server with SA permissions, even though the server is set to mixed security and we use the correct authentication.

Is it possible that the client MACHINE is not authenticating with the domain or sql server properly. We are logged into the client with the domain account that is the SQL admin account on the sql server box.

We should be able to interact with the sql server settings through the ODBC DSN on the client shoulnd't we?

Are we missing a service pack on the client?

Thanks,

Kimball

Have you referred to Maccola vendor in this case about the compatibility, I would guess this is to with that drivers.

No Client Protocols Enabled yet I can still get a DSN connection to SQL Server 2005

Perplexed...Why am I able to get a connection to my SQL Server from an
Access 2003 database via DSN linked SQL server db table without a
common protocol between the client and server? Could it be that the
client does have TCP/IP or Named Pipes enabled but they are just not
showing up in the cliconfg.exe utility as such? Help!Clients have always TCP/IP and/or Named Pipes enabled. This setting is for
the server, not for the client tools.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"lacdn76" <dakarpiak@.yahoo.com> wrote in message
news:1175526801.931586.126790@.y66g2000hsf.googlegroups.com...
> Perplexed...Why am I able to get a connection to my SQL Server from an
> Access 2003 database via DSN linked SQL server db table without a
> common protocol between the client and server? Could it be that the
> client does have TCP/IP or Named Pipes enabled but they are just not
> showing up in the cliconfg.exe utility as such? Help!
>

No Client Protocols Enabled yet I can still get a DSN connection to SQL Server 2005

Perplexed...Why am I able to get a connection to my SQL Server from an
Access 2003 database via DSN linked SQL server db table without a
common protocol between the client and server? Could it be that the
client does have TCP/IP or Named Pipes enabled but they are just not
showing up in the cliconfg.exe utility as such? Help!
Clients have always TCP/IP and/or Named Pipes enabled. This setting is for
the server, not for the client tools.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"lacdn76" <dakarpiak@.yahoo.com> wrote in message
news:1175526801.931586.126790@.y66g2000hsf.googlegr oups.com...
> Perplexed...Why am I able to get a connection to my SQL Server from an
> Access 2003 database via DSN linked SQL server db table without a
> common protocol between the client and server? Could it be that the
> client does have TCP/IP or Named Pipes enabled but they are just not
> showing up in the cliconfg.exe utility as such? Help!
>