Results 1 to 6 of 6
  1. #1
    D-Train is offline Novice
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    12

    Need help Migrating from Access 365 to SQL Server 2019 Express

    Hi guys, first post here. I hope someone can help. A little background info first: I'm actually running Windows 11 for the past couple of months but so far haven't really noticed much difference from Windows 10, everything seems to work just fine. The computer is a Dell laptop with a fast I7 processor, 16GB RAM, 1TB SSD with 327GB free.

    I'm 60 years old and was an IT guy for over 20 years, Network Administrator, CNE (anyone remember Novell Netware? LOL) and I'm A+ certified, etc. so I'm pretty computer savvy but don't know squat about using SQL Server. My Access database was created from the Assets Manager sample database and has been fine for my purposes. I had a little help making some minor tweaks to it. I also passed a course in my local community college on basic VBA programming but I hate programming. So, all that being said....

    I've been using Access 365 for a couple of years now to manage a collection of mine. I just recently hit the 2GB database size limit and realize I need to move this over to SQL Server so I can keep growing my database.

    So yesterday I downloaded and installed SQL Server 2019 Express. Here are the results of my installation.

    Click image for larger version. 

Name:	SQL Server installed successfully.jpg 
Views:	54 
Size:	45.0 KB 
ID:	46889

    I've also downloaded and installed the SQL Server Migration Assistant. I've spent hours trying to figure out where to go from here. Should I move my Access data to SQL server using the Upsizing Wizard?

    Seeing as how I'm a novice at all this stuff I'm thinking I should probably keep using Access as my front end and use SQL Server as the back end to store the actual data. Is this my best bet? I've read many pages on Microsoft website trying to figure out how to use the SSMA but I'm not sure about how to make the connection to my SQL Server.

    When I try to connect to my SQL Server using the SSMA I get this:



    Click image for larger version. 

Name:	SQL Server Connection issues.jpg 
Views:	51 
Size:	30.3 KB 
ID:	46892

    Here's a shot of my SQL Server configuration manager status:

    Click image for larger version. 

Name:	SQL Server Configuration Manager status.jpg 
Views:	50 
Size:	60.7 KB 
ID:	46891

    I've looked into modifying my Windows Defender Firewall settings. If everything is running on the same machine do I need to open any TCP ports? If so how? I've tried turning off Windows Defender and pausing Avast but it doesn't seem to help.

    I've started the SSMA and can select the source database file of my Access database but can't select the target SQL Server database because it doesn't exist and I'm not sure the SQL Server is even visible to the SSMA because of blocked ports and I'm unable to get the Server Agents running. (Maybe that's my biggest clue?)

    So a step by step procedure on how to go about doing what my next steps are would be very helpful.

    Thanks a million in advance for any assistance.
    Attached Thumbnails Attached Thumbnails SQL Server install info 1.jpg  

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You'll also want to install SSMS if you didn't when you installed SQL server - to manage your SQL Server.

    I'd have a read here https://www.mssqltips.com/sqlservert...ocal-computer/

    It includes information about connections and set up.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    D-Train is offline Novice
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    12
    Hi Minty,
    this was helpful, I've managed to connect to my SQL server and run the SSMA. However I'm getting some errors during the migration process. For one thing I need to delete some unused indexes that are getting lots of errors during migration. Access help says:
    "Delete an index

    If you find that an index becomes unnecessary or is having too great an impact on performance, you can delete it. When you delete an index, you remove only the index and not the field or fields on which it is built.

    1. In the Navigation Pane, right-click the name of the table that you want to delete the index in, and then click Design View on the shortcut menu.
    2. On the Design tab, in the Show/Hide group, click Indexes."


      I can't figure this out, though it's probably very simple. In my navigation pane I have no tables listed, only shortcuts, forms, queries, reports, like this:
      Click image for larger version. 

Name:	Assets navigation.jpg 
Views:	55 
Size:	27.0 KB 
ID:	46898

      Everything I right click on and select "Design view" I don't see any menu options for "Design Tab" in "Show/Hide Group". Deleting a lot of the extraneous indexes will cause my Migration to run a lot more smoothly. Here is a shot of my migration report:

      Click image for larger version. 

Name:	SSMA Report1.jpg 
Views:	57 
Size:	76.0 KB 
ID:	46899

      I also read on one of Microsoft's webpages that SQL Server cannot import an "attachment" column from a table because it does not support attachments. What?!?!? This is an assets database which contains a very import column for attaching a photo of the asset. How will I get around this limitation?

      Sorry for all of the questions but I'm getting closer with your help.
      Thanks!!

  4. #4
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Attachments are a unique "feature" in access and don't scale well to any other large scale relational database unfortunately.

    What you should do is store the photo on a file server, and then store the picture file name in the database.
    If you do this with a well thought out file structure you can build the path to the photo easily and if you ever move them it should just be one path to update in one table in your database.

    Indexes : with a table in design view there is an "Indexes" button on the toolbar - click it!
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    keviny04 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Apr 2015
    Posts
    128
    SQL Server supports image data type, but look what it says here: "IMPORTANT! ntext, text, and image data types will be removed in a future version of SQL Server. Avoid using these data types in new development work."

    I've never used embedded image data in Access nor SQL Server. Like Minty suggested, I put paths of images into my database instead. Maybe the OP needs embedded images for certain needs, such as security (?).

  6. #6
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,

    * first of all: if you use SQL server for personal databases: don't bother with SQL express. Just download and use the free developers version. For reporting and integration packages (which are great when you migrate the tables from Access to SQL server) you can freely download Visual Studio 2022 - community version. In the developers version you have the job agent, so you can schedule your database and log backups without having to write scripts for that. Important because if you're using a full recovery model and don't take regular log backups, you're risking that the log file will blow up and flood the hard disk.
    * never ever use an upgrade wizard: more trouble than it's worth. Just recreate your tables on the SQL server (use the management studio). If you have groups of tables (contact tables, Sales tables, ...) consider creating a schema per group and create the tables in the correct schema. By default all tables are created in the dbo schema.
    * be aware that the SQL engine works with indexes in another way than Access engine. For example: in SQL server you construct the indexes using not only index fields, but also add included columns. A great help to determine which indexes you need is looking at the query plans. Don't create too much indexes: if an index needs to be more updated than it is used in a search it slows your database down instead of helping it.
    * When recreating your tables also look at the text fields. If you're only using English texts, you can stick with varchars (smaller and faster) in stead of using nvarchars.
    * before you install the server: consider which security model you want to use: integrated or SQL Server or allow a mix of both.
    * if you want to store pictures use a varbinary(max) field. You can use POWERSHELL to view the pictures stored there.

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

Similar Threads

  1. Replies: 4
    Last Post: 09-13-2020, 01:36 PM
  2. Linking access to sql server express
    By lrz in forum Access
    Replies: 1
    Last Post: 11-24-2018, 02:51 AM
  3. Replies: 3
    Last Post: 07-16-2014, 10:44 AM
  4. Replies: 1
    Last Post: 04-21-2011, 07:12 PM
  5. Convert Access 07 ADO code to SQL Server Express 2008
    By jrdnoland in forum Programming
    Replies: 8
    Last Post: 04-14-2010, 05:57 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