Results 1 to 10 of 10
  1. #1
    VSCurtis is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2014
    Posts
    65

    Upsize To SQL Server and Link Tables To Existing Form

    Hi Guys,



    I'm about ready to upsize my Access database to SQL Server because I'm pushing the Access size limit. My database is 1.83 GB and I still have a ton of data to enter. I'm wondering how difficult it will be to upsize and use my existing Access data entry form by linking the tables. My form uses DAO to edit the data and update and delete existing records. The database contains multiple BLOB images per record and each record is blank except for a temporary key that will be updated as I enter data. I did it this way because I needed to know how big the database was going to get after the BLOB data was added, that way I could have an idea how much wiggle room I would have. The database may decrease in size as duplicate images are deleted. But in the end Access isn't going to be able to handle it. What changes will be required in my code to accomplish this? This is the first time I've ever tried this so I'm not sure what I'm getting into. Any assistance you can provide is appreciated.

  2. #2
    VSCurtis is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2014
    Posts
    65

    Using Microsoft SQL Server Migration Assistant for Access NO Data In SQL Server Tables

    Hi Guys,

    I'm having problems using the Microsoft SQL Server Migration Assistant for Access it will not connect to my instance of SQL Server Express 2014. I can connect just fine through the Management Console. I've had similar problems using the old upsize wizard but can't remember how to fix it. Sever Name is blank port is blank. Can't get past the initial log on screen for SQL Server. Windows Authentication is not being used on my machine. Any help you can provide is appreciated.

  3. #3
    VSCurtis is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2014
    Posts
    65

    Using Microsoft SQL Server Migration Assistant for Access NO Data In SQL Server Tables

    Hi Guys,

    I've been trying to use the Microsoft SQL Server Migration Assistant for Access to upsize my database and migrate my data. No data is being migrated into the SQL Server database. What's going on here? I miss the upsizing wizard. It was much simpler to use. Any help you can provide is a appreciated.

  4. #4
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    Is the SQL server 32-bit or 64-bit?

    Are you running the 32-bit or 64-bit versions of SSMA?

  5. #5
    VSCurtis is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2014
    Posts
    65
    64 Bit. My copy of Access 2016 is 64 Bit. I think I'm finally having some luck though. Looks like my data is getting imported. Had to go into every table and rename fields to remove spaces in the names. I don't think the tool likes spaces. Strange thing is that the tool reports that there is duplicate record in my primary table. Not possible. The table has a primary key field assigned. All keys are unique and Access has not complained about a duplicate key. Still trying to figure that one out. Have had to do a lot of clean up and fixing code to account for renaming all the fields. Have to do this upsize because I'm getting close to the Access 2 GB limit and still have a lot of data to enter. The database contains a lot of BLOB data.

  6. #6
    VSCurtis is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2014
    Posts
    65
    I just used the migration tool and told it to link my tables so I could see what was in my tables from Access and NONE of my BLOB data is present in ANY of my tables. What's going on here? I take it the migration tool can't handle BLOBs? Any help you can provide is appreciated.

  7. #7
    VSCurtis is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2014
    Posts
    65
    I just ran the migration tool again and it clearly showed that there was data in my primary table. My temp keys all appeared to be there as well as my BLOB data. I don't understand what is going on.

  8. #8
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    You will probably have issues with the BLOB data.

    If it we my project, I would not use the SSMA to import the BOB Data. I would create my own routine to export the BLOB data from Access to disk. Then import it into the SQL database.

  9. #9
    VSCurtis is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2014
    Posts
    65
    The BLOB data is on point. NO corruption. I have extracted the images multiple times, They didn't test their lovely little tool, plain and simple. Yeah I'm already thinking along those lines. So basically I have to start from square one again. Also trying the upsizing wizard in Access 2010. How lovely an upsizing tool that can't do the job. Just goes to show, if you want something done right, do it yourself. Thanks for the assist.

  10. #10
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    You're welcome.

    Glad we could assist.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 02-02-2015, 04:08 PM
  2. Two tables link on a form
    By chessico in forum Forms
    Replies: 6
    Last Post: 07-21-2013, 10:08 PM
  3. Link tables to server name, not mapped drive letter in Win 7
    By sleake in forum Import/Export Data
    Replies: 8
    Last Post: 11-16-2012, 10:48 AM
  4. Replies: 1
    Last Post: 07-17-2010, 08:29 PM
  5. How do I link existing tables?
    By jsbdiver in forum Programming
    Replies: 0
    Last Post: 05-24-2010, 02:53 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums