|
|
Posted by Neil on 10/29/05 23:53
OK, point well taken. I don't see any workaround, though, since I'm using
ODBC linked tables. I can't use a pass-through query and pass the table name
as a parameter, because pass-throughs return read-only sets. And a view
wouldn't be able to return just the records for that machine.
Here's an idea. What if there were one table, but a series of views, with
each view returning records for a particular unique ID. When the app is
opened, it's assigned an available ID and its link is set to the view that
returns records for that ID. Still kind of a kludge, but perhaps a little
better.
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns96FED3AD11D99Yazorman@127.0.0.1...
> Neil (nospam@nospam.net) writes:
>> I have an Access 2000 MDB with ODBC linked tables to a SQL Server 7 back
>> end. I currently have a selections table in the front end file which the
>> users use to make selections of records. The table has two fields --
>> primary key (which matches primary key of main, SQL Server table), and a
>> boolean field. The table is linked to the main table in a heterogeneous
>> inner join.
>>
>> I'm looking to move the table to the back end, while still giving each
>> machine a unique set of selections. Using one large table with machine
>> name as part of the primary key actually slows things down. So I'm
>> considering using a series of tables, where each machine has its own
>> table in the back end for selections. The machine name would be
>> incorporated in the particular selections table name, and the front end
>> link would be modified on the fly when the database is opened to point
>> to that machine's back end selections table.
>>...
>> Anyone see any problems with this approach, specifically creating the
>> table on the fly and then immediately using it, as well as having that
>> many little tables running around? Thanks for any input!
>
> Yes, I see problems. Simply don't go there. This is not the way you use a
> relational database. Make that machine name part of the PK in the single
> table. If having one single table, slows things down, investigate why
> instead of resorting to kludges.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
>
Navigation:
[Reply to this message]
|