Solved

using vb.net in access 2013

Posted on 2014-09-22
11
537 Views
Last Modified: 2014-10-06
In MS Access 2013 when I write code for an event the MS Visual Basic for Applications opens. Instead of writing the code in VBA is it possible to write the code in VB.net? If so what do I need to do so I may use VB.net along with VBA.
0
Comment
Question by:garysamuels
  • 3
  • 3
  • 2
  • +3
11 Comments
 
LVL 26

Assisted Solution

by:MacroShadow
MacroShadow earned 250 total points
Comment Utility
No, Office applications don't directly support VB.Net. You could write a .net dll and use it in your vba project.
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 250 total points
Comment Utility
VBA is the embedded language of Access.  There is no way to use VB.net directly.  You would have to write the VB.net code and create a .com wrapper class and call the .com wrapper from VBA.  If you were performing something completely isolated like converting data from another application or screen scraping to get data, you might be able to justify the complexity but not for something that would normally be done with VBA.

If you are asking this question, you probably shouldn't be using Access as a FE at all.  Just use VB.net.  It will be a whole lot easier than trying to merge the two incompatible technologies.  You'll be a whole lot happier and who ever has to follow in your footsteps will be also.  No VBA programmer will thank you for that kind of kludge.  It will take a VB.net programmer to sort it out and your client will curse your name for costing him so much money.
0
 
LVL 13

Expert Comment

by:Russell Fox
Comment Utility
You can create an assembly (dll) file, register it in Access, and then call it like other native functions. Here's an overview of programming with Access.
0
 
LVL 11

Expert Comment

by:LordWabbit
Comment Utility
You should also probably try do away with access entirely.  MS SQL Server desktop edition is free, and a lot more powerful than access.  Unless there is a lot of access forms etc. you should really think about getting rid of Access.  There are also extra license fees associated with Access, so in the long run you will be saving your client money.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
@LordWabbit,
You should also probably try do away with access entirely.  MS SQL Server desktop edition is free, and a lot more powerful than access.
You are confusing Jet/ACE with Access.  Access is the rapid application development platform that is used to create applications.  Jet and ACE are the relational databases that are used to hold data.  You can compare Jet and ACE to MS SQL Server but NOT Access.  SQL Server cannot replace Access as a development platform.  Access requires Jet or ACE as a container to hold its own objects but Access can be used as a front end for any RDBMS that is ODBC compliant so Jet and ACE are not required to hold data for an application.
0
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).

 
LVL 11

Expert Comment

by:LordWabbit
Comment Utility
@Pat, I'm not confusing anything
Unless there is a lot of access forms etc. you should really think about getting rid of Access.
We have a legacy system built on Access forms which I am busy rewriting into MVC.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
@LordWabbit,

   I agree with Pat; SQL Server is not a direct replacement for Access.  It's a direct replacement for JET/ACE, which is part of Access.

  Now if your comment had said "MS SQL Server desktop edition is free, is a lot more powerful than JET/ACE,  and you can re-write the front end forms and reports in MVC or some other tool", then it would have been more of an apples to apples comparison.

 Switching to SQL Server alone doesn't get rid of Access, which is what Pat was pointing out and trying to clarify.  Also depending on your needs, simply switching out SQL for JET/ACE may be more than enough for some.

 Jim.
0
 

Author Comment

by:garysamuels
Comment Utility
I built this application many years ago as a simple access database project. As time has gone by I changed it so that we use MSSQL as the backend database and Access as the front end user interface. There are now dozens of forms and reports based on countless queries. Everything has been coded using VBA. Rebuilding it as a VB.net application is not something I have the time to do.

I want to get rid of our in-house servers and move everything to the cloud. I've established an Azure account, copied the SQL server to Azure SQL and copied the Access database to a Virtual Windows 8.1 machine. I've also setup Azure Storage to save images and documents that may be displayed within the Access forms.

My current dilemma is building a connection between Access and Azure Storage. There are many examples of how to do this in VB.net or C+ but no one has a solution of how to do this using VBA. I've been looking for someone to hire, someone to build a .dll holding the connection strings to Azure Storage so I can reference it in Access but no one appears to be interested.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<My current dilemma is building a connection between Access and Azure Storage.>>

 Azure is really SQL Server and as such, you can connect to it with ODBC.  I would setup a DSN connection first, test that and then work with it in Access either with the DSN or DSN-less.

The only tricky part is the connection to the server, which will look something like this:

SERVER=tcp:servernamefromazure.database.windows.net,1433

outside of that, it's straight ODBC...

Jim.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
FWIW, there are some notes here that you might want to read through:

Guidelines for Connecting to Azure SQL Database
http://msdn.microsoft.com/en-us/library/azure/ee336282.aspx

  But like I said, just straight ODBC; no need for .Net

 I would also add however that your probably not going to like it.  Your going from an on premise server to an off-premise one.  Because of that, your expectations and app design need to be fundamentally different.

 It's not a situation where you can just swap out MSSQL for Azure and call it done.  

 Everything and anything will need to be pushed server side, and gone will be the days when you simply bind a form to an entire table, but rather present summarized data and then let users drill down to where then need to be.  Basically in a nut shell, you need to have a focus on minimizing data going across the wire far more so than you've done up till now.

Jim.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
I want to get rid of our in-house servers and move everything to the cloud.
Personally, I would think long and hard about that move.  As a business owner, it would scare me to death to have my data so out of my control.  Azure in particular is out of your control if you are not running it on your own server.   You'll be paying the provider extra just to send you backups because even the high-priced plans only include 14 day retention.  You'll have more options and more providers and more control if you stick with SQL server.  The cloud is best used when you need to share files with people not on your LAN.  I've been backing up my own data to the cloud for 10 years and although I am somewhat concerned about all the hackers out there, I accept the risk in order to have a good backup methodology that I don't have to worry about.

As Jim already mentioned, you will probably be disappointed in the responsiveness of the Access app when connected to an Azure database.  Even the slowest LAN is at least 10 times faster than the internet and unless your Access app is optimized for client/server it will be bringing down way too much data from the server.  And even if it is optimized correctly to never bind forms directly to tables and to use queries with criteria instead, you will still find it sluggish due to the inherent speed difference between a LAN and the internet.  Think about it, when you connect via the internet you are vying for bandwidth with all those people out there downloading movies and music and playing games and they are sucking the life out of the internet.  Third world countries have better internet speed than most of the US because their infrastructure is newer.  

The best solution I have found for remotely sharing Access applications is Citrix.  The app doesn't require any changes.  You can run the Citrix server in house (which it sounds like you don't want) or you can find a service bureau to manage a Citrix server for you.  The reason that Citrix gives such optimal performance is because the Access app runs on the server and is local to its database.  The Citrix server sends pictures of the desktop to the remote user and receives keystrokes and mouse coordinates so the data transfer is very small.
0

Featured Post

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.

Join & Write a Comment

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

772 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

12 Experts available now in Live!

Get 1:1 Help Now