Solved

Questions about using ADO Recordsets in VB.NET vs VBA

Posted on 2016-07-28
11
63 Views
Last Modified: 2016-07-29
I read in the "Microsoft Access Guide to SQL Server" book the following: "Code in Access Class modules is very portable to Visual Basic.  This means that a middle tier data-access class can be prototyped and tested using Access and then ported to a Visual Basic ActiveX DLL project later."

However, I just tried writing code to open an ADO recordset in VB.net, and accessing the value of the fields using the "RS!Fieldname" syntax that works fine in VBA didn't work.  Nor did the "RS("FieldName")" syntax.  I had to use the syntax "RS.Fields("Fieldname").Value" syntax.  Generally, is there a list of syntax that is VBA specific and syntax that will work in both VBA and VB.NET?  I want to use syntax that will work with both so I won't have to change as much when it comes time to convert from Access class modules to VB.net classes?

Also, I read in a Microsoft on-line document "to make the RecordSet fully usable within a .NET application, convert it to an ADO.NET DataSet by using the OleDbDataAdapter class".  What more can you do with a dataset in a .NET application that you can't do with an ADO recordset?
0
Comment
Question by:Declan_Basile
  • 3
  • 3
  • 3
  • +2
11 Comments
 
LVL 34

Expert Comment

by:PatHartman
ID: 41733090
I haven't seen such a list but I would guess based on your example that you should always use the long syntax rather than any shortcuts.  For example, VBA assumes .value as the default property but vb.net makes no such assumption so you always need to specify .value.  The rs!fieldname syntax is also a shortcut.  So if you can figure out what the "long" reference is, use that.
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 251 total points
ID: 41733106
<<"Code in Access Class modules is very portable to Visual Basic. >>

  That's VB, not VB.net and they are different.   That statement refers to VB aka "Visual Basic Classic"

 VB.Net is an entirely different  animal and nothing like VB classic.  It's similar in syntax, but not the same at all.

 On the other hand, VB and VBA are almost identical.  Most code is a drop in to each other with very minor changes.

<<What more can you do with a dataset in a .NET application that you can't do with an ADO recordset?>>

 It's more a matter of working with other classes in the .Net framework rather than record operations specifically.

 For example, streaming a record set to disk.  With ADO, you'd need to do that on your own.  In VB.net using the OleDbDataAdapter class, it would be a method call which is already built into .Net.

Jim.
0
 
LVL 1

Author Comment

by:Declan_Basile
ID: 41733223
So can I convert an Access class module to a  Visual Basic ActiveX DLL project using visual studio?  How would I tell it when I create a new project that it will be a VB project and not a VB.net project?
0
 
LVL 57
ID: 41733392
<<So can I convert an Access class module to a  Visual Basic ActiveX DLL project using visual studio? >>

 Cut and paste of the source is the only method I'm aware of.

<<How would I tell it when I create a new project that it will be a VB project and not a VB.net project?>>

  VB is dead as far as VS is concerned.  Last version of VB was 6.0, and extended support ended in 2008.   While it's still used by some, it has largely died out for .Net    Last version of VS for VB classic was 6.0 .  That was in the late 90's.   VS switched to VB.Net in VS 2002.

 What's the copyright date on the book your looking at?  Must be quite old.

Jim.
0
 
LVL 1

Author Comment

by:Declan_Basile
ID: 41733614
Yes it is.  The copyright is 2001.  It's a great book but I don't believe it has ever been updated.  The idea the author was promoting was that the middle (data access) tier of a multi-tier application could be logically separated into Access class modules to make testing and debugging simple by only having to worry about a single file on a single computer.  
He wrote that Access class modules are virtually identical to Visual Basic and can be easily ported to Visual Basic when the time comes to physically implement a middle tier as a set of active X dlls, and that once that is done, those class modules can be referenced and called upon from a browser client application as easily as they can be used from an Access client application.  Is all this still true except that the code is not as portable from Access to vb.net (which took the place of classic vb)?
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 34

Expert Comment

by:PatHartman
ID: 41733625
VB and VBA were very close and so code could be ported, frequently with no changes.  That isn't the case for VB.Net.  There are some similarities but you'll have a lot more work to port the code from VBA to VB.Net
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 251 total points
ID: 41733733
<<  Is all this still true except that the code is not as portable from Access to vb.net (which took the place of classic vb)?>>

 That would still be true, but even COM is on its way out at this point.  Although I think it will be with us for some time yet, that time is coming to an end.  Look at Microsoft's newest browser, Edge.   It doesn't work with COM/Active-X at all.   Everything in the Microsoft world is .Net now.

 But if you look around, there are very few Access apps that have ever taken that approach.  What it boils down to is if your really going to take the time to do a n-tier design, then your not even going want to use Access to start with.

Access has a couple of fundamental flaws when considering it for enterprise level types apps (ones where you'd need multiple presentation layers):

1. It's highly sensitive to its run time environment because it doesn't compile into a .exe.   An Access app is more like a document that is read rather than a true program.

2. It has incomplete support for the iDispatch interface in COM.  This makes most 3rd party controls unusable, so it lacks any kind of extensibility in the interface.  So what you see is what you get for the most part.  That's not a bad thing, just a design decision.  But it limits what you can do in the UI of any app you create (like wouldn't it be nice to have a true grid control rather than continuous forms or a datasheet).

3. It's hard to really separate the UI/presentation layer from the business layer in Access.

The whole point of using Access is that it saves you from doing a lot of work by having a lot built-in.  If your going to give that up and do it your way (and have Access fight you all the way to boot), then you might as well use something else and avoid the issues.

Jim.
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 83 total points
ID: 41733826
I agree with Jim.  The only reason to use Access is to take advantage of its RAD capabilities.  If you are not going to use them, don't use Access.  Access is very scaleable but only if you go with the flow.  Think of it as the Access Way or the highway.  By using SQL Server (or other RDBMS) as the BE, you can have as many concurrent users and as large a database as your RDBMS will support, so you could in theory have thousands of users.  You just have a slightly clunky distribution method, poor support for a multi-developer environment, and are limited to a client/server implementation on a LAN or to using Citrix/RDP on a WAN.  If you can live with those three limitations, and want to avail yourself of a fabulous RAD tool, then go with Access.  If you have a problem with any of those things, step away and don't look back.  Access is actually flexible enough so you can coerce it in to doing some pretty slick things but you'll learn a lot of new curse words along the way and ultimately, Access will beat you so don't go into a battle you cannot win.
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 83 total points
ID: 41733934
He wrote that Access class modules are virtually identical to Visual Basic [True -- but VB is not VB.Net AT ALL]and can be easily ported to Visual Basic [but basically have to be re-written for VB.Net where EVERYTHING is an object] when the time comes to physically implement a middle tier as a set of active X dlls[But only IE ever supported ActiveX, and NO ONE is starting a greenfield ActiveX project in 2016], and that once that is done, those class modules can be referenced and called upon from a browser client application as easily as they can be used from an Access client application [if anyone would be using ActiveX].  Is all this still true except that the code is not as portable from Access to vb.net (which took the place of classic vb)?

Basically, the code is not portable at all.  By the time you sorted out all the syntax errors that VB.Net will throw, you'll wind up rewriting 90% of it.  The logic will hold (your loops, your recordset definitioins, your SQL Strings, and what you do with data) but the methodology when everything is an object and Me.SomeControl.Value = Something becomes much, much harder and you curse .ToString() with your last breath.

Access had a good relationship with VB and ASP
Access has basically no relationship with VB.Net and ASP.Net.
They know some of the same people and use some of the same turns-of-phrase, but they aren't acquainted, never mind related.
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 83 total points
ID: 41734503
FWIW, I took the same journey a while back, and I can tell you there is very little that will directly translate over to .NET code - and even the stuff that will translate over could do with a good scrubbing to make it more "Net-like". At the time, I would have considered my VBA knowledge to be very, very extensive, including extensive use of class-oriented programming (not OOP, mind you) but all that got me in .NET was an understanding of the basics of programming theory. In fact, I had to "unlearn" quite a bit to be able to wrap my head around the whole .NET thing.

For example, you don't really use recordsets in .NET any longer - you use DataTables instead, which would be the rough equivalent of an ADO Recordset. You can also use a Dataset, which can contain multiple, related Datatables. Both of those are based in the SQL or OLDEB namespaces. You can force .NET to use the older ADO libraries, but it's generally a bad idea to do so.
0
 
LVL 1

Author Closing Comment

by:Declan_Basile
ID: 41735251
Thank you everyone.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now