Post reply

Database Migration from Access97 to SQL Server 7.0

  • June 28, 2005 at 8:30 pm

    #76361

    I have problem migrating the database from Microsoft Access 97 to Microsoft SQL Server 7.0 here.

    If I use the import method using the Enterprise Manager, the Primary Key of the table would not be imported.

    If I use the upsizing wizard 97 of Microsoft Access, the contraints (allow zero length) wont be upsized.

    As I'm currently using the second method, the upsizing wizard 97, so I need to manually insert the constraint using the SQL statement in Query Analyzer. The SQL statement is as below.

    ALTER TABLE Employee CHECK CONSTRAINT EmployeeId=''

    However, I got a problem here, the alter statement cannot be executed when there's record in the database.

    So would that be any solution to insert constraint into a table which already consist of record?

    Thanks to any who can help. Thanks a lot. I would much appreciate it.

     

    Regards,

    lingming

  • June 29, 2005 at 12:18 pm

    #569500

    Hi Lingming,

    If you are intested, I can solve your problem.  My business is data conversion from anywhere to any where.

    Thanks a lot

    Srinivas

    732-887-6643

    klsrao@yahoo.com

  • June 30, 2005 at 2:16 am

    #569695

    Please do let me know if you know the solution. Please tell/show me how it works. Many many thanks.

  • June 30, 2005 at 3:07 am

    #569702

    Firstly the constraint that you are building would not "allow zero lengths", but would "only allow zero lengths", that is all fields would have to be empty strings.

    I think that all you need to do is allow nulls (in simple terms, an empty data field), but this should be the case with the tables created by the upsizing wizard.

    I usually have better results importing using the Enterprise Manager's Import, especially as Primary Keys are easy to add in the table designer.  You can also recreate relationships using a database diagram.

    When I first migrated Access 97 to SQL Server 7 it took me a few goes to get things right, so don't be shy of starting over.  And one quick caveat, use views or stored procedures on the server instead of local Access queries – if you are making Access do all the work your application can actually slow your application down!

     If you are thinking about upgrading then Access 2000/XP/2003 is much better at talking to SQL Server using the Projects (ADP files) rather that the traditional MDBs, but your application may need further modifications to run, as these use ADO rather than DOA, which are different methods to retrieve data in code.

    Hope this isn’t too confusing!

     

  • July 1, 2005 at 9:13 pm

    #570480

    I have a problem here. When I'm testing my program on a stand-a-lone PC, everything goes fine. However, when I try to package the program and install it in a workstation. An error message come out, saying that "[ODBC][Miscrosoft SQL Server]Timeout Expired!". Why is this so?

    The program is using VB6, MSDE 7.0 and using ODBC connection. So far, all other PCs are OK. Connection between the client PC and the server is OK as well. I don't know why this error message coming out when I'm trying to execute one of the page.

    Anyone who knows the solution for it, please do let me know.

    Thanks and regards

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply