Re: Access2k upsizing to SQL2k Automation

Yep, no primary keys. Once I manually set the primary keys on two of the
columns just like the MDB it behaved correctly.

At this time it looks like I will have to manually alter the TABLE CREATE
statements of the DTS/Import to include primary key on selected columns. I'm
surprised it doesn't do this automatically or have an option like when
importing from another sql server. Or did I miss something? In any case, this
is very good progress for me and I appreciate all your help.


"Douglas J. Steele" wrote:

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)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Steve" <Steve@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:EB66ABC3-008E-4FEB-938E-A9350FE9946A@xxxxxxxxxxxxxxxx
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" wrote:

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)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Steve" <Steve@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:CF6B28DD-6260-47FA-AD44-291F82E29AD9@xxxxxxxxxxxxxxxx
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,







.

Relevant Pages

  • Re: Access2k upsizing to SQL2k Automation
    ... I used DTS/Import on an MDB. ... correctly against the SQL database. ... Upsize Wizard only marks some of the columns 'null'. ...
    (microsoft.public.access.conversion)
  • Re: interview questions
    ... the most important thing is that they understand database DESIGN. ... There is no such thing as multiple primary keys in a ... Should an Autonumber field be used as a Primary Key? ... Normalization is a methodology or process for removing as much redundant ...
    (microsoft.public.access.tablesdbdesign)
  • Re: interview questions
    ... the most important thing is that they understand database DESIGN. ... There is no such thing as multiple primary keys in a ... Should an Autonumber field be used as a Primary Key? ... Normalization is a methodology or process for removing as much redundant ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Dataset.merge method
    ... and Deleted are matched on primary keys. ... > from my database with another one that is filled with values from my active ... > My messed up display name ... > AGoodLastName ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Updating the SQL key value
    ... Auto-numbered synthetic primary keys are the root of all evil. ... usually indicate a lazy approach to database design. ... What if your database must be upgraded and the logic changes? ... I don't get it why you think of that as 'the root of all evil'. ...
    (comp.lang.php)