Re: Changing linked SQL Server tables in code

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...@xxxxxxxxx> wrote:
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...@xxxxxxxxxxx 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 -

.

Relevant Pages

  • Re: Re-Seed in SQL Server
    ... Add a new field to the table that holds the Primary Key. ... or something like that and make it an Autonumber field and a Primary Key. ... If the table is already on the SQL Server you could try the below. ... that your Autonumber field in the Access Database has gotten corrupted. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Data access perfomance
    ... Hi, thanks I kind reduce the time inserting into the database, the problem ... was that I had a primary key on the table, ... >> I can't discuss Oracle, but for SQL Server, the following measures ...
    (microsoft.public.data.oledb)
  • Re: Identity crisis: GUID VS. Range
    ... I'm using SQL Server automatic identity ranges. ... the primary key then the index can grow a lot. ... central Sql Server 2005 database. ... Assign ranges of primary keys in each subscriber in order to assign ...
    (microsoft.public.sqlserver.replication)
  • Re: How to list tables with Primary keys
    ... We imported a bunch of tables from a database and realized that the ... Also, for future reference, is there a way to include the primary key ... SQL Server index performance ... SQL Server - optimization:index performance ...
    (comp.databases.ms-sqlserver)
  • Re: MS Access DAO -> ADO.NET Migration
    ... William Vaughn ... Microsoft MVP ... Hitchhiker's Guide to Visual Studio and SQL Server ... My migration app works building a SSCE database file with imported data ...
    (microsoft.public.dotnet.framework.adonet)
Loading