SQL Server Table (1 Viewer)

[robertbwainwright]

Background:

I have a DB that someone else set up and since has left our company. In the DB he has created a linked table in Access to SQL Server. The table has been modified in SQL Server and some columns added. In the past I have had to delete the old linked table and re-link the table to get the new columns to show in the linked table in access.

Problem:

The linked tables in access are set up so the user does not need to have an ODBC connection mapped. They appear as a linked table, but when I look at the address I notice it does not start with ODBC;DSN=..... it says ODBC;Description=..... Could this be a DSN-less connection? If so how do I re-create the table? If not what kind of connection might this be and again, how do I create?

thanks,
Robert

 

[pbaldy]

On the one hand, it sounds like it could be a DSNless connection. Mine don't start exactly like that, but I'm using the Native Client, and there are different methods. That said, mine relink when I start the db, so they would already be seeing the new fields. Is there code that runs on startup to relink?

 

[Simon_MT]

There are several methods. I understand that that a ODBC with a Passthru Query is the most efficient method to extract information from a SQL Server. It goes with saying all data should be filtered in large datasets.

Simon

 

[robertbwainwright]

There are several methods. I understand that that a ODBC with a Passthru Query is the most efficient method to extract information from a SQL Server. It goes with saying all data should be filtered in large datasets.

Simon

Thanks Simon,

I understand pass through queries and have used several times, but the query is not my question, it is the linked table. Some how the person who created the DB was able to make a linked table to a sql server backend without having an explicit odbc connection. Furthermore the db does not have code behind it to set up the connection via VBA. This is a rather elegant solution as the odbc connection does not have to be set up on every PC that utilizes this access front end.

 

[robertbwainwright]

On the one hand, it sounds like it could be a DSNless connection. Mine don't start exactly like that, but I'm using the Native Client, and there are different methods. That said, mine relink when I start the db, so they would already be seeing the new fields. Is there code that runs on startup to relink?

Pbaldy,

No, there is not any code running on start up. I have done a number of web searches about DSN-less connections and I have seen some code that connects like you have described.

thanks for your help,
Robert

 

[pbaldy]

Have you tried selecting the table in the Linked Table Manager and hitting ok to refresh the link?

It could be a File DSN was used, which doesn't require anything on the user PC either.

 

[robertbwainwright]

Have you tried selecting the table in the Linked Table Manager and hitting ok to refresh the link?

It could be a File DSN was used, which doesn't require anything on the user PC either.

Pbaldy,

Yes, that works to refresh the new fields. I am still interested in knowing how this was set up as it would be helpful in the future.

thanks,
Robert

 

[Galaxiom]

I am still interested in knowing how this was set up as it would be helpful in the future.

Linking using a file DSN generated by the Access connection wizard creates a DSNless connection that begins exactly as you described in the opening post. It is the easiest way to create a DSNless connection.

Description is an optional connection string attribute so it is often omitted where the developer has directly entered the connection string.