D
Dave Stoecker
Looking for general guidance on how to tackle a seemingly difficult
scenario, so any suggestions/insights are appreciated.
SQL 2000 Standard
Access 2000/2002
Omnis 7 datafile (many tables, lots of data) & ODBC driver
Access plays nice with the ODBC driver (when using MDB)
SQL Linked Server does not play nice - I can see the tables, but any query
of them errors out or is truncated after the first 2 or 3 records.
No outside manipulation of the line-of-business app that uses the Omnis file
is possible, and we are invested in it for at least a couple more years
I need to get data from many of the Omnis tables into SQL Server tables, and
to refresh the data somewhat regularly (not real-time) so I can tie it in
with some ADP projects I have.
The end goal is to make ADE(s) for around 10 users.
I would just use MDE's and link to whatever tables I need but query
performance and network traffic is *horrible* with the Omnis driver.
My initial analysis of the problem is that I will either have to write a
crapload of code to compare table contents and update accordingly, or have
craploads of code to drop and recreate SQL objects, keys, indexes etc. each
time the data is to be refreshed. Neither one sounds fun, but I suppose my
options are limited, if this is even doable.
If anyone out there has been in a similar situation and found a reasonably
workable solution, could you give an overview of your approach?
Pointers to any code examples would also be much appreciated.
Respectfully,
Dave Stoecker
scenario, so any suggestions/insights are appreciated.
SQL 2000 Standard
Access 2000/2002
Omnis 7 datafile (many tables, lots of data) & ODBC driver
Access plays nice with the ODBC driver (when using MDB)
SQL Linked Server does not play nice - I can see the tables, but any query
of them errors out or is truncated after the first 2 or 3 records.
No outside manipulation of the line-of-business app that uses the Omnis file
is possible, and we are invested in it for at least a couple more years
I need to get data from many of the Omnis tables into SQL Server tables, and
to refresh the data somewhat regularly (not real-time) so I can tie it in
with some ADP projects I have.
The end goal is to make ADE(s) for around 10 users.
I would just use MDE's and link to whatever tables I need but query
performance and network traffic is *horrible* with the Omnis driver.
My initial analysis of the problem is that I will either have to write a
crapload of code to compare table contents and update accordingly, or have
craploads of code to drop and recreate SQL objects, keys, indexes etc. each
time the data is to be refreshed. Neither one sounds fun, but I suppose my
options are limited, if this is even doable.
If anyone out there has been in a similar situation and found a reasonably
workable solution, could you give an overview of your approach?
Pointers to any code examples would also be much appreciated.
Respectfully,
Dave Stoecker