Access database 2007 read files from VB .NET and visual studio 2017

Question

I have a customer using Access version 2007.   We are planning to update things and move them to Microsoft SQL and program in VB .NET.

I have a 64 bit Windows 10 machine and we are using Access 2016 on that machine.   The access program and files seem to have transferred from the 32 bit to 64 bit fine.   But we have really just begun to use it so we really do not know.

Here is the goal of this project.

Keep the existing Access program in use while we transition functionality to SQL for use on both the cloud and local.   Said in another way, we intend to use both VB and Access programs at the same time so that we do not have to move all features at the same time.   We plan to move the project to VB.NET eventually but that is going to take some significant time.   So we need to get to the Access files so that they can be updated from VB.net and also SQL files updated from Access.  

So the Access questions I have are.

1.  What is the recommended way to update the SQL and the access tables at the same time?   Is it best to run the sql calls from VBA in Access?  Is there a better way to do this?

2.  Are we going to run into locking issues?   Should we permit updating of a table to be either done only by Access or only by VB .NET?

We certainly could create a table to control locking of the tables.   Is that needed?

3.  Microsoft in their infinite wisdom makes working with 32 bit and 64 bit programs in Office difficult.   Client site is currently in 32 bit.  Do we need to update the client to 64 bit Access (apparently this means moving all their Microsoft Office products to 64 bit)?  Can we use the 64 bit environment for development and still keep compatibility with the access 2007 32 bit.  We know we can not use features that are not part of 2007.  But we are not planning to make any significant changes to the Access code at this time (just the data saving).

So any recommendations would be appreciated.

   

Answer 1

I don't think you want your data in two separate databases, which would need to be kept in sync. That could end up being a nightmare. I would move the data from Access tables to corresponding SQL Server tables and change all Access tables to link to the tables in SQL Server. This would allow your Access app to continue to work with the data, once migrated to SQL Server, while you develop the new VB.NET app.

From migration to SQL Server I would consider using the SSMA utility:

https://www.microsoft.com/en-us/download/details.aspx?id=54255

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 2

Do you know what transactions are?

Is the Access database split into front-end and back-end?

You imply you will convert in pieces but what do you define as pieces? Will the pieces be tables or sets of tables or will they be applications? Probably you are thinking of converting applications but that should be made explicit. So you will convert applications and other applications that have not yet been converted will be accessing the Access database while converted applications might be accessing the same data in SQL Server, correct?

Another possible plan is to convert the applications to VB.Net and use Entity Framework and a Data Access Layer (DAL) for the database. The DAL could support Access and should be designed to be easily switched to SQL Server. Then hopefully you can do an overnight conversion of the data to SQL Server and switch the DAL to use SQL Server. You certainly could have the new system well-tested before the conversion of the data, especially since you can use the new version of the VB.Net applications with Access before the conversion.

Now is a good time to learn about things like Team Services and Git and promotion/deployment of applications so you can do those things like professionals.

---

Sam Hobbs
SimpleSamples.Info

Answer 3

Thank you for your prompt reply.

The conversion tool is a good idea, because it will transfer the tables without having to enter all the fields individually.   Maybe then the access could be set up to link directly to that data in SQL instead of storing anything in the old access.

I would think we could just point the access to the SQL server instead of the Access data.

Have you converted an Access database to SQL and continued to use the Access front end to then connect to the SQL?

I do appreciate you thoughts.

Answer 4

 Yes the data in Access can be split from the front end.  There is a feature for doing that in Access.

I am already connected between the VB code and the Access database.  It may make more sense to move everything into SQL and then have the Access front end tie into that.  Have you had a good experience using Access as a front end to SQL?

Our real concern is that there are a number of reports and input screens that are working adequately in Access so we do not want to bother changing those at this time.

Ultimately we are converting some table formats to fit with code that is already written in VB.  But much of the Access file structure will be preserved.

We are looking at all the possibilities before selecting our actual path of action.   We find that we avoid lots of problems that way.

You assistance and thoughts are very much appreciated.

Answer 5

I have not worked with Access and SQL Server databases for production data in the manner suggested here. I have done conversions of live production data but not this specific situation.

My suggestion was to continue to use the Access database until you have all applications converted. I did not think of the possibility of converting the data to SQL Server sooner and then linking from Access. That might work; you could investigate that.

Yes it helps to have a plan and ensure problems are understood and solved early instead of later. It helps to have contingencies too.

Do you understand about a Data Access Layer and about Entity Framework? They are separate things but usually a DAL is done using EF. It takes time to learn EF but it can save time after that.

---

Sam Hobbs
SimpleSamples.Info

Answer 6

I generally work with Oracle, but the concept is the same. Most of the Access tables are linked tables to Oracle tables or views.

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 7

Given that you “final” resting place will be SQL server, and given that this project may take significant time, then yes, I would migrate the tables to SQL server, and keep Access as the front end.

Access x32, or x64 does not matter in terms of connecting and consuming SQL data (so a non-issue). In fact, it don’t matter for say vb.net either (both x32 and x64 clients can freely connect to either bit size SQL server without issues).

And I would certainly not recommend introduction of any office x64 unless that is a must do. (You would have to convert access x32 application to run with x64 – this is not too hard, but if not real reason to do so exists, then I would not).

So the client software vb.net or Access connecting to SQL server is bit size neutral process (it don’t matter).

Since the goal is to move the data to SQL server, then that is likely the first step. Access as a client to SQL server works just fine. You find about 99% of the existing code works fine, and only a few minor tweaks are required to the existing application to replace the accDB back end with a SQL server back end (we assume the access application is split, right???).

And sure, both access and .net programs can hit + use + update SQL tables without any real locking issues beyond what you likely have now, and do so at the same time.

So office/access can and should remain as x32 – this will have no bearing on the version or bit size of the database server (sql server).

If the ultimate goal is SQL server, then I makes sense that this is the first step in migration, since you don’t want to develop the .net application with Access as the back end, since over time some “bugs” and issues may arise – best to develop “along” the way with the given database platform you ultimately want to use.

The other key advantage here is then you not be shuffling data between Access and SQL server, but only a one-time migration effort. At that point, both Access and your .net program(s) are sharing a common database. And both systems are sharing the ultimate final resting place for the data.

And of course using SQL server for the data back end, be it Access as a front end, or vb.net as a front end works rather well. And you find Access in most cases performs just as well as .net code – it all comes down to poor code, or good code and design approaches (for both Access, and .net). And adopting SQL server as a back end tends to eliminate any issues of scalability of using Access for the front end (so 10, or 75 front end users of Access to sql server is a rather reliable setup).

It does help to have migrated several Access back ends to SQL server, and then made the minor tweaks required to Access. So someone with good Access skills who done such migrations is a required skill here.

Regards,
Albert D. Kallal (Access MVP, 2003-2017)
Edmonton, Alberta Canada