Looking for DAILY news and commentary? Join us on my other channel “Coffee With Steve” for daily videos where we discuss Technology, Software Development, Politics, Culture, and many other things. Coffee With Steve: th-cam.com/channels/eXAUvo5xxDY_b-lSknPC1A.html
In answer to Aditya's question, I found a way that seems to work. I used the regular ODBC window and created a File DSN and made sure it existed in a folder that the user has permissions to. I then relinked all tables using that file DSN. Then, by distributing this new front-end, the end user can access SQL Server tables. The trick is making sure the file DSN is located in a folder the user has permissions to.
I don't know how many times i missed to write a thank you note in comments on different videos because I must have been working on database, but i really appreciate your efforts and thankful to you from bottom of my heart that because of your help i have completed my first project that probably no less than any professional programmer. Now that I am going to share the database with other locations and there too you helped me!
Great video! I love following along with these mini projects and learning something different every time. Thank you for all your efforts in these tutorials.
Thank you for this useful video, explanations are clear. I just wanted to know why you choosed Windows Authentication, instead of SQL Server Authentication
This is absolutely a great walkthrough. Though I am getting the following - Synchronization Error Column already has a DEFAULT bound to it. Could not create constraint or index. See previous errors. Synchronization Error The name "N" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
Sir Thanks a lot. You are great. I had one quick questions. When we use Linked table when SQL Server is BE while Access is FE. How are we understanding the connection is done with DAO or ADO?
Great video - thanks for taking the time to publish it. I haven't migrated my database yet, but something that caught my eye in your video is that SSMA seems to automatically include a timestamp field. If I recall correctly, the Access Upsizing wizard did not (yes, its been a long time since I've upsized an Access DB :-) ). That is great functionality... if I'm not mistaken, that will avoid that pesky Write Conflict Error in SQL Backends stemming from Y/N (bit) fields in upsized databases that do not explicitly have Allow Nulls set to NO.
Hello Steve Thank you for the tutorial. My question is : what about If we have in our access database tables linked to sharepoint list, will the migration be possible in this case?
Thanks for this video Steve! it is very helpful. In my case It copied everything except 2 tables The error is “ Cloumn'To/Form' does not allow DBNull. value'”
Hi Steve, First of all: Thank you so much for youre great Videos. You're fantastic - really. I've got a question for you: We developed a database for about one year and everything worked fine until we split it up. Now we frequently get the runtime error 3048 "Cannot open any more databases" Wich is kind of anoying. Could migrate to SQL server solve our problem? Thank you so much
Hi, I need one advice. Currently I am using Microsoft Access for my projects. I am looking to learn MIcrosoft SQL Server. My question is that what other alternatives will work as a front end in place of microsoft access and what will be your advice in this case. Should I just migrate to SQL Server and keep using MS Access as a front end or will there be better option. Please consider the learning curve issue also in your advice as I am not from IT background.
Hi Steve Sorry about the very late comment. I need to know if I could give the front end to other users on different computers. I mean can this be multi users.
Hi I’m getting bit version error, I’ve installed the same version like office and the windows and still getting the bit version error, any idea how to fix it?
Excellent video. i am using a field to store an image file and then in a form i am loading the image by the field value. If i convert the access db to an SQL Server and use the Access application as front-end in another computer in the network will the images load ?
Thank you for this awesome video. I am having a problem with it though. Even though I specified to have the migration assistant link the tables for me, my SQL tables now have the prefix "dbo." at the beginning which causes them nit to be recognized by the queries and forms I have. Do you know how I can fix this to work like yours did?
Hi, I'm trying to migrate a AccessDB to SQL Server. I have a issue when open my Access application. I can see the tables on my SQL Server but, when I hit any button on my access app, show me and error. (Access Run Time ODBC 3151 Error, OBDC connection failed) I don't know how can I fix it. Thanks!!
Thanks for excellent video! I am migrating an Access D-Base to SQL 2014 express. Migration of backend went fine. But the Front End does not link to the backend on my test. Do I have to migrate the front end as well (maybe together with backend? ) Or do I need an OBDC driver to make the connection. ? Thanks!
Hi Steve, I enjoyed your video as always. I had an error and renamed my SQL tables back to the original Access table names and everything worked properly in Access; however when I tried to migrate it a second time, the migration tool could not find my tables. Can you explain what happened. When I brought in a backup copy of my Access it migrated properly. Thanks John
Do you know how to run this tool in command line ?. I have to run access to SQL server data migration for 500 customers all having there own access applications
when we use MS Access as the front-end for SQL Server, can we create DIAGRAM at SQL Server and use it, as replacement of Relationship at MS ACCESS. Or we have to create relationship at MS Access (the front-end)?
Love your videos! How do we use Windows Authentication to grant access to the SQL tables? Does something in the migration tool tell the front end what server and database to use or do you have to add this in the VB code somewhere? Or is just the linking of the tables? This is my first attempt, so please bear with me! Thanks!
+Diane Osborne Windows Authentication is typically set up through a Domain Controller when you're on a Domain network. SQL Server can be configured to be either Windows Authentication only, or mixed mode. Windows authentication is based on the windows login account that you use to access the PC, therefore the ability to access the SQL Server is managed on the SQL Server end with an integration of Active Directory. You have the option of adding linked tables which point to the server, database, and table when going through the Server Migration. Otherwise you will have to create the linked tables manually within your Access database. Linked tables are treated essentially like local tables by Access. You can query them and open them directly from within your front end to add, edit, or remove data.
How does the SSMA act, if you migrate to SQL and the Access source has already tables linked to, lets say a iseries DB400 Database ? Does it leave existing links as they are ?
Hi great video, if i enter new data in access after the migration will new data appear In SQL automatically or do I need to schedule a refresh etc? Thank you
Hi great vdo. 3 migration went well. Odbc connection ok. User happy. In the last 2 migration worked data transferred, linked but has odbc failure while opening at user pc. Ok on my pc. Is this bcos admin is on 32bit and user is on 64bit. Kindly adv as in our office we have combination of 32 and 64b. Thanks niaz
Hi Steve, this video was produced 2 years ago. Is SSMA still supported and used? If so, can I use it to migrated MS Access data into SQL server and use a different front end.
Connection to SQL Server failed. A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) .. why? please
It's very hard to say why but the error message is telling you what's gone wrong. Make sure your SQL server is up and running and you're connection string to it is correct.
Thank u so much Steve for this video. One question I have regarding relationships. Do they get migrated over as well or do I need to manual recreate the relationships in SQL after the migration? I plan to use SQL as the back end and Access as the front end.
I know this is over a year old- but good stuff- however when I down load I am only getting the 32 bit version. I have done the complete for v6 and v5.3 trying to find the 32 bit. Anyone else having an issue getting this? any suggestions?
Excellent. It works perfectly but when I distribute the newly-linked table to another user, they don't have the requisite ODBC connection. I'm trying to distribute a db which will automatically make the ODBC connection for the user. Any ideas? Thanks, Richard
Hi Carson Morton, So How did you distribute the new linked application to other users. How did you see the table connection string in VBA. Please advise
Thank you, Steve. I ran into road blocks with Access 2016 because my installation is Click to Run and would not allow me to install 2016 Access Runtime. This process worked wonderfully. My project would include 12 users in 12 different locations. Is there a way to pull that off using Access and Azure? Or do you have a better suggestion? Thought about using SharePoint Access Web App but since MS is killing that off I have to come up with a new solution. Thank you! Harry
I ran into the same roadblock. I have 32 bit Access on a 64 bit machine. The migration tool apparently needed some DAO classes from the Runtime so I followed the instructions and downloaded that but then the Runtime would not install because of "Office 16 Click-to-Run Extensibility Component 64-bit Registration " But of course, I could not uninstall the Click-To-Run because I can't find it among those programs listed in the windows control panel. If you find a way to migrate Access to Azure, please let me know!
Thank you for this video. I followed your procedure and migrated a small Access database to Sql server 2017. However I am not able to add new records to the database. The New option is grayed out. On watching your video again I see your New record option is grayed out too. Any suggestion why I am not able to insert data in the sql server database with the access front end. It is not a permission issue because I can insert data using SSMS into the same table. Thank you in advance.
Thanks for the great explanation of SQL Server Migration Assistant , Steve #ProgrammingMadeEZ -- nice to know that it can grab Access 97 data too -- what happens, though, if it is not properly structured? Will everything still go up? What if some tables are seemingly not connected (but should be)? I do a lot of 'fixing' to get data ready but Access can't even read 97 files after 2007 -- just wondering if the data will all move up and structural changes, relationships, and indexes should be done later ...? thanks
+LearnAccessByCrystal If the relationship or indexes aren't brought up to the SQL server, you can still add the key constraints and indexes later. Most of them will be imported though.
Thanks for the vidéo, Plz can you give us a link to download old version of ssma especially for 32bit (we use winXP and SQLserver2008R2), we remind you that microsoft's officiel website present only the last release of ssma. THANK YOJ VERY MUCH
+Josh Goldfarb You will just need to link the database tables manually to your front end. Check out my advanced Access 2013 series and I walk through how to do that there.
+Programming I have a query in SSMA and stuck with it for long time now. Could you please help me to sort it out. Below are the details. I am using SSMA to Access for a particular migration project to migrate all possible tables, views, linked tables, etc. I am using customized ConversionAndDataMigrationSample.xml, VariableValueFileSample.xml, ServersConnectionFileSample.xml files to provide all source, destination and variables to SSMA based on the requirement of my project. I executing SSMAforAccess.exe in cmd by passing the location of these xml files containing source, destination, variables details required to complete the migration. Migration works perfectly however i want to store a particular service account credentials in connection string to link my Access tables with SQL tables, this can be achieved by marking Linked Table property as "True" in Default project settings of SSMA GUI. It does work and stores the service account credentials in connection string when i directly create a project using SSMA GUI however whenever i try to achieve the same using xml files execution method, the connecting string in the linked tables generates without any service account credentials in it and which is an issue as per the requirements of the project. I am using the below node in ConversionAndDataMigration.xml schema XML file to map Linked Table property as "True" in Default project settings in XML. Also tried values like True, 1, yes, true, etc. I could be wrong here in choosing this node to store required user credentials in connection string. Could you please direct me to correct node or method to store service account credentials in connection string while migrating access data to SQL using CMD & XML files.
Thanks for the quick response. Is it possible for you to lead me to a person who can help in this scenario? I have posted this query on MSDN forum as well but didn't receive any response yet.
quick question please , how many users can access database take? and how many users can open simultaneously? I'm about to build a database and deploy it with 280 users is that possible? any recommended way to publish? thanks a lot :)
Bilal Kareem There is not hard limit to how many users can be using your FE to access the data on your BE, but realize system resources will play a big part in the user experience. 280 users on simultaneously would require you to be using at least a standard version of SQL server on a very beefy system. Lots of RAM, lots of CPU cores.
thank you for the quick replay, tools will be provided by the company , my concern is the number of the users because I never build a data for a 250+ users and I red some reports says only 256 users , but if will migrate my BE to SQL server will be unlimited right?
Looks like a great tool. Can't get the 32 (v6) or 64 bit (v7.4) versions to work, however. Frustrating. Any tips? "Access Object Collector error: Database Retrieving the COM class factory for component with CLSID {CD7791B9-43FD-42C5-AE42-8DD2811F0419} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)). This error may be a result of running SSMA as 64-bit application while having only 32-bit connectivity components installed or vice versa. You can run 32-bit SSMA application if you have 32-bit connectivity components or 64-bit SSMA application if you have 64-bit connectivity components, shortcut to both 32-bit and 64-bit SSMA can be found under the Programs menu. You can also consider updating your connectivity components from go.microsoft.com/fwlink/?LinkId=197502. An error occurred while loading database content."
Hey, I know this was 6 months ago. But I got this error too. Was able to fix it by downloading a Microsoft product by searching "Microsoft Access Database Engine 2010 Redistributable"
Thank you. SSMA did not work for me. I've gone back to your advanced programming videos, and I now have my back end on my company's SQL server, and linked to my front end. Now I'm working on distributing my front end.
I am running 2016, and we downloaded the latest version from the website...but we just couldn't get it to work. Migrating my data the long way worked just fine. I'm about to download the SSE Setup wizard now.
it is going fine with limited data ,but migrating a 100 thousand records it failed, any thoughts please. showing Data migration error , so all tables are rollbacked, so now i 've migrated 66 tables and 1 table which is having 100 thousand record it is still failing
+saurabh banerjee , further to add more , it has inserted records up to 140 thousands , rest 20 thousand records are not inserted , how to to overcome this issue
i am getting the TCP /IP issue [Datamigrator: Error] [7320/9] [2016-01-08 15:26:16]: The following error occurred during migration of the current batch: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) See the log for the detailed information. [Datamigrator: Error] [7320/9] [2016-01-08 15:26:16]: Exception: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) site: Void OnError(System.Data.SqlClient.SqlException, Boolean, System.Action`1[System.Action]) source: .Net SqlClient Data Provider error code: -2146232060 data: [HelpLink.ProdName] = [Microsoft SQL Server] data: [HelpLink.EvtSrc] = [MSSQLServer] data: [HelpLink.EvtID] = [10054] data: [HelpLink.BaseHelpUrl] = [go.microsoft.com/fwlink] data: [HelpLink.LinkId] = [20476] at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error) at System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync() at System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket() at System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer() at System.Data.SqlClient.TdsParserStateObject.TryReadByte(Byte& value) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlBulkCopy.RunParser(BulkCopySimpleResultSet bulkCopyHandler) at System.Data.SqlClient.SqlBulkCopy.SubmitUpdateBulkCommand(String TDSCommand) at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsync(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source) at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestContinuedAsync(BulkCopySimpleResultSet internalResults, CancellationToken cts, TaskCompletionSource`1 source) at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestAsync(CancellationToken cts, TaskCompletionSource`1 source) at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalAsync(CancellationToken ctoken) at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServerAsync(Int32 columnCount, CancellationToken ctoken) at System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader) at Microsoft.SSMA.Framework.Access.SqlServer.DataMigration.TableDataMigrator.ProcessNode(IDataMigrationContext context, DataMigrationState state, XNode node) [Datamigrator: Error] [7320/9] [2016-01-08 15:26:16]: Inner Exception #1: An existing connection was forcibly closed by the remote host site: source: error code: -2147467259
Hi Steve, I am exactly doing same way as you are doing, As its related to my work I am giving the server Name, Server Port:default Database: Name Its shows me error Create DATABASE Permissions denied in Database 'master' I dont understand why its creating in master. Any suggestions pls.
The database should not be called "Name". You should be using the new database name that you're migrating to or if it already exists, then you use that databases name. Otherwise the connection will default to the master database.
Hi Steve, I am very new to access coding. Thank You for your suggestion. It worked and I was able to Import the data to Sql server and got it linked. Exactly how you showed in the video. But My scenario is little different . I have a mail page PCC3000_FrontEnd.accdb And there exists a data base connection between 3 other accdb files. And I have't Imported/Linked them to sql. Now when I run the application at the its giving me an error msg Tables not reconnected:Invalid procedure call or argument. Run time error 5: Invalid Procedure call or arguments. Can you please advise how to fix the Issue and run my application smoothly.
Hi Steve, Thank You for the help. I have see the above video and converted the access objects tables in to sql tables using AccessSSMS 6.0 And the key were created by the tool. And the tables were linked automatically by the tool access ssms 6.0 Now if I open the tables in access its showing as True/False Instead of check box(Yes/No) And so the in code they are checking for yes/no or -1/0. But I have true or false in the columns. Do you think that's causing the Issue. Any advised how to fix those Conversion Steve. Please advise. Thank You for all the Help Steve !
Yes. That's why it's not a checkbox anymore but it's now a bit field. FWIW, SQL stores this as a 1 or 0. Access tables store it as a -1 or 0. I don't know that anyone fully understands why Microsoft decided Access needed to do this differently than all other RDBM's out there.
Because my Table has 85 columns, and I can't make structure manually.I found very fast and elegance decision Search - ( excel2mysql es ) - - very useful tool. Converts ACCESS, .xsls , xls , csv tables to SQL code for easy import in database.
I really need this information but I'm sure this video could easily fit into 5 minutes if you weren't so annoyingly verbose. Tons of unnecessary words and information rubbish - spelling url, informing about file size, etc.... I was going to subscribe but have changed my mind. You're respecting neither other people's time nor your own
Looking for DAILY news and commentary? Join us on my other channel “Coffee With Steve” for daily videos where we discuss Technology, Software Development, Politics, Culture, and many other things.
Coffee With Steve: th-cam.com/channels/eXAUvo5xxDY_b-lSknPC1A.html
In answer to Aditya's question, I found a way that seems to work. I used the regular ODBC window and created a File DSN and made sure it existed in a folder that the user has permissions to. I then relinked all tables using that file DSN. Then, by distributing this new front-end, the end user can access SQL Server tables. The trick is making sure the file DSN is located in a folder the user has permissions to.
I don't know how many times i missed to write a thank you note in comments on different videos because I must have been working on database, but i really appreciate your efforts and thankful to you from bottom of my heart that because of your help i have completed my first project that probably no less than any professional programmer. Now that I am going to share the database with other locations and there too you helped me!
Great video! I love following along with these mini projects and learning something different every time. Thank you for all your efforts in these tutorials.
Thank you for this useful video, explanations are clear. I just wanted to know why you choosed Windows Authentication, instead of SQL Server Authentication
I gave this video a thumbs up simply because you whistled the Muppet's tune and it was perfectly timed! Awesome.
You're a life saver. Thank you so much my friend. You saved me.
This is absolutely a great walkthrough. Though I am getting the following - Synchronization Error Column already has a DEFAULT bound to it.
Could not create constraint or index. See previous errors.
Synchronization Error The name "N" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
Thanks.
It's still there SSMA for Access v8.23 (November 2021)
SSMA 7.10 (Sept 10th 2018) is now available. I'm about to give this a try.
Sir Thanks a lot. You are great. I had one quick questions.
When we use Linked table when SQL Server is BE while Access is FE. How are we understanding the connection is done with DAO or ADO?
Great video - thanks for taking the time to publish it. I haven't migrated my database yet, but something that caught my eye in your video is that SSMA seems to automatically include a timestamp field. If I recall correctly, the Access Upsizing wizard did not (yes, its been a long time since I've upsized an Access DB :-) ). That is great functionality... if I'm not mistaken, that will avoid that pesky Write Conflict Error in SQL Backends stemming from Y/N (bit) fields in upsized databases that do not explicitly have Allow Nulls set to NO.
Yes it does. It used to be really annoying that it would do that and you'd have to go back and initialize all those values.
SSMA 9.2.0 fails to install for me on Windows 10.. rebooted, turned off antivirus etc. just hangs.
Hello Steve
Thank you for the tutorial.
My question is :
what about If we have in our access database tables linked to sharepoint list, will the migration be possible in this case?
Thank you so much, I enjoyed watching your video, and you're so funny with your explanation.
after I click add database, it cannot find my tables? it sees the database but the little checkbox in table is unchecked and no tables are showing?
Very nice ! Greetings and thanks from Bavaria !!
Thanks for this video Steve! it is very helpful. In my case It copied everything except 2 tables The error is “ Cloumn'To/Form' does not allow DBNull. value'”
Hi Steve, First of all: Thank you so much for youre great Videos. You're fantastic - really. I've got a question for you: We developed a database for about one year and everything worked fine until we split it up. Now we frequently get the runtime error 3048 "Cannot open any more databases" Wich is kind of anoying. Could migrate to SQL server solve our problem? Thank you so much
Getting error in 64 bit office as connection failes?kinldy assist
as always, very useful video, thank you
Great tool but it crashes on me when the data is migrated. Seems to be related to the mso.dll. Running on a 64bit system.
you are always awesome, keep going
Hi, I need one advice. Currently I am using Microsoft Access for my projects. I am looking to learn MIcrosoft SQL Server. My question is that what other alternatives will work as a front end in place of microsoft access and what will be your advice in this case. Should I just migrate to SQL Server and keep using MS Access as a front end or will there be better option. Please consider the learning curve issue also in your advice as I am not from IT background.
how you know its attachment that fails? u just assumed it by experience
Hi Steve
Sorry about the very late comment.
I need to know if I could give the front end to other users on different computers. I mean can this be multi users.
Hi I’m getting bit version error, I’ve installed the same version like office and the windows and still getting the bit version error, any idea how to fix it?
Excellent video. i am using a field to store an image file and then in a form i am loading the image by the field value. If i convert the access db to an SQL Server and use the Access application as front-end in another computer in the network will the images load ?
Thank you for this awesome video. I am having a problem with it though. Even though I specified to have the migration assistant link the tables for me, my SQL tables now have the prefix "dbo." at the beginning which causes them nit to be recognized by the queries and forms I have. Do you know how I can fix this to work like yours did?
So I tried but when I select my Access Databases it shows 0/0 tables in it in the SSMA - Why??
Hi, I'm trying to migrate a AccessDB to SQL Server. I have a issue when open my Access application. I can see the tables on my SQL Server but, when I hit any button on my access app, show me and error. (Access Run Time ODBC 3151 Error, OBDC connection failed) I don't know how can I fix it. Thanks!!
Thanks for excellent video! I am migrating an Access D-Base to SQL 2014 express. Migration of backend went fine. But the Front End does not link to the backend on my test. Do I have to migrate the front end as well (maybe together with backend? ) Or do I need an OBDC driver to make the connection. ? Thanks!
Rename ur linked table as it was before... Typically remove. Dbo
so where do i store my attachments then?
Hi Steve, I enjoyed your video as always. I had an error and renamed my SQL tables back to the original Access table names and everything worked properly in Access; however when I tried to migrate it a second time, the migration tool could not find my tables. Can you explain what happened. When I brought in a backup copy of my Access it migrated properly. Thanks John
Do you know how to run this tool in command line ?. I have to run access to SQL server data migration for 500 customers all having there own access applications
when we use MS Access as the front-end for SQL Server, can we create DIAGRAM at SQL Server and use it, as replacement of Relationship at MS ACCESS. Or we have to create relationship at MS Access (the front-end)?
Lovely stuff - thanks
Love your videos! How do we use Windows Authentication to grant access to the SQL tables? Does something in the migration tool tell the front end what server and database to use or do you have to add this in the VB code somewhere? Or is just the linking of the tables? This is my first attempt, so please bear with me! Thanks!
+Diane Osborne Windows Authentication is typically set up through a Domain Controller when you're on a Domain network. SQL Server can be configured to be either Windows Authentication only, or mixed mode. Windows authentication is based on the windows login account that you use to access the PC, therefore the ability to access the SQL Server is managed on the SQL Server end with an integration of Active Directory.
You have the option of adding linked tables which point to the server, database, and table when going through the Server Migration. Otherwise you will have to create the linked tables manually within your Access database.
Linked tables are treated essentially like local tables by Access. You can query them and open them directly from within your front end to add, edit, or remove data.
How does the SSMA act, if you migrate to SQL and the Access source has already tables linked to, lets say a iseries DB400 Database ? Does it leave existing links as they are ?
Hi great video, if i enter new data in access after the migration will new data appear In SQL automatically or do I need to schedule a refresh etc? Thank you
I thank you
Hi great vdo. 3 migration went well. Odbc connection ok. User happy.
In the last 2 migration worked data transferred, linked but has odbc failure while opening at user pc. Ok on my pc.
Is this bcos admin is on 32bit and user is on 64bit.
Kindly adv as in our office we have combination of 32 and 64b.
Thanks niaz
Another question please , how many users can access handle?
And how many users can login simultaneously? Thank you!
Bilal Kareem Theoretically unlimited, but the resources that are running everything will cause some sort of limitation.
Hi Steve, this video was produced 2 years ago. Is SSMA still supported and used? If so, can I use it to migrated MS Access data into SQL server and use a different front end.
Connection to SQL Server failed.
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) .. why? please
It's very hard to say why but the error message is telling you what's gone wrong. Make sure your SQL server is up and running and you're connection string to it is correct.
Great video
Thank u so much Steve for this video. One question I have regarding relationships. Do they get migrated over as well or do I need to manual recreate the relationships in SQL after the migration? I plan to use SQL as the back end and Access as the front end.
Kaylen did you get the solution for this. Did the relationships got migrated?
I know this is over a year old- but good stuff- however when I down load I am only getting the 32 bit version. I have done the complete for v6 and v5.3 trying to find the 32 bit. Anyone else having an issue getting this? any suggestions?
Excellent. It works perfectly but when I distribute the newly-linked table to another user, they don't have the requisite ODBC connection. I'm trying to distribute a db which will automatically make the ODBC connection for the user. Any ideas? Thanks, Richard
You would need to set the table connection strings in VBA, and not use DSN's.
Hi Carson Morton,
So How did you distribute the new linked application to other users.
How did you see the table connection string in VBA.
Please advise
Sir I create backend in access and front end in Vb.net now I want to convert backend to my sql so this method will work
Thank you very much
Thank you, Steve. I ran into road blocks with Access 2016 because my installation is Click to Run and would not allow me to install 2016 Access Runtime. This process worked wonderfully. My project would include 12 users in 12 different locations. Is there a way to pull that off using Access and Azure? Or do you have a better suggestion? Thought about using SharePoint Access Web App but since MS is killing that off I have to come up with a new solution. Thank you! Harry
I ran into the same roadblock. I have 32 bit Access on a 64 bit machine. The migration tool apparently needed some DAO classes from the Runtime so I followed the instructions and downloaded that but then the Runtime would not install because of "Office 16 Click-to-Run Extensibility Component 64-bit
Registration " But of course, I could not uninstall the Click-To-Run because I can't find it among those programs listed in the windows control panel. If you find a way to migrate Access to Azure, please let me know!
Thank you for this video. I followed your procedure and migrated a small Access database to Sql server 2017. However I am not able to add new records to the database. The New option is grayed out. On watching your video again I see your New record option is grayed out too. Any suggestion why I am not able to insert data in the sql server database with the access front end. It is not a permission issue because I can insert data using SSMS into the same table. Thank you in advance.
You need to add a primary key to the table.
that I can use to migrate from sql server to Db2?
Fantastic and very useful video
dear when we have relation between two tables in ms access after migrated did lost this relationship between these two tables inside sql server?
Thanks for the great explanation of SQL Server Migration Assistant , Steve #ProgrammingMadeEZ -- nice to know that it can grab Access 97 data too -- what happens, though, if it is not properly structured? Will everything still go up? What if some tables are seemingly not connected (but should be)? I do a lot of 'fixing' to get data ready but Access can't even read 97 files after 2007 -- just wondering if the data will all move up and structural changes, relationships, and indexes should be done later ...? thanks
+LearnAccessByCrystal If the relationship or indexes aren't brought up to the SQL server, you can still add the key constraints and indexes later. Most of them will be imported though.
thank you. You are awesome!
Thanks again, do we need to split our data ?
Bilal Kareem If your database is currently not split, this will split it for you.
Programming Thanks a lot Steve, you are awesome :)
Wonderful
Thanks a lot :)
Thanks for the vidéo, Plz can you give us a link to download old version of ssma especially for 32bit (we use winXP and SQLserver2008R2), we remind you that microsoft's officiel website present only the last release of ssma. THANK YOJ VERY MUCH
+Josh Goldfarb You will just need to link the database tables manually to your front end. Check out my advanced Access 2013 series and I walk through how to do that there.
+Programming I have a query in SSMA and stuck with it for long time now. Could you please help me to sort it out. Below are the details.
I am using SSMA to Access for a particular migration project to migrate all possible tables, views, linked tables, etc. I am using customized ConversionAndDataMigrationSample.xml, VariableValueFileSample.xml, ServersConnectionFileSample.xml files to provide all source, destination and variables to SSMA based on the requirement of my project. I executing SSMAforAccess.exe in cmd by passing the location of these xml files containing source, destination, variables details required to complete the migration. Migration works perfectly however i want to store a particular service account credentials in connection string to link my Access tables with SQL tables, this can be achieved by marking Linked Table property as "True" in Default project settings of SSMA GUI. It does work and stores the service account credentials in connection string when i directly create a project using SSMA GUI however whenever i try to achieve the same using xml files execution method, the connecting string in the linked tables generates without any service account credentials in it and which is an issue as per the requirements of the project. I am using the below node in ConversionAndDataMigration.xml schema XML file to map Linked Table property as "True" in Default project settings in XML.
Also tried values like True, 1, yes, true, etc.
I could be wrong here in choosing this node to store required user credentials in connection string. Could you please direct me to correct node or method to store service account credentials in connection string while migrating access data to SQL using CMD & XML files.
Honestly I've never had that type of scenario come up. I don't really know how to resolve that particular issue.
Thanks for the quick response. Is it possible for you to lead me to a person who can help in this scenario? I have posted this query on MSDN forum as well but didn't receive any response yet.
My best suggestion would be to use AccessForums.net. There's a bunch of great people over there who might be able to help.
Thanks, i'll get in touch with them. :)
Thanks
The have a version 7.8 now
Im running my sql server on a XAMPP stack i use the same settings as you but it wont connect. any ideas ?
+mrbkent XAMPP is MySQL, this was for MS SQL Server.
+Programming Ahhh i see thought it might work the same, but thank you
quick question please , how many users can access database take?
and how many users can open simultaneously? I'm about to build a database and deploy it with 280 users is that possible? any recommended way to publish? thanks a lot :)
Bilal Kareem There is not hard limit to how many users can be using your FE to access the data on your BE, but realize system resources will play a big part in the user experience. 280 users on simultaneously would require you to be using at least a standard version of SQL server on a very beefy system. Lots of RAM, lots of CPU cores.
thank you for the quick replay, tools will be provided by the company , my concern is the number of the users because I never build a data for a 250+ users and I red some reports says only 256 users , but if will migrate my BE to SQL server will be unlimited right?
Yeah there's no limit on SQL connections except for what the resources can handle.
Success!
Looks like a great tool. Can't get the 32 (v6) or 64 bit (v7.4) versions to work, however. Frustrating. Any tips?
"Access Object Collector error: Database
Retrieving the COM class factory for component with CLSID {CD7791B9-43FD-42C5-AE42-8DD2811F0419} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)). This error may be a result of running SSMA as 64-bit application while having only 32-bit connectivity components installed or vice versa. You can run 32-bit SSMA application if you have 32-bit connectivity components or 64-bit SSMA application if you have 64-bit connectivity components, shortcut to both 32-bit and 64-bit SSMA can be found under the Programs menu. You can also consider updating your connectivity components from go.microsoft.com/fwlink/?LinkId=197502.
An error occurred while loading database content."
Hey, I know this was 6 months ago. But I got this error too. Was able to fix it by downloading a Microsoft product by searching "Microsoft Access Database Engine 2010 Redistributable"
So, do I migrate my front end, back end, or both?
Since a database is just for data, you'd only be migrating the tables you want on the back end.
Thank you. SSMA did not work for me. I've gone back to your advanced programming videos, and I now have my back end on my company's SQL server, and linked to my front end. Now I'm working on distributing my front end.
Ok... you might have needed to get the latest version 7.0 of SSMA to get it to work, especially if you're using Access 2016.
I am running 2016, and we downloaded the latest version from the website...but we just couldn't get it to work. Migrating my data the long way worked just fine. I'm about to download the SSE Setup wizard now.
You forgot to say that this only works with Office install, not click to run.
Click to run wasn't a thing in May of '15.
Thanks :D
it is going fine with limited data ,but migrating a 100 thousand records it failed, any thoughts please.
showing Data migration error , so all tables are rollbacked, so now i 've migrated 66 tables and 1 table which is having 100 thousand record it is still failing
+saurabh banerjee , further to add more , it has inserted records up to 140 thousands , rest 20 thousand records are not inserted , how to to overcome this issue
+saurabh banerjee It would help if you told us what the error is.
Thanks after coming back
i am getting the TCP /IP issue [Datamigrator: Error] [7320/9] [2016-01-08 15:26:16]: The following error occurred during migration of the current batch:
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
See the log for the detailed information.
[Datamigrator: Error] [7320/9] [2016-01-08 15:26:16]: Exception: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
site: Void OnError(System.Data.SqlClient.SqlException, Boolean, System.Action`1[System.Action])
source: .Net SqlClient Data Provider
error code: -2146232060
data: [HelpLink.ProdName] = [Microsoft SQL Server]
data: [HelpLink.EvtSrc] = [MSSQLServer]
data: [HelpLink.EvtID] = [10054]
data: [HelpLink.BaseHelpUrl] = [go.microsoft.com/fwlink]
data: [HelpLink.LinkId] = [20476]
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()
at System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()
at System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer()
at System.Data.SqlClient.TdsParserStateObject.TryReadByte(Byte& value)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlBulkCopy.RunParser(BulkCopySimpleResultSet bulkCopyHandler)
at System.Data.SqlClient.SqlBulkCopy.SubmitUpdateBulkCommand(String TDSCommand)
at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsync(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)
at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestContinuedAsync(BulkCopySimpleResultSet internalResults, CancellationToken cts, TaskCompletionSource`1 source)
at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestAsync(CancellationToken cts, TaskCompletionSource`1 source)
at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalAsync(CancellationToken ctoken)
at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServerAsync(Int32 columnCount, CancellationToken ctoken)
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader)
at Microsoft.SSMA.Framework.Access.SqlServer.DataMigration.TableDataMigrator.ProcessNode(IDataMigrationContext context, DataMigrationState state, XNode node)
[Datamigrator: Error] [7320/9] [2016-01-08 15:26:16]: Inner Exception #1: An existing connection was forcibly closed by the remote host
site:
source:
error code: -2147467259
Try using the Migration Assistant wizard. th-cam.com/video/YJeS7nr365M/w-d-xo.html
Flibber-Flab
Aferin .
Hi Steve,
I am exactly doing same way as you are doing,
As its related to my work I am giving the server Name,
Server Port:default
Database: Name
Its shows me error Create DATABASE Permissions denied in Database 'master'
I dont understand why its creating in master.
Any suggestions pls.
The database should not be called "Name". You should be using the new database name that you're migrating to or if it already exists, then you use that databases name. Otherwise the connection will default to the master database.
Hi Steve,
I am very new to access coding.
Thank You for your suggestion. It worked and I was able to Import the data to Sql server and got it linked. Exactly how you showed in the video.
But My scenario is little different . I have a mail page PCC3000_FrontEnd.accdb
And there exists a data base connection between 3 other accdb files.
And I have't Imported/Linked them to sql.
Now when I run the application at the its giving me an error msg
Tables not reconnected:Invalid procedure call or argument.
Run time error 5: Invalid Procedure call or arguments.
Can you please advise how to fix the Issue and run my application smoothly.
I have no way of knowing how to solve your problem.
Hi Steve,
Thank You for the help.
I have see the above video and converted the access objects tables in to sql tables using AccessSSMS 6.0
And the key were created by the tool. And the tables were linked automatically by the tool access ssms 6.0
Now if I open the tables in access its showing as True/False Instead of check box(Yes/No)
And so the in code they are checking for yes/no or -1/0.
But I have true or false in the columns.
Do you think that's causing the Issue.
Any advised how to fix those Conversion Steve.
Please advise.
Thank You for all the Help Steve !
Yes. That's why it's not a checkbox anymore but it's now a bit field. FWIW, SQL stores this as a 1 or 0. Access tables store it as a -1 or 0. I don't know that anyone fully understands why Microsoft decided Access needed to do this differently than all other RDBM's out there.
Because my Table has 85 columns, and I can't make structure manually.I
found very fast and elegance decision Search - ( excel2mysql es ) - -
very useful tool. Converts ACCESS, .xsls , xls , csv tables to SQL code for easy
import in database.
I really need this information but I'm sure this video could easily fit into 5 minutes if you weren't so annoyingly verbose. Tons of unnecessary words and information rubbish - spelling url, informing about file size, etc.... I was going to subscribe but have changed my mind. You're respecting neither other people's time nor your own