Re: Changing linked SQL Server tables in code

brucedodds@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 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


.

Relevant Pages

  • VBA Microsoft Access and SQL Server
    ... I am currently working on a project with Power Point. ... into a Microsoft SQL Server database. ... information from the Microsoft SQL Server database and re-populate a Power ...
    (microsoft.public.office.developer.vba)
  • Re: Need ODBCDirect-ion
    ... Just as a side note, ODBC, or so called "jet" direct is simply a way to ... You need experience with sql server. ... "Upsizing to Microsoft SQL Server" White Paper Available in Download ... Choosing Database Tools White Paper Available in Download Cente ...
    (microsoft.public.access.modulesdaovba)
  • Re: network error
    ... the back-end properly connected with ODBC to the back-end. ... sure that you have enough record locks and that your connection is good. ... front end, and a Microsoft SQL Server database holding the tables, as the ... back end (SQL Server 2000, ...
    (microsoft.public.access.formscoding)
  • Re: pass through query
    ... I need to set/connect the ODBC property and ... specify the name of the SQL server database? ... In the ODBC Connect String property box press the build button to the ...
    (microsoft.public.access.queries)
  • Re: Using Hyperstar to get data out of UV
    ... Though I am solid in SQL Server, ... that will allow me to connect via ODBC. ... Hyperstar is already on our system. ... SQL Server database to Hyperstar? ...
    (comp.databases.pick)