Change Link from SQLServer to Access (1 Viewer)

[AnnaFoot]

Hello,

I have a situation where a front-end works beautifully linked to a set of web based Sql Server tables. However the client now would like a read only copy to be downloaded on to the local pc, so that the information can be browsed while not online. The pc's do not have sqls server on them.

I have code that changes the connection between the live data and a development version in sql server on my machine. However it doesn't seem to want to change the fact it is connected using ODBC (a type 4 in MSysObjects) to connecting using Access (a type 6 on MSysObjects). If this can be changed i assume it is through tabledefs - but i can't figure out the command. I assume i have to somehow set is as a local linked table, and then tell it what it's connection is, i'll also have to be able to do the reverse - tell a local linked table it is about to become an ODBC linkd table.

The only othr option i can see is to have differnt links - and hence different named tables, which means duplicating all the queries depending on which set of tables i am using. I'd really like to avoid that....

Thanks in advance,

Anna

 

[llkhoutx]

You've almost got it right.

Create new SQL Server tables, link then to the FE, then append the Web SQL Server table rows to the newly created SQL Server tables. Autonumber indexing will cause problems. Make then Integer on the (new) SQL Size when appending. Then male that autonumbered column a primary key.

LInked tables, have a foreign name and a local name, both are specified in the MSysObjects table. The local name can be any name you want.

Merely uses the link SQL Server database tables required and change the table name as required. It's quite simple.

I use DNS connectctions and the TransferSpreadsheet instruction to doing the actual linking and change the names. If you don't link all the SQL Server tables, you'll have top maintain a list of the ForeignNames you want to link, specifyinh the new name in TRansferSpreadsheet command..