Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Jan 2002
    Location
    Rochester, Kent, United Kingdom
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Upgrade backend to SQL (Access xp)

    Hi Guys
    Whats the best way to convert an XP database to SQL without using ODBC
    Any Ideas?

  2. #2
    2 Star Lounger
    Join Date
    Mar 2005
    Location
    Anderson, Indiana, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Upgrade backend to SQL (Access xp)

    I know it is probably more than you asked for but, it could help.

    I cannot think of any way not to use ODBC. I am assuming that you want to populate the new tables you will be creating in SQL with the data in the Access XP database. I guess you could copy/paste the information into SQL but would not recommend it.

    If you do use copy/paste, If there are many records, you could run into trouble. The clipboard can only copy, if I remember correctly, 65,536 records at a time. In addition, SQL will only display 10,000 records at a time, in a table, so if you paste more than 10,000 at one time I am not sure but I thought I read some where it will only take the first 10,000. I have never done the copy/paste into SQL.

    As far as actually creating the SQL back end I draw and write everthing down by hand. I use several 11x17 sheets of paper with six pre drawn boxes with lines for this process. The boxes divide the sheet into equal sections. I then laminated the sheets of paper and used them like a dry erase board. This is the example I posted. When I am finished with them, I can simply wipe them off and put them in a magnetic sleeve I have on the file cabinet and re-use when needed.

    I then use Enterprise Manager to create the tables in the SQL server.

    After I created, a SQL back end, I then created an Access Data Project (.ADP) from the SQL tables, then linked that file using ODBC into the old .mdb Access database. Then I used append queries to populate the new tables.

    Once that was completed, I used the .ADP file as the front end and built all my views, forms, and reports for the end user in that file. All stored procedures and functions I always created in Enterprise Manager.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Upgrade backend to SQL (Access xp)

    Are you aware of the SQL Server Upsizing Wizard that comes with Access? The 2002 version does a pretty decent job of converting an Access database as a back-end. And ODBC is about as reliable as any other technology out there, so why are you trying to avoid using it?

    See also my <post#=508,324>post 508,324</post#> to a thread with a similar theme.
    Wendell

Posting Permissions

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