Changing linked SQL Server tables in code

brucedodds

I've inherited an A2003 application with linked SQL Server 2000 tables
in the back end, using the Microsoft SQL Server ODBC driver.

We've set up a test SQL Server database. I'd like to automate the
process of relinking the application to test/prod.

I've found some code examples here of relinking ODBC back ends to
different databases, but they involve deleting the table before
relinking. My concern is that the primary key information for the
tables will be lost when the table is deleted. A linked ODBC table
can't be updated unless it has a primary key defined.

If anyone can provide information on how to change ODBC linked tables
in code, while maintaining the primary key, I'd appreciate it.

TIA

Bruce

Jun 27 '08 #1

Rich P

Hi Bruce,

Relinking ODBC tables in code is kind of a pain. This is a downside of
ODBC. If the tables were purely static then ODBC is an easy solution,
but whenever you need to make something even just a little bit mroe
dynamic - ODBC becomes a real hassel. ADO is a much easier option to go
with as far as using Code for manipulating data from Sql Server Tables.
With ODBC you have to use a lot of API code. With ADO you just make a
reference to the Microsoft ActiveX DataObjects library (2.5 or higher)
and you can find tons of sample of code on the Net.
Rich

*** Sent via Developersdex http://www.developersdex.com ***

Jun 27 '08 #2

brucedodds

Thanks, Rich. This application is filled with bound objects. Can
they be accomodated via ADO?

On Jun 23, 11:14*am, Rich P <rpng...@aol.comwrote:

Hi Bruce,

Relinking ODBC tables in code is kind of a pain. *This is a downside of
ODBC. *If the tables were purely static then ODBC is an easy solution,
but whenever you need to make something even just a little bit mroe
dynamic - ODBC becomes a real hassel. *ADO is a much easier option to go
with as far as using Code for manipulating data from Sql Server Tables.
With ODBC you have to use a lot of API code. *With ADO you just make a
reference to the Microsoft ActiveX DataObjects library (2.5 or higher)
and you can find tons of sample of code on the Net.

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

Jun 27 '08 #3

lyle fairfield

The primary key is in the SQL database. It cannot be "lost" through
relinking. You are not deleting the table. You are deleting the
TableDef whch lives in the front end and provides the connection with
the SQL Table.

Fast air code (on my way out)

Private Sub lODBCConnection()
Dim tdf As DAO.TableDef
With DBEngine(0)(0)
With .TableDefs
.Delete "FFDBAAccounts"
.Refresh
End With
Set tdf = .CreateTableDef("FFDBAAccounts")
With tdf
.SourceTableName = "FFDBAAccounts"
.Connect = "ODBC" _
& ";Driver={Sql Server}" _
& ";Server=server_name" _
& ";Database=database_name" _
& ";PWD=pass_word" _
& ";UID=user_id"
End With
With .TableDefs
.Append tdf
.Refresh
End With
End With
End Sub

On Jun 23, 10:53*am, brucedo...@comcast.net wrote:

I've inherited an A2003 application with linked SQL Server 2000 tables
in the back end, using the Microsoft SQL Server ODBC driver.

We've set up a test SQL Server database. *I'd like to automate the
process of relinking the application to test/prod.

I've found some code examples here of relinking ODBC back ends to
different databases, but they involve deleting the table before
relinking. *My concern is that the primary key information for the
tables will be lost when the table is deleted. *A linked ODBC table
can't be updated unless it has a primary key defined.

If anyone can provide information on how to change ODBC linked tables
in code, while maintaining the primary key, I'd appreciate it.

TIA

Bruce

Jun 27 '08 #4

Rick Brandt

br********@comcast.net wrote:

I've inherited an A2003 application with linked SQL Server 2000 tables
in the back end, using the Microsoft SQL Server ODBC driver.

We've set up a test SQL Server database. I'd like to automate the
process of relinking the application to test/prod.

I've found some code examples here of relinking ODBC back ends to
different databases, but they involve deleting the table before
relinking. My concern is that the primary key information for the
tables will be lost when the table is deleted. A linked ODBC table
can't be updated unless it has a primary key defined.

If the server table has a PK or unique index this won't happen.

If anyone can provide information on how to change ODBC linked tables
in code, while maintaining the primary key, I'd appreciate it.

Are you using DSNless connections? If not I have never had any problems
just changing the DSN and not even touching the Access app.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Jun 27 '08 #5

Rick Brandt

Rich P wrote:

Hi Bruce,

Relinking ODBC tables in code is kind of a pain. This is a downside
of ODBC. If the tables were purely static then ODBC is an easy
solution, but whenever you need to make something even just a little
bit mroe dynamic - ODBC becomes a real hassel.

Please elaborate. I never have any difficulty changing ODBC links. Loop
through the objects, set the connect property. What is difficult about
that?

ADO is a much easier
option to go with as far as using Code for manipulating data from Sql
Server Tables. With ODBC you have to use a lot of API code.

API code? No idea what you mean here.

With ADO
you just make a reference to the Microsoft ActiveX DataObjects
library (2.5 or higher) and you can find tons of sample of code on
the Net.

Or just set the connect property with ODBC. Doesn't even require a
reference.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Jun 27 '08 #6

Rich P

I was actually thinking DSN - you need api code to change the dsn from
VBA. From the days when I used ODBC tables I undid the dsn then
reconnected the dsn. Can't remember what the deal was because I
switched over to ADO for dealing with sql server tables on the backend
several years ago (mostly for ADP's).

Of the advantages of ADO over ODBC - mostly it is just more flexible and
it is also a disconnected system where ODBC has a continuous connection.
Although, you can certainly run pretty much all the queries through ODBC
that you can run through ADO.

Another feature of ADO is that if someone decides to step up to ADO.Net
the transition is a little easier if the person has experience with
classic ADO (the ADO for Access) because the fundamentals are similar -
Just ADO.Net extends classic ADO about 1000+%

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Jun 27 '08 #7

brucedodds

I found out what the problem was. I was working with a test SQL Server
database that I thought was a clone of production. However, none of
the indexes were propagated to the test tables. Doh! Hence the
missing primary keys.

Thanks for the air code, which works perfectly.

On Jun 23, 1:41*pm, lyle fairfield <lyle.fairfi...@gmail.comwrote:

The primary key is in the SQL database. It cannot be "lost" through
relinking. You are not deleting the table. You are deleting the
TableDef whch lives in the front end and provides the connection with
the SQL Table.

Fast air code (on my way out)

Private Sub lODBCConnection()
Dim tdf As DAO.TableDef
With DBEngine(0)(0)
With .TableDefs
.Delete "FFDBAAccounts"
.Refresh
End With
Set tdf = .CreateTableDef("FFDBAAccounts")
With tdf
.SourceTableName = "FFDBAAccounts"
.Connect = "ODBC" _
& ";Driver={Sql Server}" _
& ";Server=server_name" _
& ";Database=database_name" _
& ";PWD=pass_word" _
& ";UID=user_id"
End With
With .TableDefs
.Append tdf
.Refresh
End With
End With
End Sub

On Jun 23, 10:53*am, brucedo...@comcast.net wrote:

I've inherited an A2003 application with linked SQL Server 2000 tables
in the back end, using the Microsoft SQL Server ODBC driver.

We've set up a test SQL Server database. *I'd like to automate the
process of relinking the application to test/prod.

I've found some code examples here of relinking ODBC back ends to
different databases, but they involve deleting the table before
relinking. *My concern is that the primary key information for the
tables will be lost when the table is deleted. *A linked ODBC table
can't be updated unless it has a primary key defined.

If anyone can provide information on how to change ODBC linked tables
in code, while maintaining the primary key, I'd appreciate it.

TIA

Bruce- Hide quoted text -

- Show quoted text -

Jun 27 '08 #8

brucedodds

On Jun 23, 7:30*pm, "Rick Brandt" <rickbran...@hotmail.comwrote:

brucedo...@comcast.net wrote:

I've inherited an A2003 application with linked SQL Server 2000 tables
in the back end, using the Microsoft SQL Server ODBC driver.

We've set up a test SQL Server database. *I'd like to automate the
process of relinking the application to test/prod.

I've found some code examples here of relinking ODBC back ends to
different databases, but they involve deleting the table before
relinking. *My concern is that the primary key information for the
tables will be lost when the table is deleted. *A linked ODBC table
can't be updated unless it has a primary key defined.

If *the server table has a PK or unique index this won't happen.

You're right - see note above.

Thanks for the response.

>

If anyone can provide information on how to change ODBC linked tables
in code, while maintaining the primary key, I'd appreciate it.

Are you using DSNless connections? *If not I have never had any problems
just changing the DSN and not even touching the Access app.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt * at * Hunter * dot * com

Jun 27 '08 #9