Access2k upsizing to SQL2k Automation

[Steve]

The problem: I have already upsized 162 mdb's and have about 800 to go. The
mdb's are all the same schema with different data. Upsize Wizard always
completes without errors. Is there a way to automate the Upsize where I only
have to supply the initial parameters from a script or vba code? I would like
to supply the names of the mdb's from a list and have the upsize run on each
mdb without interaction from me.

I've been looking in circles at WSH, VBA, ACWZTOOL.MDE, etc., and
haven't found anything definitive. A pointer in the right direction would be
greatly appreciated. Oh, and if there is a more appropriate newsgroup, I'll
be glad to ask there (if I knew what it was; first post and all that).

Thanks,

 

[Douglas J. Steele]

While it's doubtlessly possible to automate, given that you'd still have to
test each conversion to make sure that it worked properly, the actual time
spent on the conversion is relatively small. I also question why you would
have 962 databases with "all the same schema with different data". You might
want to reconsider your design.

If you are simply trying to get data from Jet databases into SQL Server,
why not look into SQL Server's DTS (Data Transformation Services)?

(And no, this isn't your "first post and all that": you posted exactly the
same thing in microsoft.public.access.replication yesterday)

 

[Steve]

Okay, I used DTS/Import on an MDB. The ASP/WEB side seems to operate
correctly against the SQL database. But, on the application side, I can't
update the SQL database. It reports that the database is readonly. The ODBC
dsn hasn't changed and I verified it's connection to the SQL database. I've
checked permissions and they are the same as when I use Access Upsizing
Wizard. The only difference looks like all columns are marked 'null' where
Upsize Wizard only marks some of the columns 'null'.

Is this a common problem when using DTS on MDB's? Is there a comprehensive
way for me to compare a DTS/Imported database to an Access Upsized one?

Thanks,

 

[Douglas J. Steele]

Do the tables in SQL Server have primary keys defined (or at least unique
indexes)? That's mandatory in order for the tables to updatable.

You might try reposting to microsoft.public.access.sqlupsizing, although I'm
not sure you can get to it through Microsoft's web interface (you might have
to go to http://groups.google.com instead)