Vb.net 1st real attempt. (2 Viewers)

GBalcom

Much to learn!
Local time
Today, 01:08
Joined
Jun 7, 2012
Messages
459
I've decided to try and learn VB.net, using VS community 2015. After running through some of the example apps such as Picture Viewer, etc. I decided to take my simplest access application and try and duplicate it in VS.

This app has only 3 tables, minimal VBA code, and keeps track of our engineering changes. It requires no reporting, and only 3 forms. (NavForm, Input form for our tags, Input form for our change records)

Overall, I think VS is neat, but it's amazing how long it's taken to make this simple app so far...and I'm far from done. I'm really questioning if it's worth the extra effort.

One hang up I'm having is that my back end .accdb uses one attachment type field. I've been researching for over a week on how to handle this in VB.net. The only viable solution seems to be DAO, which people are saying shouldn't be used because it's old and outdated. I'm basically at a crossroads, and don't know how to proceed.

Any thoughts appreciated. If anyone has a system for dealing with the attachment type field in VS I'd love to hear it. :banghead:
 

MarkK

bit cruncher
Local time
Today, 01:08
Joined
Mar 17, 2004
Messages
8,178
I would just use DAO in your .Net project. The problem arises when you want to bind your DAO.Recordset to a Listbox in .Net, because the DAO.Recordset doesn't implement IEnumerable or IList, so it's cumbersome that way. But in your case, to manipulate the attachment you need to run methods of the DAO.Field2 object, right? So just use DAO. IMO.
 

GBalcom

Much to learn!
Local time
Today, 01:08
Joined
Jun 7, 2012
Messages
459
Thank you CJ,
your search came up with some I hadn't. Following one of the examples I came up with the following code:

Code:
Dim dbe As New DBEngine
        Dim db As Database = dbe.OpenDatabase("S:\Gary\MS Access\ACS Engineering Changes\ACS Engineering Changes_beVS.accdb")
        Dim sSQL As String = "SELECT * FROM tblEvent Where ID ="
        Dim sSQLWhere As Integer = Integer.Parse(IDTextBox.Text)
        sSQL = sSQL & sSQLWhere.ToString
        Debug.Print(sSQL)

      
        Dim rstRecord As Recordset = db.OpenRecordset(
               sSQL, RecordsetTypeEnum.dbOpenDynaset)
        'rstRecord.edit

        'Create recordset for attachments, then browse through and collect their file names
        Dim strFileNames As New ArrayList
        strFileNames.Clear()


        Dim rstAttachments As Recordset2 = rstRecord.Fields("Attachments").Value

        If rstAttachments.BOF And rstAttachments.EOF Then
            'no records returned
            Exit Sub
        End If

        Do Until rstAttachments.EOF
            rstAttachments.MoveFirst()
            MessageBox.Show(rstAttachments.Fields("FileType").ToString)
            rstAttachments.MoveNext()
        Loop

Two things about this code:
  1. I'd like to know a better solution for the sSQL variable. I tried concantenating the string with the variable in line, and couldn't get it to work.
  2. I want to pull in the FileNames, so I can load a list box on my form with the existing attachments for that record. Trouble is, I can't seem to pull the file name. Through the following code, I'd been able to determine there are 6 fields in this recordset;
    FileData
    FileFlags
    FileName
    FileTimeStamp
    FileType
    FileURL

But, I can't seem to pull the FileName, instead I get "System._ComObject"

Any ideas why?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 08:08
Joined
Feb 19, 2013
Messages
16,552
I'm no expert on vb.net, have gone the Java route.

I'd like to know a better solution for the sSQL variable. I tried concantenating the string with the variable in line, and couldn't get it to work.
if it the same as vba, then .text is only available if IDTextBox has the focus

But, I can't seem to pull the FileName, instead I get "System._ComObject"
Sorry, no. Perhaps you need to include a reference to DAO in references?


vb.net is more comfortable working with sql server, and attachments are unique (I believe) to access. Hope you haven't got lookup, multivalue or calculated fields - they are also pretty much unique to access, intended to work with sharepoint.
 

MarkK

bit cruncher
Local time
Today, 01:08
Joined
Mar 17, 2004
Messages
8,178
I would expect the code to look something like...
Code:
    Const SQL_BASE As String = "SELECT * FROM tblEvent Where ID = "
    Const FILE_PATH As String = "S:\Gary\MS Access\ACS Engineering Changes\ACS Engineering Changes_beVS.accdb"
    
    Dim dbe As New DAO.DBEngine
    Dim Names As New ArrayList
    Dim dbs As DAO.Database = dbe.OpenDatabase(FILE_PATH)
    Dim sql As String = SQL_BASE & Me.IDTextBox.Text
      
    With dbs.OpenRecordset(sql)                     'open recordset
        Do While Not .EOF                           'enumerate rows
            With .Fields("Attachments").Value       'this field holds a recordset
                Names.Add .Fields("FileName").Value 'add this field value to Names
            End With
            .MoveNext
        Loop
        .Close
    End With
    
    'use the Names arraylist here...
...but in this case...
Code:
            MessageBox.Show(rstAttachments.Fields("FileType").ToString)
...the DAO.Field2 object "FileType" does not expose a ToString method, so the framework returns the generic "System.__ComObject" string. You need to use the DAO.Field2.Value property to get data out of the Recordset.Field objects.
 

GBalcom

Much to learn!
Local time
Today, 01:08
Joined
Jun 7, 2012
Messages
459
Hey everyone,
Thanks to all who responded. I've got this to work now. This thread is what I was missing: https://www.syncfusion.com/faq/1144/i-get-the-error-system-comobject-when-using-recordset

For the benefit of the forum, here's my working code:

Code:
    Private Sub FillAttachments()
        '=============================================================
        ' this code requires that your project have the following COM Reference:
        '     Microsoft Office 14.0 Access Database Engine Object Library
        Dim dbe As New DBEngine
        Dim db As Database = dbe.OpenDatabase("yourdb.accdb")
        Dim sSQL As String = "SELECT * FROM tblEvent Where ID = " & IDTextBox.Text.ToString
        Debug.Print(sSQL)


        Dim rstRecord As Recordset = db.OpenRecordset(
               sSQL, RecordsetTypeEnum.dbOpenDynaset)

        'Create recordset for attachments, then browse through and collect their file names
        Dim strFileNames As New ArrayList
        strFileNames.Clear()

        Dim rstAttachments As Recordset2 = rstRecord.Fields("Attachments").Value

        If rstAttachments.BOF And rstAttachments.EOF Then
            'no records returned
            'MessageBox.Show("No attachments for this record")
            Exit Sub
        End If

        Dim AttachmentFileName As Field2 = rstAttachments.Fields("FileName")

        rstAttachments.MoveFirst()

        Do Until rstAttachments.EOF
            'add this file name to our arraylist
            strFileNames.Add(rstAttachments.Fields("FileName").Value.ToString)

            rstAttachments.MoveNext()
        Loop

        'populate listbox with attachment filenames
        CheckedListBox1.Items.AddRange(strFileNames.ToArray)

        rstAttachments.Close()

        rstRecord.Close()
        db.Close()


    End Sub
 

GBalcom

Much to learn!
Local time
Today, 01:08
Joined
Jun 7, 2012
Messages
459
almost there....I need this code to fire every time the parent record changes on the form. what form event fires to accomplish this?
 

GBalcom

Much to learn!
Local time
Today, 01:08
Joined
Jun 7, 2012
Messages
459
Almost ready to throw in the towel

I've tried really hard to learn VB.NET and gain it as a skill set. I'm starting to lose faith that this is a good direction for a part time programmer (<5 hours per week). I've worked for months now, working through an online tutorial, and a vb.net programming book. I'm sure it's possible to create a great application, but the time is excessive.

I suppose my main drive for trying this is because I was worried about access becoming deprecated as a product to the point where I couldn't utilize my programs. (heavy VBA usage).

The things I'm really struggling with are:
  • ADO.NET (the whole disconnected database model). I'm really missing a simple DAO recordset!
  • Reporting control (lack of function compared to Access)
  • development speed. Access for me is MUCH faster.

I've also just learned that I can create a DLL in Visual Studio for use in Access if I ever REALLY needed .net functionality. OOP is totally new to me, and I can now see the benefit of using class modules in Access.

So, should I throw in the towel and go back to Access 2010/2013 with a new perspective, or press on:banghead:?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 08:08
Joined
Feb 19, 2013
Messages
16,552
So, should I throw in the towel and go back to Access 2010/2013 with a new perspective
really depends on the direction you want to go. It's always better to have more than one tool under your belt - but only if you are going to use it, or because of wanting to learn something new.

Within the confines of business PC's with keyboard and mouse, I think Access has a lot to offer, particularly for businesses with small budgets and bespoke requirements. The time to develop in VB.Net, Java or other languages is significantly longer - I'd say typically by a factor of 10 which is not the small business way, partly because of budget but also they need to be more dynamic. Whilst for larger businesses there are many process critical applications which perhaps should be written in VB whatever to comply with the law as dictated by IT but never will be because only 5 people will be using it.

There is a growing move towards touchscreen devices which requires quite dynamic interaction with the user (and typically quite small screens) - and Access graphic manipulation is not the best, VB.net and the like generally has much better graphics capabilities. For now I see touchscreen as being primarily in the consumer province rather than the business data management province, but something to bear in mind for the future.
 

GBalcom

Much to learn!
Local time
Today, 01:08
Joined
Jun 7, 2012
Messages
459
The time to develop in VB.Net, Java or other languages is significantly longer - I'd say typically by a factor of 10 which is not the small business way, partly because of budget but also they need to be more dynamic. Whilst for larger businesses there are many process critical applications which perhaps should be written in VB whatever to comply with the law as dictated by IT but never will be because only 5 people will be using it.

CJ, Thank you very much for your response. You hit the nail on the head for me. We are a very small company, 10 employees total, with 3 in the office. I'm the General Manager, and wear many hats day to day, from engineering, purchasing, supervisor, etc. I have a good ERP system, and a good engineering system. They are both on SQL Backends. Over the course of 3 years, I've used Access to "fill in the blanks" between gaps in our system. This has bridged the "Islands of information", and allowed us to eliminate any duplicate entry, as well as many other benefits. I now have 12 little applications that we count on daily as a company. Some are automated and run at night to simply log the day's performance numbers into a table. Check metrics daily and email a report if necessary. Two are "Data Viewers" that we rely on for easy viewing of pertinent information on the shop floor and in the office. One other updates our google calendar with our ongoing production schedule. As you can see, these have become part of the lifeblood of our operation. I was concerned honestly about MS Access dying.

I've spent 2 months of nights and weekends trying to learn vb.net.
Taking a step back and looking at the big picture, there is no way I have time necessary to become proficient in vb.net long term. I can't afford to spend 40 hours on a project I could do in 4. My guess is that a well structured front end in Access will scale as far as I'll ever need.

I've decided to spend time refactoring my code and tightening up my applications in Access, instead of starting over. I have some FMS tools that have been handy in the past, that I'll use to their fullest shortly to help me produce better applications. Thanks again!!

There is a growing move towards touchscreen devices which requires quite dynamic interaction with the user (and typically quite small screens) - and Access graphic manipulation is not the best, VB.net and the like generally has much better graphics capabilities. For now I see touchscreen as being primarily in the consumer province rather than the business data management province, but something to bear in mind for the future.

Great point! We are currently using tablets on the floor to view my one access application. It's working because they are surface pro's with a decent size screen.
 

Awesomo

Registered User.
Local time
Today, 08:08
Joined
Feb 9, 2016
Messages
45
A bit late to the party on this, but I would advise not to use DAO code in Dotnet projects. Otherwise you are just replicating something that's just easier to do in Access.
You should add a dataset to your project, then work with that. You can then work with a datatable, which is the closest thing to a DAO recordset.
The first book I got on this was Pro ADO.NET 2.0 by Sahil Malik. I've just googled and it seems the book I got 10 years hasn't been updated since! So maybe a more modern version on ADO.Net would help you.
 

Lightwave

Ad astra
Local time
Today, 08:08
Joined
Sep 27, 2004
Messages
1,521
Also late to the party

Don't worry about MS Access being deprecated - get an installation cd if you are really worried and make sure you store it somewhere safe. Hell get three one for 2010 one for 2013 and one for 2016 if you are really worried. EVEN in the unlikely event MS go under that will probably do you for the rest of your life and probably the life of your children if AI haven't taken over even programming. The point about Digital things are they are immortal. That was the promise of the digital age.

People at my office complain about MS Access - a decade ago management gave it the the same line saying that there was an issue with business continuity.

Utter tosh.

They went off and spent 500 thousand on a document management system. Why so much? - because the vendor only had one user. That was 2007 and the vendor has now discontinued the product. It had so called rolls royce enterprise technology - big server - sql server - etc...

Access 2003 worked before they came on line and still works now that the vendor has gone off line - and all for pretty much zero - we pay for office licences anyway whether we use access or not.

Access 2016 will probably be good for the next 40 years I suspect so will Access 2003. That is almost definitely longer than any web based product.

For me the question was - do I need a web based application. The answer was - yes it would be useful. I now use a mix of access and web based stuff. Access I use for highly complicated + legacy stuff that I simply don't have time to recreate. I also use it for pure programming flexible business analytics which are really just one off. I also use it to keep costs down.

I don't know about you but we have a rolls royce LAN internal network - why not use it. The old legacy applications are actually getting faster!!!

Web based stuff costs because you end up hiring space on someone elses cloud.

I am a fan of web based stuff though but if its for the desktop I would just go for Access - its cheaper and more flexible. There still advantages to the desktop (tends to be faster / takes advantage of expensive structure already in place / often cheaper / quicker development time / combo boxes don't improve just because they are on the web :) / can be improved security / don't have a poxy save button etc...

I did a blog post on alternatives to MS Access on the web - apologies if people think its self promotion but easier for me to send you there than try and re-create it here.
http://rounduptheusualsuspects.org/?p=1360
 
Last edited:

GBalcom

Much to learn!
Local time
Today, 01:08
Joined
Jun 7, 2012
Messages
459
Thanks Lightwave,
I've settled on remaining with Access. I have an install disc for A2013, and am sticking with that for at least the next few years. I also have an FMS Ultimate suite license for 2013. Between them, I'm happy with my desktop development.

The next phase for me will likely be trying to at least view some of this data while off site. I may handle this with a simple remote login somehow. But, I don't need much more than that. I looked at Nubuilder, and it's pretty interesting. I may peruse that, for no other reason than curiosity, in the future.

For now, sending an email or text upon certain conditions, is the extent of my "offsite" needs. I've handled that easy enough with VBA in Access.


I sure hope things like Nubuilder and the like can "lighten the load" for future power users and part time developers in business settings.
 

Users who are viewing this thread

Top Bottom