Replacing back-end MS-ACCESS with SQL SERVER
-
We use a legacy application (vb6 / access). The UI uses DAO to connect to the local front-end MDB file. Local tables are linked to the back-end mdb file which is located on the server. So far so well. The problem is that the back-end MDB is now 1,8 Gb and it will be 2 Gb pretty soon, which is fatal in MS-Access world. We're thinking of replacing the back-end db with sql server express, so we'll have to make minimal changes to the front-end databases (just the connection string). Is it a good idea? What is the correct procedure to import the back-end MDB to SQL server express? Which version (LocalDB etc) should we use? Thanks in advance. PS There is no option of making/buying a new application. We are trying to solve our problem with minimum effort.
-
We use a legacy application (vb6 / access). The UI uses DAO to connect to the local front-end MDB file. Local tables are linked to the back-end mdb file which is located on the server. So far so well. The problem is that the back-end MDB is now 1,8 Gb and it will be 2 Gb pretty soon, which is fatal in MS-Access world. We're thinking of replacing the back-end db with sql server express, so we'll have to make minimal changes to the front-end databases (just the connection string). Is it a good idea? What is the correct procedure to import the back-end MDB to SQL server express? Which version (LocalDB etc) should we use? Thanks in advance. PS There is no option of making/buying a new application. We are trying to solve our problem with minimum effort.
There seems to be plenty of resources about moving data from Access to SQL Lite, I would be more worried about the queries you have supporting your application, Access sql is not the same as TSQL. You will need to assess whether the queries will work (not a trivial proposition)! I would be more concerned that the UI is in VB6, and changes to support Sql Lite are going to have no support outside your people.
Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP
-
We use a legacy application (vb6 / access). The UI uses DAO to connect to the local front-end MDB file. Local tables are linked to the back-end mdb file which is located on the server. So far so well. The problem is that the back-end MDB is now 1,8 Gb and it will be 2 Gb pretty soon, which is fatal in MS-Access world. We're thinking of replacing the back-end db with sql server express, so we'll have to make minimal changes to the front-end databases (just the connection string). Is it a good idea? What is the correct procedure to import the back-end MDB to SQL server express? Which version (LocalDB etc) should we use? Thanks in advance. PS There is no option of making/buying a new application. We are trying to solve our problem with minimum effort.
You'll need to migrate the back-end database to SQL: SQL Server Migration Assistant for Access (AccessToSQL) - SQL Server | Microsoft Docs[^] Microsoft SQL Server Migration Assistant for Access[^] Once you've done that, you'll need to change the links in the front-end database to point to the SQL database instead of the back-end Access database.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
There seems to be plenty of resources about moving data from Access to SQL Lite, I would be more worried about the queries you have supporting your application, Access sql is not the same as TSQL. You will need to assess whether the queries will work (not a trivial proposition)! I would be more concerned that the UI is in VB6, and changes to support Sql Lite are going to have no support outside your people.
Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP
Actually I meant SQL Server (Microsoft), not SQL Lite. :)
-
You'll need to migrate the back-end database to SQL: SQL Server Migration Assistant for Access (AccessToSQL) - SQL Server | Microsoft Docs[^] Microsoft SQL Server Migration Assistant for Access[^] Once you've done that, you'll need to change the links in the front-end database to point to the SQL database instead of the back-end Access database.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Well, I installed the SQL server and made the migration too. However, there are some problems with my source code: To open recordsets with DAO, I use Set rs = currentdb.OpenRecordset("test", dbOpenDynaset) which seems not to be ok with SQL server, since rs.FindFirst can't find anything. After some googling, I found out that I should change to: Set rs = currentdb.OpenRecordset("test", dbOpenDynaset, dbSeeChanges) so now i'm able to use rs.FindFirst, but i'm facing a new problem: Can't use rs.AddNew Any ideas? PS there are hundreds of thousands of code lines, so changing the way my software works is not an option. Like I said, I'm just trying to make things work as is.
-
Well, I installed the SQL server and made the migration too. However, there are some problems with my source code: To open recordsets with DAO, I use Set rs = currentdb.OpenRecordset("test", dbOpenDynaset) which seems not to be ok with SQL server, since rs.FindFirst can't find anything. After some googling, I found out that I should change to: Set rs = currentdb.OpenRecordset("test", dbOpenDynaset, dbSeeChanges) so now i'm able to use rs.FindFirst, but i'm facing a new problem: Can't use rs.AddNew Any ideas? PS there are hundreds of thousands of code lines, so changing the way my software works is not an option. Like I said, I'm just trying to make things work as is.
Any details you can provide about why you can't use the rs.AddNew? (Permission / Not Found / ? )
-
We use a legacy application (vb6 / access). The UI uses DAO to connect to the local front-end MDB file. Local tables are linked to the back-end mdb file which is located on the server. So far so well. The problem is that the back-end MDB is now 1,8 Gb and it will be 2 Gb pretty soon, which is fatal in MS-Access world. We're thinking of replacing the back-end db with sql server express, so we'll have to make minimal changes to the front-end databases (just the connection string). Is it a good idea? What is the correct procedure to import the back-end MDB to SQL server express? Which version (LocalDB etc) should we use? Thanks in advance. PS There is no option of making/buying a new application. We are trying to solve our problem with minimum effort.
-
We use a legacy application (vb6 / access). The UI uses DAO to connect to the local front-end MDB file. Local tables are linked to the back-end mdb file which is located on the server. So far so well. The problem is that the back-end MDB is now 1,8 Gb and it will be 2 Gb pretty soon, which is fatal in MS-Access world. We're thinking of replacing the back-end db with sql server express, so we'll have to make minimal changes to the front-end databases (just the connection string). Is it a good idea? What is the correct procedure to import the back-end MDB to SQL server express? Which version (LocalDB etc) should we use? Thanks in advance. PS There is no option of making/buying a new application. We are trying to solve our problem with minimum effort.
-
To buy yourself some time why not split the backend database into multiple linked databases?
Well, having multiple back-ends would do the job for some months more, but like you said, this is not a permanent solution.
