Tech Off Thread

6 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

Convert MS Access DB to MS SQL w/ Visual Studio

Back to Forum: Tech Off
  • User profile image
    allanmac

    I have created a database using MS Access and VBA.  I want to convert it to run on MS SQL server for the backend.  I have recently obtained MS Visual Studio 2005.Net Professional, which is what I plan to create the front end with.  The DB can have up to 5 people accessing it simultaneously, some via mobile through a VPN on Verizon EDVO. 

    My questions are:

    1. I have thought about making the front end web based.  Pros & Cons?

    2. What is the best language/platform to use? VB, C#, ADO, ASP, others?

    I would consider myself an intermediate programmer.  I started w/ basic (Vic20/C64), pascal, and Fortran in the 80's.  HTML, Java, C, JS, VBS, and Visual Basic for Applications (Access & Excel) in the mid 90's.  I consider VBA my strongest.  Other than DB development (VBA), most of my programming has just been playing around or for school projects, nothing serious.  My main job functions are network/systems admin & hardware/PC maintenance.

    Any feedback would be greatly appreciated.  Thanks!

  • User profile image
    Tensor

    allanmac wrote:
    I have created a database using MS Access and VBA.  I want to convert it to run on MS SQL server for the backend.  I have recently obtained MS Visual Studio 2005.Net Professional, which is what I plan to create the front end with.  The DB can have up to 5 people accessing it simultaneously, some via mobile through a VPN on Verizon EDVO. 

    My questions are:

    1. I have thought about making the front end web based.  Pros & Cons?


    Pros:
    Zero deployment to client.
    Relatively platform indipendant.

    Cons:
    Stateless model which needs some gettugn used to if you are sued to a windows type environment.
    Doesnt work offline.
    Typically not as rich user experience.

    If all the users are going to be on windows, directly wired in to your network, then I would gues windows forms will be easier for you to get going in.

    allanmac wrote:

    2. What is the best language/platform to use? VB, C#, ADO, ASP, others?

    I would consider myself an intermediate programmer.  I started w/ basic (Vic20/C64), pascal, and Fortran in the 80's.  HTML, Java, C, JS, VBS, and Visual Basic for Applications (Access & Excel) in the mid 90's.  I consider VBA my strongest.  Other than DB development (VBA), most of my programming has just been playing around or for school projects, nothing serious.  My main job functions are network/systems admin & hardware/PC maintenance.

    Any feedback would be greatly appreciated.  Thanks!


    ADO and ASP are technologies you will be usign in .NET. You are doign db access so you will eb using ADO. ASP will be right for you if you want to do your front end.

    Your choice is pretty much going to be VB.NEt or C#. It is apples and oranges really - they both are capable of the same job - some people just prefer {}; while soem do not. I would say that if your experience is in VBA then you will perhaps find VB.NET easier to get into as the syntax is similar - allthough the other side of that coin may be that you do not change how you program to take full advantage of .NET because youdotn have to

    I'd advise go with VB.NET, but try and learn what is differnt about it, rather just what is the same, if you see what I mean.


    HTH

  • User profile image
    Pace

    Hey there.

    Same job as me Big Smile

    First thing to do is just port access to use the SQL tables instead before you start tinkering with the front end.

    Once you have that only then is it worth looking at changing.

    Looking at your skills I would use vb.net as you will be really familiar with it.

    Greatest thing about .net is its ease of use.

    You know how in VBA you have to do;

    dim someStr as string
    me.txString.SetFocus
    someStr = me.txString.text

    well in .net you could just say;

    dim someStr as string = me.txString.text

    honest, I write a third less code in .net compared to the nightmare of an IDE that access is. The forms snap lines etc in VS are a god send, they are really hitting home with it now. IMO the only good thing access has left going for it is its reporting functionality.

    Anyway, go VB.net, but port access to SQL first Wink

    Edit; Learn Stored Procedures in SQL Wink

    have fun!

  • User profile image
    allanmac

    Thanks for the input.

    The DB is used to enter run reports for Emergency Medical Services by our city paramedics in the field.  Currently, I have replicated Access DBs on each laptop that they sync once per day to the master DB on the server. 

    I think I will abandon the web based front end because it has to be available offline.  I was hoping to avoid the synching/conflicts issue.  I guess I'll have to figure out replication & synching using MSDE on the laptops.

    I thought VB would be easier for me to use, but considered C# as a way to improve my programming background/skills.  I'll probalby just use VB as I get used to interfacing with SQL.  Maybe C# next time.

    Again thanks for the suggestions.  I really just wanted a little direction as where to start before jumping in over my head! ;-}

  • User profile image
    SimonJ

    If you have to go offline you should be looking at using SQL Server Express on the workstations with a VB.NET front end and using SQL Server replication between the central SQL Server and the SQL Server Express instances on the workstations. You need to read up on replication, uniqueIDs (GUIDs) and partitioning data.

    You should also consider the Offline Application Block from MS Patterns & Practices. This gives you seamless network connectivity changes, caching etc, just like Outlook 2003 & 2007. Using the Offline Application Block is complicated but your application might benefit from it.

    SimonJ

  • User profile image
    TommyCarlier

    If you're going for Windows Forms, and you want easy deployment, easy updating for online and offline scenario's, you should check out ClickOnce deployment. And, like SimonJ already suggested, SQL Server Express is probably the way to go for offline scenario's. And if you use ClickOnce, both the application and the data can be synchronized when online (either on the web or on the intranet). We're using ClickOnce as a deployment for our internal tools (only available on the intranet, on a shared folder).

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.