Steve,
Good question. After struggling with the .adp for 2 days I have determined
it will be too much of an uphill battle for this application. I have
decided to go with .mdb with linked tables. The main issue we are having is
performance with the shared .mdb so I thought .adp would be the best
solution.
Performance problems with MDB front-ends to MS SQL Server are rarely a result
of the fact that the front-end is an MDB. The most common cause of
performance problems is a lack of good C/S design such that large result sets
are repeatedly returned from the server.
Now the problem I am having is that 2 (of about 60) of the tables are not
uploading. Unfortunately the wizard errors don't give me much information.
Any ideas what could cause this?
Using an upsizing wizard is a pretty unreliable way of migrating tables to SQL
Server. You almost allways want to create the table definitions yourself, so
you can decide what indexes to have, whether to add a timestamp column, etc.
After that, you use append queries to upload the data. This is not a problem
with ADP vs MDB.
If the number of tables is large, you may want to write your own program to
analyze the schema of your MDB back-end and generate a provisional script for
generating the tables on the server, then hand-tweak the script before running
it on the server. Note - first script building the tables, then populate
them, then add indexes/keys, then create relations.
When populating tables, you may want to use an Access program to create a
temporary copy of each table on the server without an IDENTITY column, upload
the data to the copy, then execute a pass-through query to append the data
from the temporary copy to the destination table with identity insert turned
on.
I know that all sounds kind of daunting, but it's the most reliable way to do
it.
