Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    D-Train is offline Novice
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    12

    Migrating from Access 365 to SQL Server 2019 Express

    Hi guys, I hit the 2GB limit on my Access 365 database and am attempting to migrate everything into a new installation of SQL Server 2019 Express. I ran the SSMA utility and got a lot of the data migrated but have hit some snags. I'm a novice when it comes to using Access and SQL Server so go easy on me, though I'm very computer literate.



    For one thing I need to delete some unused indexes that are getting lots of errors during migration. Access help gives the following instructions for deleting unneeded indexes:

    "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 find the menu options they are talking about, though it's probably very simple. In my navigation pane I have no tables listed (other than the newly created SSMA tables), only shortcuts, forms, queries, reports, like this:


    3. Everything in the Navigation pane that I right click on, then select "Design view" I don't see any menu options like they say that say "Design Tab" in "Show/Hide Group". Here is also a shot of my migration report. You can see there are a bunch of indexes that are garbage, I have no idea where all the "new_new_new" BS came from?:



      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 to getting this working. I have some other questions but I'll ask those in a separate thread.
      Thanks!!


  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    As for attachments, export all embedded objects to folder location. Save filename in text field.
    https://docs.microsoft.com/en-us/off...ile-method-dao

    Design tab would be on the ribbon. It only shows when object is open in Design View.

    You must have tables somewhere since you are converting them to SQLServer. Was your Access db split already?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The SSMA objects are tables, yes?
    Right click, select design view. "Indexes" is on Table Design ribbon tab in Show/Hide group.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    D-Train is offline Novice
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    12
    Thanks Micron,
    I opened another backup copy of the database which still had my old forms and now I have found the appropriate menu and have deleted the indexes I don't need. I had already run the migration assistant which apparently imported the tables and renamed them to ssmaxxxx
    So I guess I need to rerun the migration assistant on the backup database that I just edited (deleted indexes from). I also notice that the version of the database that I ran the migration assistant one no longer has any of my attachments. I guess this leads back to the question I had about SQL Server not being able to import attachments, so it deleted the attachments from that column.
    I guess my biggest question is how to I open Access and tell it to use the newly migrated data files that now exist on SQL Server? I assume I need to edit my autoexec? Are there any other mappings I need to make or changes so that Access is my front end and opening tables on my SQL Server?
    I'm making some progress so thanks for putting up with all of my questions.

  5. #5
    D-Train is offline Novice
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    12
    Hi June,
    Thanks for your help. I'm really confused about the whole "attachments" thing. All of the attachments (pictures) are already on my C: drive in a subfolder under my "Pictures" library. Some records contain only 1 photo attachment, some might have as many as 6 or more. Since all of my pictures are already in one folder on my hard drive is this a piece of code that I just have to create once? I really suck at VBA programming and am a bit intimidated by this.

    There is also a 2nd folder associated with each photo here:
    C:\My name\AppData\Local\Microsoft\Windows\iNetCache\ACC 5330
    (the numerical part of the last subfolder starting with ACC changes, but I think this is just a temporary file and can be ignored, right?)
    Sorry if these are stupid questions.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    My experience with non-native Access tables in an Access db is limited to ODBC connections on corporate sql server type db's so I can't help you with migration issues. However, your posts are a bit confusing (but I'm easily confused). Either you have a file path in a table that will be used to retrieve an image, or you have images as attachments in an Access attachment field. Or you have both, in which case the fact that you have images on a drive folder means not much in terms of your migration issues. If you have records with attachment field in the table, and the field contains images, then that's likely the main reason you reached the 2GB limit. Not many experienced in Access would use an attachment field for this very reason. Instead, the path to the attachment is stored in the table in a text field, not the file in an attachment field. If you fixed that (assuming that's what you have) then you may not need to migrate at all.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Need a text field that has a value that can be used to construct image name and file path. Reivew https://www.accessforums.net/showthread.php?t=65686 and https://www.accessforums.net/showthread.php?t=73766

    If you don't have that field then need to create and populate. If each asset can have multiple pictures then normalized structure would have a dependent table where each image is a record.

    I don't know what that 'temporary' file is for. Ignore it until there is an issue.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    D-Train is offline Novice
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    12
    "If you have records with attachment field in the table, and the field contains images, then that's likely the main reason you reached the 2GB limit. The path to the attachment is stored in the table in a text field, not the file in an attachment field. If you fixed that (assuming that's what you have) then you may not need to migrate at all."

    Click image for larger version. 

Name:	mind blown.jpg 
Views:	26 
Size:	61.9 KB 
ID:	46907

    I think you just nailed it! That's EXACTLY why I've hit the 2GB limit, the images are stored inside the database so now I have to figure out how to fix this issue using you and June's pointers. Is there any way to somehow export the current info for each image into a table so I don't have to go through and reassign photos once I get the text field issue sorted out? If not I'm willing to go through all the hassle of reassigning each photo again. Though there's probably 3000+ images to reassign.

    I didn't design it this way. This database is one that Microsoft supplies with Access. If you open Access 365 and select File/New then select the Asset Tracking sample database you will see that it has an attachment field. Can you look at the code for that and see if that's exactly what Microsoft is doing in the database? Then tell me how to resolve it in simple steps. Heck I'll pay you to do it for me if I have to. This database is of vital importance to me. Thanks again for all of your help.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If the images are already in a folder then that part is accomplished. Now need to know if there is a field in table that corresponds to image name. Do images in table have same names as images in folder? Name of image in table can be extracted to a text field with an UPDATE action SQL. Or if there are multiple images in attachment field, use it to save names to a related table.

    First build a query that expands the attachment field to pull image names, like: SELECT ID, Att.FileName FROM tablename;

    Now that query can be source for UPDATE or INSERT SELECT or SELECT INTO actions.

    This will not include the folder path but as shown in other links, that can be dynamically constructed.

    Delete the Attachment field after all works satisfactory.
    Last edited by June7; 12-21-2021 at 12:38 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If you open Access 365 and select File/New then select the Asset Tracking sample database you will see that it has an attachment field.
    IMO, M$ allows certain things in Access in order to make it easier for the novice to use the product. Problem is, you go into those worm holes only to find you're some place you didn't want to be. I might be able to see what that template code does, but it wouldn't tell me if you're using the attachment field. Pretty easy to tell though:

    open your table and look at the attachment field(s). If you see what looks like a paper clip and a number (1), it contains 1 attachment. If you see (2), it contains 2 and so on. If you double click on the record it will open a dialog that allows you to add another attachment or open the one in the record. If you're curious, and the db is not split, copy the db, remove all attachment fields, compact and repair and see what your file size could be.

    IF IT IS A SPLIT DB: if you want to still do that, make sure you manage the links to the back end attachment table(s) in the back end that you've copied. You don't want to be deleting images from your production tables when doing this test.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    D-Train is offline Novice
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    12
    Hi Micro,
    Yes, I'm definitely using the attachment field. The form has the paper clip icon that, when you click it, allows you to see what photo(s) is/are attached and allows you to add more attachments. Interestingly, I ran the SSMA and now when I open that same database (the one I originally posted a screenshot of that shows tables all starting with ssmaxxxx) the attachments field is still there but all of the attachments are missing and clicking on the "add attachment" button no longer works. So it seems this copy of my database is now opening tables that are now on my SQL Server? I don't know how to tell.
    All I know is I did a "compact and repair" operation on this database and it is now 1.8GB in size, so now not much of a reduction in size.
    Thanks a million for all of your assistance.

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Did you read posts 7 and 9?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    All I know is I did a "compact and repair" operation on this database and it is now 1.8GB in size,
    I didn't say to do that on your db with the attachments. June7 has given you some guidance on how to "fix" the problem if you're going to stick with this db and not migrate to sql server.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  14. #14
    D-Train is offline Novice
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    12
    Yes June, I read those posts, but I gotta say I have no confidence in being able to do what you said to do. I really don't understand the concepts. I have done almost zero Access programming, that's why I'm using a Microsoft provided template so I don't have to write any code, so I really need SIMPLE instructions. Writing any kind of code is intimidating to me, and that is assuming I even know how/where to insert it. I'm really needing you to make it simpler for me please. Pretty please?

  15. #15
    D-Train is offline Novice
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    12
    Hi Micron, not to worry, I have several backups to work with. I'm just saying the copy of my database that I migrated is now missing the attachments, and it only reduced the size to 1.8GB. So I'm not sure what to make of that. If the attachments are now missing and it's still very large what does that tell you?

    I just opened an older version of the database from before I migrated, and deleted the attachment column from the form. Then did a compact and repair, then closed database, checked the size and it is still 2GB. Very strange, right? Are the attached images still in the database but just not showing on the form now or...?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 12-28-2021, 01:24 PM
  2. Replies: 4
    Last Post: 09-13-2020, 01:36 PM
  3. Linking access to sql server express
    By lrz in forum Access
    Replies: 1
    Last Post: 11-24-2018, 02:51 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