Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164

    Unanswered: Upsizing from Access

    Hi every one.
    I'm in trouble.
    I am trying to upsize my Access db to sql server and I'm facing a lot of problems;
    for example the autonumbers don't work and when I try to change the data field type to uniquidentifier it won't work... and there are also other problems.

    Do you think, according to your experience,that is it worth it to re build the db from scratch?

  2. #2
    Join Date
    Jan 2003
    Location
    México City
    Posts
    31

    Upsizing...

    Maybe if you feel your model desing is too poor or when you've planning a mayor version upgrade, because recoding time are too long.
    Also if you want to use the new Access project type (without Jet), because you have to change a lot of db access functions, but again it means recoding.
    We found some problems with identity duplicates (must ran DBCC to fix it) after some Access97 malfunctions (sorry, I'm not the programmer and I ran out from that company so I'm not sure about Service Packs and new Access versions).
    IMHO, if you have code try to use the same model until you stabilize again your application and then you change it in a smoother way.

  3. #3
    Join Date
    Oct 2002
    Posts
    369

    Re: Upsizing from Access

    RE:
    Hi every one. I'm in trouble.
    Q1 I am trying to upsize my Access db to sql server and I'm facing a lot of problems;
    Q2 for example the autonumbers don't work and when I try to change the data field type to uniquidentifier it won't work... and there are also other problems.
    Q3 Do you think, according to your experience,that is it worth it to re build the db from scratch?
    A1 You may wish to consider identifying various types of issues and developing workable strategies for each type.

    A2 MS Access Autonumber columns are more similar to Identity Columns than uniquidentifier typed columns; consider using Int, BigInt or Numeric Identity Columns instead of uniquidentifier columns to replace the original Autonumber columns.

    A3 Sometimes it may be so, however if the logical design is otherwise valid and correct oftentimes not, (insufficient information to address the question reasonably.)

  4. #4
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164

    Re: Upsizing from Access

    __2__

    Q2-1: One of my major problems is that I can't import the quries from Access (mdb) into Views in Access project and this is while I have a lot of list boxes in my access database that are based on queries and a lot of code that runs the requry function.

    Q2-2: I was using the "Beginning Access 2000 VBA" by Smith and Sussman (programmer to programmer) and it was very usefull for me but I don't know if a lot of the code and examples will still be valid if I am connecting to SQL Server (e.g DoCmd Reqery and Recordset functions. If so do you know a good book that talks about VBA programming for Access projects?

    Thanks



    Originally posted by DBA
    A1 You may wish to consider identifying various types of issues and developing workable strategies for each type.

    A2 MS Access Autonumber columns are more similar to Identity Columns than uniquidentifier typed columns; consider using Int, BigInt or Numeric Identity Columns instead of uniquidentifier columns to replace the original Autonumber columns.

    A3 Sometimes it may be so, however if the logical design is otherwise valid and correct oftentimes not, (insufficient information to address the question reasonably.)

  5. #5
    Join Date
    Jan 2003
    Posts
    1
    Hi everyone

    I am also facing a problem of upsizing a MSAccess database to MSSQL Server 7. The problem is the re-coding workload is too high (e.g. # -> ', query -> view/stored procedure, etc.). I would like to know if I can use another connection string so that I am no need to re-code but still can access MSSQL database? I am no need to have the best performance, at least in this stage, what I want to do is transfer all the data as well as relations from MSAccess to MSSQL Server and all queries which is written for MSAccess can be re-used. Any idea?

  6. #6
    Join Date
    Oct 2002
    Posts
    369

    Re: Upsizing from Access

    RE:
    __2__

    Q2-1: One of my major problems is that I can't import the quries from Access (mdb) into Views in Access project and this is while I have a lot of list boxes in my access database that are based on queries and a lot of code that runs the requry function.

    Q2-2: I was using the "Beginning Access 2000 VBA" by Smith and Sussman (programmer to programmer) and it was very usefull for me but I don't know if a lot of the code and examples will still be valid if I am connecting to SQL Server (e.g DoCmd Reqery and Recordset functions. If so do you know a good book that talks about VBA programming for Access projects?

    Thanks
    A2-1 One strategy may be to build views (using Enterprise Manager for example) that produce result sets similar to those MS Access Queries that cannot be imported. (The resulting views may then be linked in a manner similar to linking tables).

    A2-2 I have no particular suggestions for texts on VBA programming for Access projects; however, posting specific VBA development questions to an Access / VBA usnet may be of help. See: http://dbforums.com/f219/ or

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •