Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    MEMFBI is offline Novice
    Windows 10 Access 2003
    Join Date
    Apr 2020
    Posts
    28

    Move a access database that has been converted to a SQL database to a new SQL server

    Hello
    Months ago we converted an access database to a SQL database on a SQL server. Works great.
    We are moving to a new SQL server. What is the best way to move this database to the new SQL server. And then how do then point the frontend to new server?

    Running Accesss 2013
    New Server :




    • Windows Server 2016 Standard x64
    • Microsoft SQL Server 2017 Standard


    Thank you

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,671
    The easiest way to move the data is probably take a backup from the old system and attempt to restore it to the new one.
    If there are compatibility issues SQL is pretty good at letting you know.
    You may need to investigate a thing called compatibility level again SQL Server is good at telling you what won't work.

    Once the data is moved, assuming the same security is used, your front end should simply need the linked tables reconnecting to the new server.
    This can be achieved in numerous ways, but once re-linked and working simply distribute the new FE to all the users.

    Obviously this is really easy to test off line, you should already have backups you can test with.
    Your end users should expect a period of lock out during "change over" once you are ready to do it all for real.
    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
    MEMFBI is offline Novice
    Windows 10 Access 2003
    Join Date
    Apr 2020
    Posts
    28
    We have copied the database to the new server.
    Have created a "test" front end database to use.
    Select Linked Table Manager
    Select Add
    Enter the database name on the server, select SQL(Server/Azure)
    Select the new server
    The only tables listed appear to be sys tables, no database tables.
    Is this not the correct way to link to a new server?

    Thank you

  4. #4
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,671
    Hmmm - Is the new version database "running"
    You should be able to see it and query it in SSMS.

    When you created the link did you select the database within the server ? In the ODBC settings it is listed under "Change the default database to"
    Click image for larger version. 

Name:	SQL_ODBC1.png 
Views:	31 
Size:	46.3 KB 
ID:	44429
    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
    MEMFBI is offline Novice
    Windows 10 Access 2003
    Join Date
    Apr 2020
    Posts
    28
    OK, corrected the ODBC connection.
    But the table it brings in does not have current data. It is data that was in the database when it was converted to a SQL database.
    So in the test frontend I then linked to the existing database on the current server, it also only reflects data from when the database was converted to SQL
    I verified the map of the ODBC driver and it is what I used.
    Below is how the current database looks in access. Server fb-sql3 is the current server
    The table SSMA$Bid_Table$Local has current data
    I am confused.

  6. #6
    MEMFBI is offline Novice
    Windows 10 Access 2003
    Join Date
    Apr 2020
    Posts
    28
    Click image for larger version. 

Name:	Bid_Table.png 
Views:	27 
Size:	26.8 KB 
ID:	44445
    Not sure if the previous reply contained the screen shot (sorry)

  7. #7
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,671
    SSMA$Bid_Table$Local is a local table, not a SQL server table. (but you probably new that already)

    It sounds as if your database was still saving records locally somehow instead of to the linked tables.
    Very strange.

    SSMA$ tables, I think are created when you use the SQL Server Migration Assistant, I don't know their purpose, but I've only ever played with that not used it in anger.
    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 ↓↓

  8. #8
    MEMFBI is offline Novice
    Windows 10 Access 2003
    Join Date
    Apr 2020
    Posts
    28
    The SQL database was created using SQL Migration Assistant.
    So it appears that the data is not being stored on the server.
    Odd.

  9. #9
    NoellaG's Avatar
    NoellaG is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    723
    Hi, if you use the management studio on SQL server to look directly at the table content. Do you see all of the records?

  10. #10
    MEMFBI is offline Novice
    Windows 10 Access 2003
    Join Date
    Apr 2020
    Posts
    28
    Quote Originally Posted by NoellaG View Post
    Hi, if you use the management studio on SQL server to look directly at the table content. Do you see all of the records?
    After some investigation. It was realized that the server migration tool was actually run from the the sql server, not a local machine. So has been running as an access database all this time.

    Ran a test on a local machine and the database is now on the SQL server.

    Thanks

  11. #11
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,671
    Glad you worked it out.
    It can get confusing with multiple copies of things floating around.
    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 ↓↓

  12. #12
    MEMFBI is offline Novice
    Windows 10 Access 2003
    Join Date
    Apr 2020
    Posts
    28
    Hello
    Once again, I am trying to migrate an access database to a SQL server . I am runninng SSMA on my laptop. I am having an issue when it comes to linking the tables. I am receiving error that data migration was not performed becauseClick image for larger version. 

Name:	Migration Issue.jpg 
Views:	15 
Size:	80.7 KB 
ID:	44830 no objects were selected. I have attached screen shots of my selections. Appartently I am not selecting correctly.

  13. #13
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,671
    Difficult to say, but it looks like it was trying to "Sync" rather than import/migrate the data.
    I think you have to actively select which objects(tables) you want to migrate over.

    I really need to have another look at SSMA, but these days I nearly always start developing directly in SQL/Azure so never need to move things over.

    Have you tried simply Importing into Azure?
    If you right click on a database and select tasks, then import data, you can select an Access Database as a source.
    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 ↓↓

  14. #14
    MEMFBI is offline Novice
    Windows 10 Access 2003
    Join Date
    Apr 2020
    Posts
    28
    Thank you for that simple suggestion! How do I determine what the destination type would be to receive the data?

  15. #15
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,671
    Does it not make suggestions?

    The only ones to be really wary of are dates - Stick to the old fashioned DateTime or use SmallDatetime if times recorded to nano seconds aren't important.
    DateTime2 does not work very well with Access although it is supposed to be compatible with the newer drivers.

    Bit fields can be troublesome http://allenbrowne.com/NoYesNo.html read and digest.
    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 ↓↓

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

Similar Threads

  1. Migrating MS Access Database to SQL Server Database.
    By aligahk06 in forum SQL Server
    Replies: 5
    Last Post: 08-31-2020, 09:44 AM
  2. Replies: 8
    Last Post: 09-25-2019, 11:35 AM
  3. Replies: 1
    Last Post: 10-06-2017, 10:22 AM
  4. Replies: 2
    Last Post: 04-27-2016, 06:20 AM
  5. Replies: 11
    Last Post: 10-08-2012, 07:27 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 - Senior Forums